Forum Discussion
Fabric Data Factory Copy Data Pipeline - recursive excel file processing
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.
6 Replies
v-cboorla-msftMicrosoft Employee
Hi Anonymous
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.
- v-cboorla-msft
Hi Anonymous
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 Anonymous
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.