我正在寻找帮助,以导入一个.csv
文件到SQL Server使用BULK INSERT
,我有几个基本的问题.
Issues:
CSV文件数据之间可能有
,
(逗号)(例如:description),那么我如何进行导入来处理这些数据呢?如果客户机从Excel创建CSV,那么带有逗号的数据将被括在
""
(双引号)内[如下例],那么导入如何处理?我们如何跟踪某些行是否有坏数据,哪些导入会跳过?(导入会跳过不可导入的行)
以下是带有标题的CSV示例:
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.
和要导入的SQL语句:
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
)