SQL Server BULK INSERT - escaping reserved characters

Question

There is very little documentation about escaping characters in SQL Server BULK INSERT files.

The documentation for BULK INSERT says that the operator has only two formatting options: FIELDTERMINATOR and ROWTERMINATOR , however it does not say how you should avoid these characters if they appear in the value of a string field.

For example, if I have this table:

 CREATE TABLE People ( name varchar(MAX), notes varchar(MAX) ) 

and this single row of data:

 "Foo, \Bar", "he has a\r\nvery strange name\r\nlol" 

... what its corresponding massive insert file looks like, because it will not work for obvious reasons:

 Foo,\Bar,he has a very strange name lol 

SQL Server says it supports \r and \n , but doesn’t say if backslashes escape, and does not mention the value of the field (for example, with double quotes or dropping double quotes), so I'm a little perplexed in this area.

Answer 1

I worked on this problem using \ 0 as a line separator and \ t as a field separator, since no character appeared as a field value and both are supported as separators using BULK INSERT.

I am surprised that MSSQL does not offer more flexibility when importing / exporting. It does not take much effort to create a first-class CSV / TSV analyzer.

Answer 2

The volume insert must have the appropriate fields and the number of fields for each row. Your example is a little rude, like its unstructured data. As for characters, they will interpret them literally, rather than using escape characters (your line will be visible in the file.

As for the double quotes covering each field, you just need to use them as fields and line terminators. So now you should have:

Fieldterminator = '","', Rowterminator = '"\ n'

It makes sense? Then after the bulk insert, you need to strip out the double quote prefix with something like:

Refresh table set yourfirstcolumn = right (yourfirstcolumn, len (yourfirstcolumn) - 1)