IS there a CSV Quotechar function as in Python suitable for Bulk Insert into MS SQL Server?

Question

CSV reading and writing with quotechar '|' in Python

The csv format below allows to read and write files via Python with columns that contain the specified delimiter (',' in this case). In this case, the ',' is placed between the B values of the second entry.

AAAAA, |B,BB|, CCC

The following Python code can be used for, e.g. writing lines to the file:

    with open(self.base_uri + filename, 'w') as f:
            writer = csv.writer(f,
                                delimiter=',',
                                quotechar='|',
                                quoting=csv.QUOTE_MINIMAL)

            for row in data_list:
                writer.writerow(row)

---

Difficulties wiht Bulk Insert into MS SQL Server

When trying to use the csv.file to apply a bulk insert in MS SQL Server, an error occurs for each line, where a quotechar is included:

take a look here

The SQL code I have utilized so far looks like this:

bulk insert DATABASE
from 'C:\Users\XX\Documents\sample.csv'
with
(
rowterminator='\n',
fieldterminator=','
)

Do you have any ideas on how to fix this issue? Is there any equvalent to the quotechar in Python in MS SQL Server?

---

Some questions about the topic : Bulk insert with text qualifier in SQL Server

Answer 1

I guess you need FIELDQUOTE:

Specifies a character that will be used as the quote character in the CSV file. If not specified, the quote character (") will be used as the quote character as defined in the RFC 4180 standard.

bulk insert DATABASE
from 'C:\Users\XX\Documents\sample.csv'
with
(
rowterminator='\n',
fieldterminator=',',
fieldquote = '|'
)

If you are using version lower than 2017, you could genereate your CSV with quotechar equals " indstead of pipe symbol.