We have found a workaround, will use another delimiter and strip all double quotes as suggested.
Attempting to disable quote all text option when copying to CSV
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?
5 additional answers
Sort by: Most helpful
-
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?
-
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?
-
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.
-
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.