Importing a CSV file using BULK INSERT command into SQL Server table

Question

I have CSV file which have a couple of data columns.

The CSV file looks like

field1: Test1
field2: Test2
field3: Test3, Test4, Test5

In this case which library can I use as field terminator I mean if I use this query to insert CSV file into shopifyitem table as you assume that the data field inserted not correctly

BULK INSERT shopifyitem
FROM 'c:\test.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

So which field terminator can I use?

Thank you so much in advance....

Answer 1

I don't think you're going to be able to import that format without some type of pre-processing. As Aaron implied, that's not a standard CSV.

If you can't get the file re-formatted, there are several ways to get the data into an importable format:

http://www.timmitchell.net/post/2013/01/14/ragged-flat-file-processing-in-ssis/

http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html

http://msdn.microsoft.com/en-us/library/ms136060.aspx (scroll down to the "Flat File Source Example")

These are all SSIS solutions which leverage .NET for the bulk of the work. I prefer SSIS simply because of the built in productivity tools. It could be done with any text processor like a console app or powershell script (if you really have some time on your hands).

I prefer the script component source with a stream reader, but Tim Mitchell has an interesting alternative.

Answer 2

Try using:
ROWTERMINATOR = '0x0a'