CSV import in SQL Server 2008

Question

I have a csv file that has column values enclosed within double quotes.

I want to import a csv file from a network path using an sql statement.

I tried bulk insert. But it imports along with double quotes. Is there any other way to import a csv file into SQL Server 2008 using an sql statement by ignoring the text qualifier double quote?

Thanks -Vivek

Answer 1

This is a known issue when importing files with text delimiters as the bcp/bulk insert utilities don't allow you to specify a text delimiter. See this link for a good discussion.

Answer 2

You could use a non-xml format file to specify a different delimiter per column. For values enclosed with double quotes, and delimited by tabs, the delimiter could be \",\". You'd have to add an initial unused column to capture the first quote. For example, to read this file:

"row1col1","row1col2","row1col3"
"row2col1","row2col2","row2col3"
"row3col1","row3col2","row3col3"

You could use this format file:

10.0
4
1  SQLCHAR 0 50 "\""     0 unused ""
2  SQLCHAR 0 50 "\",\""  1 col1   ""
3  SQLCHAR 0 50 "\",\""  2 col2   ""
4  SQLCHAR 0 50 "\"\r\n" 3 col3   ""

(The number on the first line depends on the SQL Server version. The number on the second line is the number of columns to read. Don't forget to adjust it.)

The bulk insert command accepts a formatfile = 'format_file_path' parameter where you can specify the format file. For example:

BULK INSERT YourTable
FROM 'c:\test\test.csv'
WITH (FORMATFILE = 'c:\test\test.cfmt')

This results in:

select * from YourTable
-->
col1        col2        col3
row1col1    row1col2    row1col3
row2col1    row2col2    row2col3
row3col1    row3col2    row3col3