none
How to handle double pipe delimiter in copy activity in Azure Data Factory

    Question

  • Hi there,

    I'm working with a double pipe delimited file which I want to ingest in Azure SQL DB via Data Factory:

    Column1||Column2||Column3

    In my input dataset, I specified the double pipe delimiter as columnDelimiter in the format section of typeProperties:

            "format": {
                "type": "TextFormat",
                "rowDelimiter": "\n",
                "columnDelimiter": "||",
                "skipLineCount": 2
            }
    

    Unfortunately, this is not working. All rows are marked as "incompatible" and no data is ingested in my SQL table. Next, I tried a few things. When not specifying any columnDelimiter, the complete row of data including the delimiters are ingested in the first column of my SQL table, so no delimiter is considered at all. This is not correct, logically each data item must be ingested in a separate column. Scripting the || in Unicode also did not fix it. When converting my input file to single pipe delimited it works fine. However, in the end-state I'm not able to alter the input file so handling the double pipe delimiter is essential.

    Any thoughts? Thanks in advance!

    Monday, May 14, 2018 6:08 AM

All replies

  • Hi,

    ADF only supports single character column delimiter. And if you don't specify it, the default column delimiter (comma) is used. You could reference this doc.  https://docs.microsoft.com/en-us/azure/data-factory/supported-file-formats-and-compression-codecs#text-format.


    Tuesday, May 15, 2018 5:43 AM