Invalid excel header with empty value when last cells are focused or empty columns(cells) empty using Data factory

Question

Hi Team,

I am trying to load data from excel to ASQL using ADF. when i am trying to preview data using source dataset with option first row as header true and also while using option import schema in mapping is giving me below error

Invalid header with empty value.

I have tried by setting range option in source dataset by providing range like A1:AZ1000 it is working as excepted but we need a dynamic selection of range like A1:AZ so that ADF can choose last row and dynamic range option is falling due to empty header value after data range.

And I found that in excel sheet if focus or filter is set beyond my selection range (A1:AZ) by business this issue happening.

Is their any solution to handle this issue or any bug fix done for this issue without manually adjusting this issue in each excel.

Answer 1

@vamsi krishna : I would suggest you to use Data Flow in ADF which allows you to handle NULL values in column headers

ifNull({ColumnName}, 'Unknown')  

When you now try to do a mapping then all columns with Unknown will be skipped from mapping as destination won't have any such columns

Answer 2

Hi @vamsi krishna ,
Welcome to Microsoft Q&A platform and posting your query.

As I understand your issue here is that you are trying to load data from Excel to Azure SQL database but because of having empty headers in the source dataset, it's throwing an error. To mitigate the same ,you have tried defining range and it worked well with the approach but the requirement here is to take the range dynamically. Please let me know if my understanding is incorrect.

As a workaround , please follow the below steps:

1. Use dataset pointing to excel and import schema keep 'First row as header' option as unchecked so that the header would be treated as a row.

2. Use Filter transformation to filter out the row having header value. For instance, Use expression : Column_2!='Name'

3. Use Sink transformation with Azure sql dataset to load the data to SQL tables . Disable auto mapping and manually map the columns with desired column names in sink

If the above answer helped, please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
Also, please do consider retaking the satisfaction survey . Appreciate for taking out your time and effort for the same in advance. Thankyou !

Answer 3

Thanks @AnnuKumari-MSFT for the great answer .
Hi @vamsi krishna ,
Just in case if you want to use ADF and not data flow . You can look into this thread .
https://learn.microsoft.com/en-us/answers/questions/751609/how-to-transform-the-data-if-my-excel-file-doesn39.html.
Let me know if you have further queries .
Thanks
Himanshu

If the above answer helped, please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 4

I deleted the empty columns until each empty cell looked the same size in excel. This resolved the issue for me, It seems the empty columns were used but then the values & column names were deleted, but because the columns were formatted it seems to have caused this issue. If this doesn't work for you, I would say copy all the data, just the columns with values, and then paste to a new excel file. Good Luck to yall!!!