Copy Data, Excel file in storage account to SQL Table, not all columns found

Anonymous
2021-05-07T14:26:40.933+00:00

Hello,

I'm trying to copy data from an Excel file to an SQL Table in an Azure DB.
So far I did not have problems with doing this, but the number of columns in the Excel went from CF to EE.

Since those new columns have been added, I get the following error:
Invalid column name 'MyColumnName' is specified while read data from worksheet 'Data'

I tried to understand, and when I do "Import Schemas" in the mapping section of the copy data, the columns that I can map stop at column CG of the Excel, the ones after that are not available for mapping. I have tried switching the columns and I can always map the columns of the Excel file until CG in the Copy Data.

Is this a normal limitation of the Copy data with Excel files in ADF ? Is there a way to get all my columns in the mapping of the copy data ?

The dataset used in the copy data is made to read Excel files with First Row as Header, and is connected to a storage account.

Thanks in advance,
Thomas Duvivier

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,601 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vaibhav Chaudhari 38,606 Reputation points
    2021-05-07T15:24:37.137+00:00

    No limitation as such I guess. I could not reproduce the issue. I created excel with 104 columns (Excel col A to col CZ) and could import the data and also could see all excel columns in mapping tab. 94823-image.png

    Just for a test, you could try specifying excel range in excel dataset and see if it catches all the columns. Or if you already have a range given, see if that is correct.

    ----------

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav