Attempting to disable quote all text option when copying to CSV

Adam Wachtel 116 Reputation points
2021-09-08T21:22:13.747+00:00

I am copying from JSON to CSV using a copy data task. The issue I run into is with nullable boolean fields, that are represented in the JSON as true, false or no property in the case of a null. This is translated to "true","false","" in the resulting csv, rather than true,false,[null]. I.e. I don't want them wrapped in double quotes.

When I disable quote all text (I have to manually edit the settings json), I get:

ErrorCode=DelimitedTextInvalidSettings,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=QuoteAllText cannot set to false for Copy activity currently.,Source=Microsoft.DataTransfer.ClientLibrary,'

Obviously the answer states I can't do that, but why? More importantly how can I remove the quotes from columns that don't have commas?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,605 questions
Accepted answer
  1. Adam Wachtel 116 Reputation points
    2021-09-10T18:35:42.923+00:00

    We have found a workaround, will use another delimiter and strip all double quotes as suggested.

5 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Krish Chou 0 Reputation points
    2023-11-13T07:57:58.84+00:00

    We got the same issue in ADF and raised a ticket with Microsoft. They refused to acknowledge that this is a bug and said that Copy Activity is supposed to work this way and asked us to use data flow activity to remove the quotes from the generated csv. This resulted in slowness and a surge in usage costs which were unacceptable.

    I have found a workaround for this. My use case was SQL Server as source and CSV as sink. The workaround was that in the table I have created a derived column with the following rule:

    IIF(CHARINDEX(',',COLUMN_NAME) <> 0,'"'+COLUMN_NAME+'"',COLUMN_NAME)

    which basically adds double quotes when a comma (column delimiter) is present in the value.

    I have then used column mapping in the Copy Data activity to point the derived column to the output column and it started working.

    Do let me know if you guys find any issues with this approach or any better one.

    0 comments
  2. Martinez, Richard P 0 Reputation points
    2023-08-28T23:13:56.9733333+00:00

    To remove the double quotes from your sink - set quote character to "no quote character" AND enter a value for null value. worked for me.

    0 comments
  3. Hubbaduba 1 Reputation point
    2023-02-06T20:47:07.0433333+00:00

    I was able to change the file extension from .txt to .csv and the result was w/o "" even with the checkmark in the box.

    0 comments
  4. Adam Wachtel 116 Reputation points
    2021-09-09T22:39:34.907+00:00

    Martin, thanks for the suggestion. Unfortunately that results in an invalid CSV, we have a third party that will be reading these and needs it to be properly escaped. Is there a reason why the quote all text cannot be unchecked or an ETA when it may be operational?

    1 person found this answer helpful.