Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions
How to define schema when copying Excel file with several sheet with different schema
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 49%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMS%3C/text%3E%3C/svg%3E)
Mohammad Saber
20
Reputation points
Hi,
I am going to use a Copy activity in ADF to copy excel files from ADLS to Synapse Data Warehouse.
Excel file has several sheets with different column names and schema. A list of excel sheets are passed to the ADF pipeline as a parameter to be processed.
How can I define different Schemas in Dataset object in ADF in this scenario?
Background:
When I trigger Copy activity without defining or mapping Schema, I encounter the below error:
ErrorCode=DelimitedTextColumnNameNotAllowNull,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The name of column index 1 is empty. Make sure column name is properly specified in the header row.,Source=Microsoft.DataTransfer.Common,'
I searched about this error and checked the source file:
- I have checked all columns in the excel file. All columns have names. There is no empty cell in the 1st row of the excel file. There is no comma, space, or special character in column names.
- Dataset used as Source in the Copy activity is Excel (not CSV).
- "First row as header" option is enabled.
- When I use "Preview data" option, I can get a sample of data in ADF, which looks fine. All column names are similar to the source file.
- Dataset object allows us to define Schema. When I import schema for every excel sheet, it looks fine. But, the problem is there are several sheets in the excel with different schema.
As a potential solution, I am going to define schema for the excel sheets given that every excel sheet has a different schema.
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 65%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECB%3C/text%3E%3C/svg%3E)
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator2025-02-14T11:08:56.1333333+00:00 Thank you for posting your query!To define different schemas for multiple sheets in an Excel file when using a Copy activity in Azure Data Factory, please refer to the link provided below, as they might offer some insights that could help you address your question.
How to load excels sheet with different schema into cosmos DB using ADF
I hope this information helps. Please do let us know if you have any further queries.
Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Thank you.
-
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator
2025-02-17T01:57:51.3133333+00:00 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 with more details and we will try to help.
-
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator
2025-02-18T01:41:45.2533333+00:00 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 with more details and we will try to help.
Sign in to comment
Sign in to answer