Copy an Excel file with empty headers Azure Data Factory (ADF)

Question

I work on Azure Synapse, I'm using Blob storage Excel and I want to copy a lot of excel sheets in my SQL database. But each Excel sheet have some empty values on the header line, and columns to be integrated have variable locations (I work with a ForEach Sheet, so the sheetname is in a parameter).

I specify that when I give names to headers, my copy is done without problem and I can work correctly with the desired data in my database, however it is inconceivable to enter by hand hundreds of headers in hundreds of different files.

How can I fix the problem and copy only the columns whose headers are not null?

Thanks a lot for your help.

Answer 1

If you need to copy data in Azure SQL that means you have a fixed list of columns to be picked from source

I would suggest to add mapping in ADF which would pick only those columns from source for which you will map a destination column and will skip the rest