azure data factory 2 - Load multiple xlsx from Blob to ADF

Question

I created a dataset containing all xlsx files (file per country) from Blob.

To validate if all countries are loaded to the data flow, I created a sink after loading source data.

In the data preview, there is only one country (I set up debug settings to make sure all data come to be checked by statistics function); From the output (format txt), I view one country, then Blanks which are separated by comma, then some countries, then blanks.

I reviewed the blogs, and set up a wildcard path which is the same as File Path. The problem is still unsolved.

I appreciate someone providing any insights and comments to fix it.

question from:https://stackoverflow.com/questions/65906594/load-multiple-xlsx-from-blob-to-adf

Answer 1

Make sure these .xlsx files have the same schema and sheet name, that's the key. I test and it works. All the files will be loaded.

Here are my example excel files A.xlsx and B.xlsx, I put them in the one container:

With Copy data tool, set the container as path and all the files are loaded correctly:

Sink settings:

And here's the copy active overview:

Please note:

1. use Merge files in Sink Copy behavior.
2. set the delimiter which data not contains.

Output file AB.txt:

HTH.