Import CSV in SQL Server 2008

Question

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

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

I tried volume insertion. But it is imported along with double quotes. Is there any other way to import a csv file into SQL Server 2008 using the sql statement ignoring the double quotation mark?

Thanks -Vivek

Answer 1

non-xml, . , , \",\". . , :

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

:

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   ""

( SQL Server. - . .)

bulk insert formatfile = 'format_file_path', . :

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

:

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

Answer 2

, bcp/bulk insert . . .

Answer 3

@Andomar anaswer 99% . , , SQL Server 2014 , : \r\n. :

12.0
4
1   SQLCHAR 0   50  "\""    0   unused  ""
2   SQLCHAR 0   50  "\",\"" 1   col1    ""
3   SQLCHAR 0   50  "\",\"" 2   col2    ""
4   SQLCHAR 0   50  "\""    3   col3    ""

, , , SQL- :

BULK INSERT MyTable
FROM 'C:\mypath\datafile.csv'
WITH (
    FIRSTROW = 2,
    FORMATFILE = 'C:\mypath\formatfile.cfmt',
    ROWTERMINATOR = '\r\n'
)

There were 40 fields in the actual CSV, so it was useful to read on the Microsoft website that there was no need to write column names ( col1- col40works fine), and also that the fourth parameter in each row 50in this example should only be a maximum length , not accurate.