将CSV文件导入SQL Server [英] Import CSV file into 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，测试他的测试数据，我们可以测试第二个ROW，测试。 
 sanjay，4，Science ,, 25，仅测试。 
  

要导入的SQL语句：

  BULK INSERT SchoolsTemp 
 FROM'C：\CSVData\Schools.csv'
 WITH 
（
 FIRSTROW = 2，
 FIELDTERMINATOR ='，'， -  CSV字段分隔符
 ROWTERMINATOR ='\\\
'， - 用于将控件转移到下一行
 TABLOCK 
）
  
 
 
 
感谢。
解决方案
基于SQL Server的CSV导入
 
 
 1）CSV文件数据可能有，逗号）之间（例如：
描述），那么如何使导入处理这些数据？
 
 
 解决方案 
 
 
 
如果您使用，（逗号）区分字段终止符中的
逗号和数据中的逗号。我将使用不同的FIELDTERMINATOR 
如 || 。 
 
 
 
所以代码看起来像是，这将完美地处理逗号和单斜杠。
 
 
 2）如果客户端从excel创建csv，那么
逗号的数据被包含在...（双引号）[作为 
  
 
 > 
 
 
如果你使用BULK插入，那么没有办法处理双引号，数据将
插入双引号到行。 
在将数据插入表之后，您可以用 替换这些双引号。
 更新表
 set columnhavingdoublequotes = replace（columnhavingdoublequotes，''，''）
  
 
 b $ b 
 
 
 3）如何跟踪某些行是否有错误的数据，导入是否跳过？
（导入是否跳过不可导入的行）？
 
 
 解决方案 
 
 
 
表格，因为无效的数据或格式，可能是
句柄使用 ERRORFILE属性，指定错误文件名，它将把
行出错的行写入错误文件。代码应该像。
  BULK INSERT SchoolsTemp 
 FROM'C：\CSVData\Schools.csv'
 WITH 
（
 FIRSTROW = 2，
 FIELDTERMINATOR = ，'， -  CSV字段分隔符
 ROWTERMINATOR ='\\\
'， - 用于将控件移动到下一行
 ERRORFILE ='C：\CSVDATA\SchoolsErrorRows.csv'，
 TABLOCK 
）
  
 
I looking for help to import a .csv file into SQL Server using BULK INSERT and I have few basic questions around this.

Issues:  

The CSV file data may have , (comma) in between (Ex: description), so how can I  make import handling these data?  
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?  
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:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
Thanks.
解决方案
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 delimiter than there is no way to differentiate between
comma in field terminator and comma in your data. I would use a different FIELDTERMINATOR
like ||. 

So 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:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )


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