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,341 questions
{count} votes

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: Most helpful
  1. 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.

  2. MartinJaffer-MSFT 26,011 Reputation points
    2021-09-09T20:34:34.333+00:00

    @Adam Wachtel I spoke too soon. I have found a work-around.

    I created 2 datasets, identical except that one had quote char as " and the other had no quote char. Both pointed to the same file.

    I started off with below csv sample data.

    "needed","unneeded"  
    "bar,baz","abc"  
    "null","true"  
    "5,600","false"  
    

    The copy activity used the the dataset with quote char as " as source. Used the one without as sink. The result was:

    needed,uneeded  
    bar\,baz,abc  
    null,true  
    5\,600,false  
    

    The idea was to place a second copy activity after your JSON->CSV . Or just remove the quote character in your sink dataset.

    Will this work as a stopgap measure?

    0 comments No 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 No comments

  4. 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 No comments