I have several csv files need to be imported in to sql database.
The data in the file looks like following.
productid,product,manufacturers[LF]
1001,TV,"Sony, LG, Panasonic, Toshiba"[CR][LF]
1002,Watch,Sonata[CR][LF]
1003,Computer,"Dell, HCL, Sony, HP"[CR][LF]
....
The first row is the column names. However, the line ends with only [LF]. For all the rest rows (data rows), all end with [CR][LF]. I have two issues with importing these files.
First one is I don't know how to define the text qualifier as double quotes in the BCP in command. I checked the documentation. It seems there is no parameter for text qualifier.
I do see some examples with using a format file. But the thing is, in my actual data. It has hundreds of columns. It would be extremely time consuming to write a format file by myself. The other thing is if you define a column with Delimiter as ",\\"", the entire column should be in double quotes. For my case, only cells contains comma will be in double quotes. The format file cannot handle this kind of case.
The other issue is regarding the row terminator. It seems the only choice for me to define [LF] as the row terminator. If I use [CR][LF] as row terminator, then the column name and the first row of data will be considered as the first row. However, if I define [LF] as row terminator, when I do the test(my test data does not contain double quotes as text qualifier), I notice the first row of the data always got skipped. The data is loaded from the second row.
This is the command I used for the test:
bcp.exe "db.dbo.temp_2022" in "2022-01.csv" -c -r "\n" -S "servername,port" -t, -T
Does anyone know how to solve these two issues?
Asked by DBZHOU
(11 rep)
Mar 24, 2023, 01:22 PM
Last activity: Jul 25, 2025, 01:00 AM
Last activity: Jul 25, 2025, 01:00 AM