如何解决CSV文件批量导入SQL Server数据表失败问题及其他实现方案

现有配置错误原因 #

• 格式文件配置错误：第二行标注的字段数为133，实际CSV仅3个字段，完全不匹配
• 分隔符配置错误：CSV文件为逗号,分隔，无字段包裹引号，但格式文件中配置的是制表符\t加引号作为分隔符，和实际文件格式不符
• 未跳过表头行：CSV第一行是字段名，现有导入脚本未配置跳过首行，会把表头当做数据尝试插入
• 字段长度不匹配：格式文件中配置的字段长度和实际表结构定义的varchar长度不一致

注：操作前需要确认SQL Server服务运行账户对CSV文件、格式文件所在路径有读取权限，否则会触发权限类报错

修正后的实现方案 #

方案1：无需格式文件，直接使用BULK INSERT参数配置（推荐，更简单） #

直接修改BULK INSERT语句，指定分隔符、行结束符、跳过首行即可，代码如下：

BULK INSERT [dbo].[emp] FROM 'C:\emp.csv' 
WITH (
    FIELDTERMINATOR = ',',  -- 字段分隔符对应CSV的逗号
    ROWTERMINATOR = '\n',   -- 行结束符对应换行符
    FIRSTROW = 2,           -- 跳过第一行表头
    CODEPAGE = 'ACP'        -- 如果导入内容含中文可加该参数，纯英文内容可省略
);

方案2：修正格式文件后导入 #

如果需要使用格式文件，先把emp.fmt内容修改为如下：

11.0
3
1       SQLCHAR             0       109     ","         1      empid         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       109     ","         2      first_name    SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       19      "\r\n"      3      last_name     SQL_Latin1_General_CP1_CI_AS

然后修改导入脚本，增加跳过首行的参数：

BULK INSERT [dbo].[emp] FROM 'C:\emp.csv' 
WITH (
    FORMATFILE='C:\emp.fmt',
    FIRSTROW = 2
);

其他可行的导入方案 #

• 使用bcp命令行工具导入，命令示例：
  bcp dbo.emp in C:\emp.csv -S 你的数据库实例名 -d 你的库名 -U 用户名 -P 密码 -c -t, -r\n -F2
• 使用SQL Server的OPENROWSET函数查询CSV内容后插入，适合需要做数据清洗转换的场景
• 使用SSMS自带的导入导出向导，可视化操作，适合不熟悉SQL的用户，支持映射字段、自动建表等操作
• 使用SSIS包实现自动化批量导入，适合定期重复执行的导入需求

内容的提问来源于stack exchange，提问作者Jeniffer Rose