无法将CSV文件导入SQL Server表的技术求助

环境说明 #

• SQL Server版本：Microsoft SQL Server Enterprise 2019 (v15.0.2000.5)
• 操作系统：Windows 10 Enterprise LTSC 2021
• Office版本：Office 2016
• CSV文件路径：F:\TaxStuff\StuffIDs.csv

问题背景 #

有一个包含50万条记录的大型StuffIDs.csv文件需要导入SQL Server表。文件表头如下：

ID  Type  Date  RunDate  ExpirationDate  SpecialOrGeneral TaxableOrFree  Vat  VatCustomPurposes  DescriptionOfID

样本记录示例：

ID Type Date RunDate ExpirationDate SpecialOrGeneral TaxableOrFree Vat
 VatCustomPurposes DescriptionOfID
 
2710000173618 شناسه عمومی وارداتی(کالا) 1401-07-24 1400-10-13 عمومی
 مشمول 9 9 پودر شیر خشک صنعتی لبنیات فرآورده دامی

 2720000173617 شناسه عمومی تولید داخل(کالا) 1401-07-24 1400-10-13 عمومی
 مشمول 9 9 پودر شیر خشک صنعتی لبنیات فرآورده دامی
 
 2710000160892 شناسه عمومی وارداتی(کالا) 1401-07-24 1400-10-13 عمومی
 مشمول 9 9 کره حیوانی لبنیات فراورده دامی
 
2720000160891 شناسه عمومی تولید داخل(کالا) 1401-07-24 1400-10-13 عمومی
 مشمول 9 9 کره حیوانی لبنیات فراورده دامی
 
 2710000038252 شناسه عمومی وارداتی(کالا) 1401-07-24 1400-10-13 عمومی
 مشمول 9 9 کشک طعم دار لبنیات فرآورده دامی

已尝试操作 #

CREATE TABLE StuffIDs 
(
    ID NVARCHAR(MAX),
    Type NVARCHAR(100),
    Date DATE,
    RunDate DATE,
    ExpirationDate DATE,
    SpecialOrGeneral NVARCHAR(50),
    TaxableOrFree NVARCHAR(50),
    Vat INT,
    VatCustomPurposes INT,
    DescriptionOfID NVARCHAR(MAX)
)
GO

