In Azure Data Factory read excel files

6
votes

I am new to Azure Data Factory(ADF), I need to access/load excel files sitting in a blob into ADF but as ADF doesn't support excel format(supports tex/csv/json/.. only) is there a way to ingest excel files into ADF? I really appreciate if anybody could help!

Thanks.

6
azureazure-data-factory
feedback.azure.com/forums/270578-data-factory/suggestions/… to share the need for this with the ADF team – Mhansma
@Mhansma just did! – guddu
Microsoft is working on adding Excel as a data source for ADF. feedback.azure.com/forums/270578-data-factory/suggestions/… – Vimal Amaldas

6 Answers

4
votes

ADF V2 now supports reading data from an Excel file, Here is the link to the article.

Hope this helps!

5
votes

ADF does not support reading from xls file yet.

You can find solutions in this answer: How to read files with .xlsx and .xls extension in Azure data factory?

2
votes

ADF now support Excel as a data source. You can read here

1
votes

You are right, Azure Data Factory does not support to read .xlsx file, the workaround is to save your .xlsx file as a .csv file, I think it should work.

My .xlsx file:

enter image description here

Save as .csv file, the info will not change:

enter image description here

Preview Data in ADF:

enter image description here

Besides, if you want to just copy the .xlsx file, no need to convert it to .csv, you just need to choose the Binary Copy option.

enter image description here

1
votes

If you're familiar with SSIS, you could simply use Excel Source in your SSIS package, and then run it on SSIS Integration Runtime using Execute SSIS Package activity in ADF pipeline.

0
votes

An easier solution is to use a powerautomate flow to export an excel table as a CSV and trigger the datafactory process. The only issue is that an undocumented feature on power automate is that you need to use a fixed Excel file name as passing the name as a variable fails.