Help escaping with External File Format

Hochmanj 101

I'm trying to create an external file format to create an external table off of a csv file with a simplified example row shown below consisting of three string fields (the last being html):

 foo,bar,"<div id=""myId"">\n<p>hello</p>\n</div>"\n

it seems, that all the '\n''s including those inside the last field are creating new lines.

Is there a way to escape the html \n while honoring the 'actual' \n that occurs at the end of the row?

1 answer

Ryan Abbey 1,181 Reputation points

2021-07-21T21:57:38.097+00:00

In your dataset, set the Quote character to '"' (double quote)
Please sign in to rate this answer.
Hochmanj 101 Reputation points

2021-07-21T22:09:45.01+00:00

Isorry, i should have specified i'm using tsql with something like ..

CREATE EXTERNAL FILE FORMAT CSV2

WITH (
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', USE_TYPE_DEFAULT = FALSE
);

that being said, taking your advice i did notice i can correctly parse it in a dataset if i use escape double quote. Perhaps I should copy in a pipeline to fix the parsing then create an external table?

for further context, the csv's are from Power BI data flows

Ryan Abbey 1,181 Reputation points

2021-07-21T23:51:21.207+00:00

In that case, yes, you need to add a QUOTE to your FORMAT_OPTIONS... can't see it in the format_options list on MS help site so not sure if it should be QUOTE but potentially could be FIELD_OPTIONALLY_ENCLOSED_BY, no idea if there's been standardisation on that part

I wouldn't go for using escape (although who knows with Microsoft), the standardisation for escaping the quote character is the quote character but not necessarily for other characters so you do run the risk of generating unexpected results (.e.g normally \t represents a tab, if you start your string as "t, it will get interpreted as a tab rather than a string starting with "t"

MartinJaffer-MSFT 26,086 Reputation points

2021-07-23T06:04:01.937+00:00

I notice one issue with the quote character. There are quotes inside, as well as surrounding the data. This would break, unless the double-double quote is a form of escaping.

I don't think the ask is possible as-is, but I have been wrong before.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Help escaping with External File Format

1 answer

Your answer