SQL Server 2008 中的 CSV 导入 [英] CSV import in SQL Server 2008

问题描述

我有一个 csv 文件，其中的列值用双引号括起来.

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

我想使用 sql 语句从网络路径导入一个 csv 文件.

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

我试过批量插入.但它与双引号一起导入.是否有其他方法通过忽略文本限定符双引号使用 sql 语句将 csv 文件导入 SQL Server 2008?

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?

谢谢-维维克

推荐答案

您可以使用 非 xml 格式文件 为每列指定不同的分隔符.对于用双引号括起来并由制表符分隔的值，分隔符可以是 \",\".您必须添加一个初始未使用的列来捕获第一个引号.例如，读取这个文件:

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

(第一行的数字取决于 SQL Server 版本.第二行的数字是要读取的列数.不要忘记调整.)

(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.)

bulk insert 命令接受一个 formatfile = 'format_file_path' 参数，您可以在其中指定格式文件.例如:

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')

结果:

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

这篇关于SQL Server 2008 中的 CSV 导入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！