In Azure Data Factory read excel files

Question

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.

https://feedback.azure.com/forums/270578-data-factory/suggestions/19807720-add-excel-as-source to share the need for this with the ADF team — Mhansma, Apr 09 '19 at 06:17
Microsoft is working on adding Excel as a data source for ADF. https://feedback.azure.com/forums/270578-data-factory/suggestions/19807720-add-excel-as-source?tracking_code=b40710f566523bae82d8585edf5aee4c — Vimal Amaldas, Jun 03 '20 at 10:46

score 5 · Answer 1 · answered Dec 07 '18 at 08:15

5

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?

answered Dec 07 '18 at 08:15

Bo Xiao

776
6
10

Thanks for the referral @BoXiao : ) How about an upvote please? : ) – wBob Dec 07 '18 at 10:06
Yes I have gone through these earlier may be at last should adopt any of the mentioned options. I was looking for any simple straight forward approach. Anyways appreciate your reply! Thanks. – guddu Dec 07 '18 at 17:43

score 5 · Answer 2 · answered Jun 15 '20 at 05:06

5

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

Hope this helps!

answered Jun 15 '20 at 05:06

Joseph

530
3
15
37

score 3 · Answer 3 · answered Jun 24 '20 at 17:16

3

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

answered Jun 24 '20 at 17:16

MladenB

161
8

Joy Wang · Answer 4 · 2018-12-07T08:06:00.353

1

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:

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

Preview Data in ADF:

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.

edited Dec 07 '18 at 08:06

answered Dec 07 '18 at 07:53

Joy Wang

39,905
3
30
54

Thanks for reply, But sadly neither will help in my case. Firstly I need to automate entire process and its recurring. Secondly I need to edit file before copying so binary option won't work. – guddu Dec 07 '18 at 17:41

score 1 · Answer 5 · answered Dec 07 '18 at 11:26

1

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.

answered Dec 07 '18 at 11:26

Sandy Winarko-MSFT

81
1

I am a beginner in this regard, Will appreciate you can point out to any document which has clear description for the same. – guddu Dec 07 '18 at 17:45

score 0 · Answer 6 · answered Mar 04 '20 at 13:34

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.

In Azure Data Factory read excel files

6 Answers6