CODEWITHSUNDEEP
sql-server
HBK
HBK

Reputation: 55

Remove double quotes from csv file while inserting data into table using bulk collect in sql server

I am trying to insert data from .csv file(test_abcd.csv), below is the file data:

enter image description here

It is having 2 rows and a header. below is the table structure: A char(10), B char(10), C char(10), D char(10), E char(10), F char(10), G char(10), H char(10)

below is bulk insert command:

bulk insert TEST_ABCD
from 'C:\path\TEST_ABCD.csv'
with
    (   datafiletype = 'char',
        fieldterminator = ',',
        rowterminator = '\n',
        formatfile='C:\path\newFormat.fmt',
        firstrow = 2
    );

and below is format file:

14.0

17 1 SQLCHAR 0 0 """ 0 dummy1 "" 2 SQLCHAR 0 50 """ 1 A "" 3 SQLCHAR 0 0 ","" 0 dummy2 "" 4 SQLCHAR 0 50 """ 2 B "" 5 SQLCHAR 0 0 ","" 0 dummy3 "" 6 SQLCHAR 0 50 """ 3 C "" 7 SQLCHAR 0 0 ","" 0 dummy4 "" 8 SQLCHAR 0 50 """ 4 D "" 9 SQLCHAR 0 0 ","" 0 dummy5 "" 10 SQLCHAR 0 50 """ 5 E "" 11 SQLCHAR 0 0 ","" 0 dummy6 "" 12 SQLCHAR 0 50 """ 6 F "" 13 SQLCHAR 0 0 ","" 0 dummy7 "" 14 SQLCHAR 0 50 """ 7 G "" 15 SQLCHAR 0 0 ","" 0 dummy8 "" 16 SQLCHAR 0 50 """ 8 H "" 17 SQLCHAR 0 0 "\r\n" 0 dummy9 ""

command executes successfully but only one is getting inserting will null values.

A   B   C   D   E   F   G   H

NULL NULL NULL NULL NULL NULL NULL NULL

and if I open test_abcd.csv file in notepad++ then it is showing like below:

A,B,C,D,E,F,G,H

"""1""","""2""","""3""","""4""","""5""","""6""","""7""","""8""" """21""","""22""","""23""","""24""","""25""","""26""","""27""","""28"""

How can I change or update my format file so I can insert proper data of csv into table.

I am using sql server 2019 developer edition

Upvotes: 0

Views: 620

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131722

SQL Server 2017 added support for CSV files and quoted fields. There's no need for a format file any more.

You can load a CSV file with fields quoted using double quotes simply by adding WITH ( FORMAT = 'CSV') :

bulk insert TEST_ABCD
from 'C:\path\TEST_ABCD.csv'
with (  
        FORMAT = 'CSV',
        firstrow = 2
     );

Upvotes: 2

Related Questions