将CSV文件加载到SQL Server时出错。

Question

我有一个像这样的SQL Server表

CREATE TABLE [dbo].[Service]
(
    [SID] INT IDENTITY (1, 1) NOT NULL,
    [LID]     INT NOT NULL,
    [RID]     INT NOT NULL,

    PRIMARY KEY CLUSTERED ([SID] ASC),

    CONSTRAINT [FK_dbo.Service_dbo.Location_LID] 
        FOREIGN KEY ([LID]) 
        REFERENCES [dbo].[Locations] ([LID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Service_dbo.Rates_RID] 
        FOREIGN KEY ([RID]) 
        REFERENCES [dbo].[Rates] ([RID]) ON DELETE CASCADE
)

我有一个.csv文件，它只包含三列按顺序排列的数字，从1到100000。

例如：

1,1,1
2,2,2
3,3,3

并且它不断地持续着，直到有10万行数据。

我正在尝试使用以下查询通过Visual Studio通过T-SQL加载这个.csv文件:

BULK INSERT service
FROM 'C:\service.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

我遇到以下错误，但不知道为什么：

Msg 4864, Level 16, State 1, Line 1
批量加载数据转换错误（类型不匹配或指定代码页的无效字符）：第1行，第3列（RID）。

- Vince

---

由于您使用的是Windows系统，行终止符不应该是\r\n吗？Windows使用回车和换行符（CRLF或\r\n）作为行终止符；而*nix和OSX仅使用LF（\n）。 - Ken White

我还没有检查哪一行出了问题。有没有简单的方法可以检查？我通过 Excel 创建了一个 CSV 文件，只创建了一列并将其复制了两次。 - Vince

根据MSDN的说法，“由于Microsoft Windows如何处理文本文件（\n会自动替换为\r\n）”，他们的示例使用ROWTERMINATOR = '''+CHAR(10)+'''。顺便提一下，您可能希望使用KEEPIDENTITY选项。 - cha

可能是csv文件出了问题。我建议尝试以下两步骤：1.创建一个“导入表”，不要包含索引、键、允许空值等，然后将csv文件导入该表中。如果一切顺利（这比直接在生产环境表上操作更好，因为您可以在操作生产环境表之前验证数据），那么几个查询就可以告诉您问题所在（例如相同或空的“SID”值）。2.如果结果不令人满意，请将文件分成两部分并导入其中一半。如果一切正常，请尝试另一半。如果失败，请再次将“错误文件”分成两部分并重复此过程（如果一切正常，则是关键问题）。 - user2299169

你可以尝试使用OPENROWSET加载数据，参见我的答案：http://stackoverflow.com/questions/35662745/how-to-modify-data-in-csv-during-bulk-insert/35671570#35671570 - gofr1

Answer 1

诊断 BULK INSERT 问题的最简单方法是指定错误文件。错误文件将包含失败行的确切数据。根据 MSDN，您还将获得一个控制文件，该文件将标识每个失败的行并提供有关每个失败的详细信息。

    BULK INSERT service
    FROM 'C:\service.csv'
    WITH
    (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\r\n',
        ERRORFILE = 'C:\ErrorFile.txt'
    );

当你打开包含错误数据的错误文件时，请使用一个显示空格和不可打印字符或具有显示每个字符字符代码的显示模式的文本编辑器。如果有任何意外字符，它们将会显示出来。

- Sheldon

Answer 2

如果您使用 Excel 保存 CSV 文件，请确保已将其保存为“CSV（MS-DOS）”，而不是“CSV（Macintosh）”。 如果 CSV 文件格式正确，则可以尝试使用批量加载的格式文件。 CSV 的格式文件应如下所示：

9.0
3
1   SQLINT  0   4   ","      1  SID ""
2   SQLINT  0   4   ","      2  LID ""
3   SQLINT  0   4   "\r\n"   3  RID ""

您可以使用批量加载查询，其格式如下：

BULK INSERT [Service]
    FROM 'C:\import_dos.csv'
    WITH (FORMATFILE = 'C:\temp\services.FMT')

- Edmond Quinton