Azure data factory - Invalid excel header with empty value

Question

Hi Team,

I am encountering an issue while loading data from excel to database using ADF. when i am trying to preview data using source dataset with option first row as header true it gives me below error

Invalid header with empty value.

I could work around this by deleting the column with the empty header but is there any solution without adjusting this issue in each excel? I would like to load the data dynamically so there will be no schema/mapping provided except for the header in the file.

Seems like a bug to me. When I set "First row as header" to false, I could preview the data, although the last column remains empty.

Answer 1

Hi @Duc Le Van

When encountering the "Invalid header with empty value" error in Azure Data Factory (ADF) while loading data from Excel, it typically indicates that one or more columns in the header row are empty. Unfortunately, ADF requires that all headers have valid names, and empty headers can lead to issues during data preview or loading.

As a workaround, you can manually remove the empty headers from the Excel files, as you have already done. However, if you want to avoid adjusting each Excel file individually, consider the following options:

Use a Template - Create a template Excel file with predefined headers, ensuring that all columns have valid names. You can then populate this template with data dynamically.

Pre-process the Excel Files - If possible, implement a pre-processing step in your data pipeline that checks for empty headers and renames them before loading the data into ADF.

Set "First row as header" to false - As you mentioned, setting this option to false allows you to preview the data without requiring valid headers. However, this may not be suitable for all scenarios, especially if you need the headers for further processing.

Currently, there is no built-in feature in ADF to automatically handle empty headers dynamically without modifying the Excel files.

Reference: Troubleshoot connector and format issues in mapping data flows in Azure Data Factory

Alternatively, could you please check the similar threads from MS Q&A forum, that might help to resolve the issue:

https://learn.microsoft.com/en-us/answers/questions/761277/invalid-excel-header-with-empty-value-when-last-ce

https://learn.microsoft.com/en-us/answers/questions/1826261/adf-copy-activity-emply-col-issue

https://learn.microsoft.com/en-us/answers/questions/97371/azure-data-factory-possible-bug-on-importing-excel

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.