Share via

How to convert excel binary files and excel macro enabled files to other formats in ADF?

Azure_Noob 30 Reputation points
2023-06-27T22:09:19.07+00:00

How can I convert Excel binary (.xlsb) or macro-enabled (.xlsm) files to other file types (.xlsx, .csv, etc.) in Azure Data Factory (ADF)? I receive files from various third parties in these unsupported formats, and asking them to send a different file type is not an option. My goal is to upload the data from the Excel sheets to an on-premises SQL Server database using the copy activity in ADF. While I have been able to upload .xlsx files successfully, .xlsb and .xlsm formats are not yet supported by ADF. I am looking for an automated solution that can handle the conversion process efficiently. Can you provide insights into how to achieve this in ADF?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,651 questions
0 comments
Accepted answer
  1. Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator
    2023-06-28T21:00:41.2366667+00:00

    Hello @Azure_Noob

    Welcome to the MS Q&A platform.

    Azure Data Factory does not natively support .xlsb or .xlsm file formats.

    Supported file formats: https://learn.microsoft.com/en-us/azure/data-factory/format-excel

    I see the below feedback for this feature request in the ADF feedback channel, which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

    https://feedback.azure.com/d365community/idea/ab1aad12-7226-ec11-b6e6-000d3a4f032c

    As a workaround you can follow the below steps

    • Create an Azure Function or Logic App that converts .xlsb or .xlsm files to .xlsx or .csv format. You can use a library like openpyxl for Python or EPPlus for .NET to handle the conversion.
    • Trigger the Azure Function or Logic App when a new file is uploaded to the storage account. You can use an event-driven architecture with Azure Event Grid to achieve this.

    After the conversion is complete, use the Copy Activity in ADF to move the data from the converted files to your on-premises SQL Server database.

    A similar thread has been discussed here using C#

    I hope this helps. Please let me know if you have any further questions..

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.