'How to transform the excel file which doesn't have the column header in Azure Data Factory

I am getting one excel file every day in the data lake. My requirement is to export the excel file into an SQL database using ADF. I have five sheets in the excel file. Among the five, I have one excel sheet that doesn't have one column header. I successfully exported all four sheets into the SQL database.

I stuck with the above one sheet which doesn't have a column header. Below is the data for this excel sheet enter image description here

See, the first column doesn't have a header. When I tried to use copy activity, the schema is neglecting the first column as it doesn't have a header. When I untick the first row as a header, then it's recognised. However, I don't want to use this because the column header for other columns considers a row value which is difficult as everyday new excel file is getting added.

Any advice on how to address this issue? Tried a lot, with no success.

azureazure-sql-databaseazure-data-factory


Solution 1:[1]

I tried to reproduce this issue. But initially I got 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,'

enter image description here

Source File:

enter image description here

So I used DataFlow activity and resolved the issue.

Follow below steps.

Step1: Select Excel as Source.

enter image description here

enter image description here

Step2: Select transformation settings.

enter image description here

Step3: Execute Pipeline.

Data stored in SQL DB.

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AbhishekKhandave-MT

Related Questions