I am trying to insert data from .csv file(test_abcd.csv), below is the file data:
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
