从CSV批量导入SQL [英] SQL Bulk import from CSV

问题描述

我需要将大型CSV文件导入SQL服务器。我正在使用这个：

  BULK 
 INSERT CSVTest 
 FROM'c：\csvfile.txt '
和
（
 FIELDTERMINATOR ='，'，
 ROWTERMINATOR ='\n'
）
 GO 
  

问题是我所有的字段都被引号（）包围，所以实际上一行看起来像是：

  1，， 2，，有时带逗号，在里面， 
  

我可以以某种方式批量导入它们并告诉SQL使用引号作为字段分隔符吗？

编辑：使用，作为分隔符的问题（如建议的示例）是：
大多数示例所做的是，它们导入的数据中包括第一个列和末尾的，然后继续进行删除。 my，我的第一列（也是最后一列）是日期时间，不允许将 20080902导入为日期时间。

解决方案

我知道这不是一个真正的解决方案，但是我使用一个虚拟表进行导入，并为所有内容设置了nvarchar。然后执行插入操作，该操作将去除字符并进行转换。它不漂亮，但能胜任工作。

I need to import a large CSV file into an SQL server. I'm using this :

BULK 
INSERT CSVTest
        FROM 'c:\csvfile.txt'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

problem is all my fields are surrounded by quotes (" ") so a row actually looks like :

"1","","2","","sometimes with comma , inside", "" 

Can I somehow bulk import them and tell SQL to use the quotes as field delimiters?

Edit: The problem with using '","' as delimiter, as in the examples suggested is that : What most examples do, is they import the data including the first " in the first column and the last " in the last, then they go ahead and strip that out. Alas my first (and last) column are datetime and will not allow a "20080902 to be imported as datetime.

From what I've been reading arround I think FORMATFILE is the way to go, but documentation (including MSDN) is terribly unhelpfull.

解决方案

I know this isn't a real solution but I use a dummy table for the import with nvarchar set for everything. Then I do an insert which strips out the " characters and does the conversions. It isn't pretty but it does the job.

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