Fabric Data Factory Copy Data Pipeline - recursive excel file processing

[AandG]

Have a directory of 100s of excel files, all with one worksheet, and all having the same column structure but the worksheet name is different in each file (its is the same as the actual file name) Requirement is to load contents of all files in to a single table using Fabric Data Factory pipeline. Using the Copy Data Activity as outlined below, using Worksheet mode 'Name' appears to expect each file to have the same name, which is not the case in this scenario so when the pipeline is run, the first file is processed successfully but then fails on the second file for this reason.

I suspect worksheet mode 'Index' is meant to cater for my scenario, the dropdown picks up the worksheet with index number '0' and data can be seen in 'preview' however when the pipeline is executed it errors with the following exception: "ErrorCode=ExcelInvalidColumnName,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid column name 'File Name' is specified while read data from worksheet ''.,Source=Microsoft.DataTransfer.ClientLibrary,'"

Q&A Assist has suggested "disable metadata propagation if it is enabled" but I don't see that property being available to me anywhere. Any guidance or suggestions appreciated.

[v-cboorla-msft]

Hi @AandG 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience that you are facing here.

As I understand that you are trying to load the contents of multiple Excel files with varying worksheet names into a single table using Microsoft Fabric pipeline.

I tried to repro the same scenario and was successful in executing the copy data activity in pipeline without any difficulty. Please find the screenshots for reference.

For doing repro I took 10 sample excel files which are in a directory named as SampleData. Just for reference I took only 3 excel data files and all the excel files have the same data.

Source data:

Copy activity configuration:

Data preview:

Execution details:

Destination:

Here are some common reasons for this error and potential solutions that might help.

• Verify that the column name you are referencing in your query matches the actual column name in the Excel worksheet.
• If your Excel column names contain special characters (such as spaces, hyphens, or other non-alphanumeric characters), it can lead to issues.
• Sometimes refreshing the query or unmerging previously merged columns can resolve issues.

I hope this information helps. Please do let us know if the issue still persists. Glad to help.

Thanks.

[Suravi_Mahanta]

During first load, I can able to  load the data. However, for second run I added one extra colum to one of the sheet, it is not detecting that column after the copy data activity completion.

Do I need to do anything with the settings?

[AandG]

Thanks for taking time to respond, good to know that it works as I had expected.  Will check your recommendations and revert back

[v-cboorla-msft]

Hi @AandG 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @AandG 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.

[v-cboorla-msft]

Hi @AandG 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.