BULK INSERT StuffIDs
FROM 'F:\TaxStuff\StuffIDs.csv'
WITH (
    FIRSTROW = 2, -- Skip the header row
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

报错信息 #

Msg 4864, Level 16, State 1, Line 16

大容量加载数据转换错误（类型不匹配或指定代码页的无效字符），行2，列3（Date）。

Msg 4863, Level 16, State 1, Line 16

大容量加载数据转换错误（截断），行3，列2（Type）。

Msg 4863, Level 16, State 1, Line 16

大容量加载数据转换错误（截断），行4，列2（Type）。

问题分析与解决方案 #

核心问题 #

1. 字段分隔符错误：CSV实际为空格分隔（非逗号），且部分字段含空格（如Type列的波斯语文本），直接用空格拆分会导致字段混乱。
2. 日期格式不兼容：文件中是波斯历日期（如1401-07-24），SQL Server默认无法识别，无法直接转为DATE类型。
3. 记录跨多行存储：每条实际记录被拆分为两行，导致BULK INSERT识别错误。
4. 字段长度不足：Type列的波斯语文本可能超过100字符，引发截断错误。
5. 空行干扰：文件中的空行会被误识别为数据行。

分步解决方法 #

方法1：使用格式文件（推荐，适配大文件）

1. 调整表结构：先将日期字段存为字符串，后续再转换；同时加长Type字段长度避免截断：

DROP TABLE IF EXISTS StuffIDs;
CREATE TABLE StuffIDs 
(
    ID NVARCHAR(MAX),
    Type NVARCHAR(200),
    Date NVARCHAR(20),
    RunDate NVARCHAR(20),
    ExpirationDate NVARCHAR(20),
    SpecialOrGeneral NVARCHAR(50),
    TaxableOrFree NVARCHAR(50),
    Vat INT,
    VatCustomPurposes INT,
    DescriptionOfID NVARCHAR(MAX)
)
GO

2. 创建格式文件（StuffIDs.fmt）：保存到F:\TaxStuff\StuffIDs.fmt，内容如下：

10.0
10
1       SQLNVARCHAR     0       50      " "     1     ID                      SQL_Latin1_General_CP1_CI_AS
2       SQLNVARCHAR     0       200     " "     2     Type                    SQL_Latin1_General_CP1_CI_AS
3       SQLNVARCHAR     0       20      " "     3     Date                    SQL_Latin1_General_CP1_CI_AS
4       SQLNVARCHAR     0       20      " "     4     RunDate                 SQL_Latin1_General_CP1_CI_AS
5       SQLNVARCHAR     0       20      " "     5     ExpirationDate          SQL_Latin1_General_CP1_CI_AS
6       SQLNVARCHAR     0       50      "\r\n " 6     SpecialOrGeneral        SQL_Latin1_General_CP1_CI_AS
7       SQLNVARCHAR     0       50      " "     7     TaxableOrFree           SQL_Latin1_General_CP1_CI_AS
8       SQLINT          0       4       " "     8     Vat                     ""
9       SQLINT          0       4       " "     9     VatCustomPurposes       ""
10      SQLNVARCHAR     0       -1      "\r\n"  10    DescriptionOfID         SQL_Latin1_General_CP1_CI_AS

说明：第6行用"\r\n "作为终止符，匹配SpecialOrGeneral后换行加空格的格式，对应第二行的记录起始。

3. 执行批量导入：指定格式文件，适配UTF-8编码并跳过空行：

BULK INSERT StuffIDs
FROM 'F:\TaxStuff\StuffIDs.csv'
WITH (
    FIRSTROW = 4, -- 跳过表头和空行
    FORMATFILE = 'F:\TaxStuff\StuffIDs.fmt',
    CODEPAGE = '65001', -- 支持波斯语的UTF-8编码
    MAXERRORS = 1000 -- 允许少量错误，可按需调整
)
GO

4. 转换波斯历日期为公历：利用SQL Server 2019的日期函数转换：

ALTER TABLE StuffIDs
ALTER COLUMN Date DATE;
ALTER TABLE StuffIDs
ALTER COLUMN RunDate DATE;
ALTER TABLE StuffIDs
ALTER COLUMN ExpirationDate DATE;

UPDATE StuffIDs
SET 
    Date = DATEFROMPARTS(1300 + CAST(SUBSTRING(Date,1,4) AS INT), CAST(SUBSTRING(Date,6,2) AS INT), CAST(SUBSTRING(Date,9,2) AS INT)),
    RunDate = DATEFROMPARTS(1300 + CAST(SUBSTRING(RunDate,1,4) AS INT), CAST(SUBSTRING(RunDate,6,2) AS INT), CAST(SUBSTRING(RunDate,9,2) AS INT)),
    ExpirationDate = DATEFROMPARTS(1300 + CAST(SUBSTRING(ExpirationDate,1,4) AS INT), CAST(SUBSTRING(ExpirationDate,6,2) AS INT), CAST(SUBSTRING(ExpirationDate,9,2) AS INT))
WHERE Date IS NOT NULL;

注：若简单转换有误差，可使用自定义波斯历转公历函数提升准确性。

方法2：使用SSIS导入导出向导（操作简便）

1. 打开SQL Server Management Studio，右键目标数据库 → 任务 → 导入数据。
2. 数据源选择平面文件，指向目标CSV，设置编码为UTF-8，字段分隔符选空格，勾选“忽略空行”。
3. 在转换步骤中添加“数据转换”组件，将波斯历字符串转换为DATE类型。
4. 完成配置后执行导入，SSIS会自动处理跨多行记录和字段拆分问题。

验证导入结果 #

执行以下查询检查数据：

SELECT TOP 10 * FROM StuffIDs;

内容的提问来源于stack exchange，提问作者مجتبی حکیمیان