locked
Found more columns than expected column count: RRS feed

  • Question

  • Hi  Guys,

    We are trying to load .txt with tab delimit to Azure SQL dB; but gt below error; While does it using SSIS we dont get any such error.

    Copy activity encountered a user error at xyz.database.windows.net side: ErrorCode=UserErrorSourceDataContainsMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' source 'xyztxt' with row number 20: found more columns than expected column count: 440.,Source=Microsoft.DataTransfer.Common,'.

    How to overcome such error....

    How Azure data factory handles dirty data issues?

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Tuesday, February 21, 2017 7:05 PM
    Tuesday, February 21, 2017 6:27 PM

Answers

  • How does row 20 look? Have you got any quoting character, and is that set for the dataset? You can set this in the format section: "typeProperties": { "folderPath": "mycontainer/myfolder", "fileName": "myblobname", "format": { "type": "TextFormat", "columnDelimiter": ",", "rowDelimiter": ";", "quoteChar": "\"", "NullValue": "NaN", "firstRowAsHeader": true, "skipLineCount": 0, "treatEmptyAsNull": true } },
    • Marked as answer by Navind Wednesday, March 8, 2017 8:27 PM
    Tuesday, February 21, 2017 9:08 PM

All replies

  • How does row 20 look? Have you got any quoting character, and is that set for the dataset? You can set this in the format section: "typeProperties": { "folderPath": "mycontainer/myfolder", "fileName": "myblobname", "format": { "type": "TextFormat", "columnDelimiter": ",", "rowDelimiter": ";", "quoteChar": "\"", "NullValue": "NaN", "firstRowAsHeader": true, "skipLineCount": 0, "treatEmptyAsNull": true } },
    • Marked as answer by Navind Wednesday, March 8, 2017 8:27 PM
    Tuesday, February 21, 2017 9:08 PM
  • Hi Helge,

    We removed "" for each column using Powershell;  And its tab delimit but some of the column have desc so it might have tabs in it.

    How to I ignore or clean such data?

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Wednesday, February 22, 2017 8:28 PM
  • Hi Navind,

    could you give me some sample data (expecially row 20) and the source dataset json? so that I can help to confirm the dataset is specified correctly. or RunId for failed copy can also help us troubleshooting.

    BTW, may I know you are creating this pipeline from CopyWizard or JsonEditor?

    Regards,

    Gary

    Friday, February 24, 2017 6:59 AM
  • Hi Gary,

    I eliminated some of the  columns which have text data type and contains "tab data in it" And  as Helge mentioned I used quote character As (") and was able to load data;  But my concerns is what if any of the column which has  tab  as data. My load might fail again.

    Below is the sample; How do I handle  bad data as its highly like to occur.

    col1 col2 col3 col4
    1 2 3 4
    2 3
    3 4 5 6

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Monday, February 27, 2017 11:33 PM
  • 0e125389-ca5c-4aae-a3b6-290e61719a7d_636238368000000000_636239232000000000_OutputDataset-bvs

    Data Read: 0.00 Bytes, Written: 0.00 BytesRuntime region: East US 2


    you can test using _old file as i replaced HSG01.csv file first column values with "-" as it contained "," in it.


    Navin.D http://dnavin.wordpress.com

    Friday, March 3, 2017 8:00 PM