Bdtjtk

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:UsersXXDocumentssample.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

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:UsersXXDocumentssample.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.

You can try a format file: If you have applied quotes on your 2nd column...
[csvfile] contains AAAAA,"B,BB",CCC

Create Table csvfile

(

f1 VarChar(10),

f2 VarChar(10),

f3 VarChar(10)

)



BULK INSERT csvfile   

     FROM 'c:downloadssample.csv'   

     WITH (FORMATFILE = 'c:downloadssample.fmt'); 

sample.fmt

14.0

3

1       SQLCHAR             0       10      ",""    1     f1       SQL_Latin1_General_CP1_CI_AS

2       SQLCHAR             0       10      "","    2     f2       SQL_Latin1_General_CP1_CI_AS

3       SQLCHAR             0       10      "rn"   3     f3       SQL_Latin1_General_CP1_CI_AS

Based on the answer of @Lukasz Skoda, I was able to find another solution.
I have set the delimiter to pipe ( | ) and the quotechar to ( " ). Thanks to everyone trying to help me!