Azure Data Factory - possible bug on importing Excel file when "First Row as Header" is true

Question

Azure Data Factory - possible bug on importing Excel file when "First Row as Header" is true

Williams, Damian (Barrachd) 26

Hi Team

I'm trying to import an Excel file. When First Row as Header is false, ADF sees the file and confirms the number of columns in Import Schema, but doesn't get the column names.

When First Row as Header is true, the error message:

"Failed to load
Invalid excel header with empty value."

is returned instead. I've checked and the Range value is definitely correct - there is data in the referenced cell and a few cells to the right.

Have tried using First Row as Header in both dynamic (parameter) mode, and hard-coding (ticking the box) and have tried amending the Range to other cells in the worksheet instead, but it doesn't matter what I do- if the setting is true, the error is returned.

Please can you confirm where I'm going wrong?

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-17T00:47:45.553+00:00

Hi @Williams, Damian (Barrachd) ,

Welcome to Microsoft Q&A forum and thanks for your query.

Regarding "I'm trying to import an Excel file. When First Row as Header is false, ADF sees the file and confirms the number of columns in Import Schema, but doesn't get the column names." - This is expected behavior when firstRowAsHeader is set to false in a dataset.

And coming back to 2nd issue when firstRowAsHeader is set to true, I was able to reproduce the error message by removing a column name in excel sheet and it throws the same error you pointed out. Just to confirm if this happens with other file format datasets, I tried with csv blob, but observed a different behavior.

In order to get more clarification on this behavior, I have reached to internal team and awaiting a response. Will get back to you as soon as I have an update from the team.

Thank you for you patience and raising this concern.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T07:44:22.187+00:00

Thank you @KranthiPakala-MSFT

Your comment on removing a column heading helped. In my case, it looks as though the spreadsheet creator had deleted the values in an entire column at the end of the data by clearing the contents. Excel still saw the column as having values (pressing CTRL-END went into that column and not into the previous column which was the last one with data).

Deleting all blank columns from the last column to the right hand edge of the worksheet has got Data Factory working again.

I think this is a bug - the documentation states that ADF dynamically detects which columns to use - it's failing if there's a previously populated empty column at the end of the table.

Damian.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T08:40:05.227+00:00

For anyone else struggling with this, the below also helped with resetting the columns Excel as containing data
https://www.accountingweb.com/technology/excel/resetting-the-last-cell-in-an-excel-worksheet#:~:text=Move%20your%20cursor%20up%20one,even%20though%20you%20erased%20it.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T09:18:59.807+00:00

@KranthiPakala-MSFT

Further update - have changes been made in this area in the past few hours?

In a copy data activity, add the Excel sheet as a source, with firstRowAsHeader set to true. On the Source tab of Copy data, clicking Preview Data works as expected.
On the Mappings tab, Import Schemas and Preview source both fail with error:

Invalid column name '_c0' is specified while read data from worksheet 'Sheet1'.

This was working earlier today on the same sheet.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-19T00:11:53.477+00:00
Hi @Williams, Damian (Barrachd) ,

Thanks much for your response and sharing your findings. Would it be possible for you to share the sample excel file with us for reproducing the issue on our end? If yes, please do email us to AzCommunity[at]microsoft[dot]com with below details.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Subscription ID: <your subscription id>

Attached sample excel file to reproduce the issue.

Let me know here once the email is sent.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-21T17:14:06.463+00:00

Hi WilliamsDamianBarrachd-7717,

Following up to see if you have got a chance to email the details requested in my previous comment? Please let me know here once the email is sent.

Thank you
Williams, Damian (Barrachd) 26 Reputation points

2020-09-22T07:58:32.11+00:00

Apologies - I had a couple of days off.
I'm not able to share the sheet because of the sensitive data it holds. A colleague has managed to get it working by adding a new sheet with links to the original
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-22T19:24:36.547+00:00

Hi WilliamsDamianBarrachd-7717 ,

