locked
String with comma in csv (text format) file - ADF v2 RRS feed

  • Question

  • Hi

    I'm not finding a way to keep comma (,) ex. ("This is great, perfect") in a string and let ADFv2 escape it while reading csv file.
    I tried using quote character by sepcifying double-quotes (") but it's not working.

    To give more detail, when I open csv into notepad++ , I see that record as follwing,
    1, abc, 100, "This is great, perfect".
    Above record has four columns but a given comma in string let ADF assume it's the fifth column.

    Please share some details to solve this.

    Note - Have tried 'escapeChar' with $ which would escape $ while reading data but I need to escape , within column read.

    Friday, November 30, 2018 5:59 PM

Answers

  • Hello,

    What does your JSON configuration look like?

    Here's what it looks like for my test data with double quotes as the quotes character:

    {
        "name": "AzureBlob2",
        "properties": {
            "linkedServiceName": {
                "referenceName": "AzureStorageLinkedService",
                "type": "LinkedServiceReference"
            },
            "type": "AzureBlob",
            "typeProperties": {
                "format": {
                    "type": "TextFormat",
                    "columnDelimiter": ",",
                    "rowDelimiter": "",
                    "quoteChar": "\"",
                    "nullValue": "\\N",
                    "treatEmptyAsNull": true,
                    "firstRowAsHeader": true
                },
                "fileName": "14.csv",
                "folderPath": "adf-data"
            }
        },
        "type": "Microsoft.DataFactory/factories/datasets"
    }

    • Proposed as answer by Jason_J (Azure) Friday, November 30, 2018 8:52 PM
    • Marked as answer by GauravSagne Thursday, December 6, 2018 1:50 PM
    Friday, November 30, 2018 8:52 PM

All replies

  • Hello,

    What does your JSON configuration look like?

    Here's what it looks like for my test data with double quotes as the quotes character:

    {
        "name": "AzureBlob2",
        "properties": {
            "linkedServiceName": {
                "referenceName": "AzureStorageLinkedService",
                "type": "LinkedServiceReference"
            },
            "type": "AzureBlob",
            "typeProperties": {
                "format": {
                    "type": "TextFormat",
                    "columnDelimiter": ",",
                    "rowDelimiter": "",
                    "quoteChar": "\"",
                    "nullValue": "\\N",
                    "treatEmptyAsNull": true,
                    "firstRowAsHeader": true
                },
                "fileName": "14.csv",
                "folderPath": "adf-data"
            }
        },
        "type": "Microsoft.DataFactory/factories/datasets"
    }

    • Proposed as answer by Jason_J (Azure) Friday, November 30, 2018 8:52 PM
    • Marked as answer by GauravSagne Thursday, December 6, 2018 1:50 PM
    Friday, November 30, 2018 8:52 PM
  • Thanks Jason, It's working fine now. Not sure what magic happens. Reg G

    Thursday, December 6, 2018 1:50 PM