question

AdamWachtel-7169 avatar image
2 Votes"
AdamWachtel-7169 asked GrantOwens-9940 published

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?

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AdamWachtel-7169 and welcome to Microsoft Q&A.

This seems to be a new thing, I do not recall this limitation before I took vacation. I will make some inquiries as to why it is disallowed.

My attempts to make a workaround using only copy activity so far have failed. Data Flow might be a possibility.

0 Votes 0 ·

Hi @MartinJaffer-MSFT ,
I am also facing similar issue. I am not able to produce a CSV file where values which has delimiter in it, should only surrounded by quotes. rest all other string values should not. e.g. =>

Hello,World,"Why, this, is, not, working"

I updated the value of QuoteAllText to "false" in Json code (as in UI, it is disabled), but then got below exception -
ErrorCode=DelimitedTextInvalidSettings,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=QuoteAllText cannot set to false for Copy activity currently.,Source=Microsoft.DataTransfer.ClientLibrary,'

Many Thanks

0 Votes 0 ·
AdamWachtel-7169 avatar image
0 Votes"
AdamWachtel-7169 answered DivasGupta-1764 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AdamWachtel-7169 Could you please share more details about your workaround solution, I am also facing similar issue and it is making me crazy. I am not able to produce a CSV file where value has delimiter in it, should only surrounded by quotes. rest all other string values should not. e.g. =>

Hello,World,"Why, this, is, not, working"

Many Thanks

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

@AdamWachtel-7169 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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AdamWachtel-7169 avatar image
1 Vote"
AdamWachtel-7169 answered GrantOwens-9940 published

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We're having the same issue. We're trying to export data from a database into a flat file. And we do NOT want the open and close quotes. But we can't turn them off? This is surely a bug?

0 Votes 0 ·