Thanks for your response and glad to know that you were able to figure out and thanks for sharing your insights here with the community as it would be helpful for others reading this thread.

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-17T00:47:45.553+00:00

Hi @Williams, Damian (Barrachd) ,

Welcome to Microsoft Q&A forum and thanks for your query.

Regarding "I'm trying to import an Excel file. When First Row as Header is false, ADF sees the file and confirms the number of columns in Import Schema, but doesn't get the column names." - This is expected behavior when firstRowAsHeader is set to false in a dataset.

And coming back to 2nd issue when firstRowAsHeader is set to true, I was able to reproduce the error message by removing a column name in excel sheet and it throws the same error you pointed out. Just to confirm if this happens with other file format datasets, I tried with csv blob, but observed a different behavior.

In order to get more clarification on this behavior, I have reached to internal team and awaiting a response. Will get back to you as soon as I have an update from the team.

Thank you for you patience and raising this concern.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T07:44:22.187+00:00

Thank you @KranthiPakala-MSFT

Your comment on removing a column heading helped. In my case, it looks as though the spreadsheet creator had deleted the values in an entire column at the end of the data by clearing the contents. Excel still saw the column as having values (pressing CTRL-END went into that column and not into the previous column which was the last one with data).

Deleting all blank columns from the last column to the right hand edge of the worksheet has got Data Factory working again.

I think this is a bug - the documentation states that ADF dynamically detects which columns to use - it's failing if there's a previously populated empty column at the end of the table.

Damian.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T08:40:05.227+00:00

For anyone else struggling with this, the below also helped with resetting the columns Excel as containing data
https://www.accountingweb.com/technology/excel/resetting-the-last-cell-in-an-excel-worksheet#:~:text=Move%20your%20cursor%20up%20one,even%20though%20you%20erased%20it.
Williams, Damian (Barrachd) 26 Reputation points

2020-09-17T09:18:59.807+00:00

@KranthiPakala-MSFT

Further update - have changes been made in this area in the past few hours?

In a copy data activity, add the Excel sheet as a source, with firstRowAsHeader set to true. On the Source tab of Copy data, clicking Preview Data works as expected.
On the Mappings tab, Import Schemas and Preview source both fail with error:

Invalid column name '_c0' is specified while read data from worksheet 'Sheet1'.

This was working earlier today on the same sheet.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-19T00:11:53.477+00:00

Hi @Williams, Damian (Barrachd) ,

Thanks much for your response and sharing your findings. Would it be possible for you to share the sample excel file with us for reproducing the issue on our end? If yes, please do email us to AzCommunity[at]microsoft[dot]com with below details.

Email subject: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Subscription ID: <your subscription id>

Attached sample excel file to reproduce the issue.

Let me know here once the email is sent.

Thank you
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-21T17:14:06.463+00:00

Hi WilliamsDamianBarrachd-7717,

Following up to see if you have got a chance to email the details requested in my previous comment? Please let me know here once the email is sent.

Thank you
Williams, Damian (Barrachd) 26 Reputation points

2020-09-22T07:58:32.11+00:00

Apologies - I had a couple of days off.
I'm not able to share the sheet because of the sensitive data it holds. A colleague has managed to get it working by adding a new sheet with links to the original
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-09-22T19:24:36.547+00:00

Hi WilliamsDamianBarrachd-7717 ,

Thanks for your response and glad to know that you were able to figure out and thanks for sharing your insights here with the community as it would be helpful for others reading this thread.

Answer 1

Akshay Mahajan 26

@KranthiPakala-MSFT and anyone else who might come looking for a workaround this issue, we got it working by providing the "Range" value after the dataset was created. We added a range say A1:G100 (in the Range field on the Connection tab) and then imported schema, it succeeded. If we remove range and try import schema again, it fails with the error that's mentioned above in this thread.

John Edwards 35 Reputation points

2024-12-06T18:33:19.4333333+00:00

Please forward your address so I can send you a beer. A nice one.

Share via

Azure Data Factory - possible bug on importing Excel file when "First Row as Header" is true

1 answer

Your answer