0

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

Community
  • 1
  • 1
M. Straube
  • 15
  • 3

3 Answers3

0

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.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you for the fast reply! I am using MS SQL Management version 2014. Fieldquote is therefore not an option. I have changed the utilized quotechar, just as you stated, from pipe symbol ( | ) to double quotes ( " ). Data entries now look like this: AA, "BBB,BB", CC Nonetheless. I receive the same errors when trying to start the bulk insert as before. – M. Straube Dec 30 '18 at 17:46
0

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:\downloads\sample.csv'   
     WITH (FORMATFILE = 'c:\downloads\sample.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      "\r\n"   3     f3       SQL_Latin1_General_CP1_CI_AS
level3looper
  • 1,015
  • 1
  • 7
  • 10
0

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!

M. Straube
  • 15
  • 3