将 CSV 文件导入 SQL Server

我正在寻找使用 BULK INSERT 将 .csv 文件导入 SQL Server 的帮助，但我有几个基本问​​题.

问题:

1. CSV 文件数据之间可能有 ,(逗号)(例如:描述)，那么我该如何导入处理这些数据?

2. 如果客户端从 Excel 创建 CSV 则带有逗号的数据包含在 "" (双引号) [如下例] 那么导入如何处理这个?

3. 我们如何跟踪某些行是否有错误数据，哪些导入会跳过?(导入会跳过不可导入的行)

这是带有标题的示例 CSV:

姓名、班级、科目、考试日期、标记、说明Prabhat,4,Math,2/10/2013,25,prabhat 的测试数据.Murari,5,Science,2/11/2013,24，他的测试的测试数据，我们可以在这里测试第二行，测试."sanjay,4,Science,,25,Test Only.

和要导入的SQL语句:

BULK INSERT SchoolsTemp从 'C:CSVDataSchools.csv'和(第一轮 = 2,FIELDTERMINATOR = ',', --CSV 字段分隔符ROWTERMINATOR = '
', --用于将控制移到下一行表块)

解决方案

基于 SQL Server CSV 导入

<块引用>

1) CSV 文件数据之间可能有 ,(逗号)(例如:描述)，那么我该如何导入处理这些数据?

解决方案

如果您使用 ,(逗号)作为分隔符，则无法区分作为字段终止符的逗号和数据中的逗号.我会使用不同的 FIELDTERMINATOR，比如 ||.代码看起来像这样，这将完美地处理逗号和单斜杠.

<块引用>

2) 如果客户端从 excel 创建 csv，那么具有逗号括在 " ... " (双引号) [如下示例] 那么导入如何处理这个问题?

解决方案

如果您使用的是 BULK 插入，则无法处理双引号，数据将是用双引号插入行.将数据插入表后，您可以用 '' 替换那些双引号.

更新表设置列具有双引号 = 替换(列具有双引号，'"','')

<块引用>

3) 我们如何跟踪某些行是否有错误数据，哪些导入会跳过?(导入会跳过不可导入的行)?

解决方案

要处理由于无效数据或格式而未加载到表中的行，可以是使用ERRORFILE属性处理，指定错误文件名，它会写行有错误到错误文件.代码应该看起来像.

BULK INSERT SchoolsTemp从 'C:CSVDataSchools.csv'和(第一轮 = 2,FIELDTERMINATOR = ',', --CSV 字段分隔符ROWTERMINATOR = '
', --用于将控制移到下一行ERRORFILE = 'C:CSVDATASchoolsErrorRows.csv',表块)

I am looking for help to import a .csv file into SQL Server using BULK INSERT and I have few basic questions.

Issues:

1. The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling these data?

2. If the client creates the CSV from Excel then the data that have comma are enclosed within "" (double quotes) [as the below example] so how do the import can handle this?

3. How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)

Here is the sample CSV with header:

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

And SQL statement to import:

BULK INSERT SchoolsTemp
FROM 'C:CSVDataSchools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '
',   --Use to shift the control to next row
    TABLOCK
)

解决方案

Based SQL Server CSV Import

1) The CSV file data may have , (comma) in between (Ex: description), so how can I make import handling these data?

Solution

If you're using , (comma) as a delimiter, then there is no way to differentiate between a comma as a field terminator and a comma in your data. I would use a different FIELDTERMINATOR like ||. Code would look like and this will handle comma and single slash perfectly.

2) If the client create the csv from excel then the data that have comma are enclosed within " ... " (double quotes) [as the below example] so how do the import can handle this?

Solution

If you're using BULK insert then there is no way to handle double quotes, data will be inserted with double quotes into rows. after inserting the data into table you could replace those double quotes with ''.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) How do we track if some rows have bad data, which import skips? (does import skips rows that are not importable)?

Solution

To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.

BULK INSERT SchoolsTemp
    FROM 'C:CSVDataSchools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '
',   --Use to shift the control to next row
    ERRORFILE = 'C:CSVDATASchoolsErrorRows.csv',
    TABLOCK
    )