1

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?

2 Answers 2

0

It's quite common to need to pre-process files before importing with bcp, since it is quite limited in its ability to handle row and field terminators.

In the past, I've used PowerShell to do this. There's a solid answer over on Stack Overflow about doing that, too.

0

Ask the people who send you the file to format it properly for bcp. Easy and reliable.

If they refuse, you would have to reformat the file yourself.

You can use sed. Judging by bcp.exe, you are on Windows, so just download it.

sed -i -e 1d -e "s/\r//" -e "s/,\([^ ]\)/\t\1/g" file.csv

Do not like to download tools? You can write your own script in VBS - it is always available on Windows.

Or you can go with perl, awk or even C++, whatever language you prefer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.