ADF can only read 1 column from excel file which has no header

Ngô Nhật Tân 25 Reputation points
2023-07-27T07:19:45.4366667+00:00

Currrently I have a excel file which has no header and I have used explicit mapping column to get data from source into sink, the json mapping file has structure like this:

{
    "type": "TabularTranslator",
    "mappings": [
        {
            "source": {
                "name": "Prop_0"
            },
            "sink": {
                "name": "AccountNo"
            }
        },
        {
            "source": {
                "name": "Prop_1"
            },
            "sink": {
                "name": "ActualLineNo(Sort)"
            }
        },
        {
            "source": {
                "name": "Prop_2"
            },
            "sink": {
                "name": "Advice(P/O)Number"
            }
        }
	]
}

Recently, my pipeline has no update but my daily pipeline giving me the error below

ErrorCode=MappingColumnNameNotFoundInSourceFile,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Prop_1' specified in column mapping cannot be found in 'name.xlsx' source file.,Source=Microsoft.DataTransfer.ClientLibrary,'

The pipeline has no update recently and I have try some solutions for this issue:

  • If in JSON file I only keep 1 field (which is Prop_0) the pipeline will work but if in JSON mapping has more than 1 columns it will send out the same error
  • I manually mapping columns instead of using JSON file, the pipeline will work
  • In JSON file, at source setting, if I replace name property by ordinal property (name: "Prop_0" will be replaced with ordinal: 1, the pipeline will work

So I don't understand why I get the error if I keep the old setting (JSON file with name property at source setting). I wonder whether there something change in ADF engine or not and how I could resolve this problem. Thank you so much

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,444 questions
{count} vote

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.