SQL Server BULK INSERT导入CSV无报错但表为空求助

CSV文件结构 #

该CSV包含两部分数据，前两行是地理信息，后续为逐小时气象数据：

latitude,longitude,elevation,utc_offset_seconds,timezone,timezone_abbreviation
-34.622143,-58.40909,19.0,0,GMT,GMT

time,temperature_2m (°C),rain (mm),relative_humidity_2m (%),wind_speed_100m (km/h)
2024-01-02T00:00,23.0,0.20,77,29.6
2024-01-02T01:00,21.6,0.20,75,36.8
2024-01-02T02:00,20.8,0.40,80,35.5
2024-01-02T03:00,21.5,0.20,82,35.4
2024-01-02T04:00,21.6,0.00,83,32.6
2024-01-02T05:00,21.8,0.40,82,27.5
2024-01-02T06:00,21.1,1.20,85,27.3
2024-01-02T07:00,21.5,0.20,82,33.4
2024-01-02T08:00,21.1,0.00,80,31.4
2024-01-02T09:00,20.8,0.00,83,29.0
2024-01-02T10:00,20.7,0.00,82,29.0
2024-01-02T11:00,21.1,0.30,79,23.4
2024-01-02T12:00,21.2,1.70,81,14.4
2024-01-02T13:00,23.8,0.40,74,15.5
2024-01-02T14:00,24.5,0.00,72,20.9
2024-01-02T15:00,25.0,0.00,68,23.0
2024-01-02T16:00,25.4,0.00,64,24.1
2024-01-02T17:00,25.8,0.00,62,25.6
2024-01-02T18:00,25.6,0.00,61,29.5
2024-01-02T19:00,25.9,0.00,60,31.9
2024-01-02T20:00,25.5,0.00,61,30.5
2024-01-02T21:00,24.8,0.00,67,32.5
2024-01-02T22:00,24.0,0.00,72,34.4
2024-01-02T23:00,22.9,0.00,81,35.6
2024-01-03T00:00,22.6,0.00,80,32.4
2024-01-03T01:00,23.0,0.00,77,32.6
2024-01-03T02:00,22.7,0.00,76,36.1
2024-01-03T03:00,22.3,0.00,76,33.4
2024-01-03T04:00,22.0,0.00,77,30.5

执行的SQL语句 #

创建表并尝试批量导入：

CREATE TABLE WeatherData (
    [time] DATETIME2 NOT NULL,
    temperature_2m DECIMAL(5,1) NULL,
    rain DECIMAL(3,2) NULL,
    relative_humidity_2m INT NULL,
    wind_speed_100m DECIMAL(4,1) NULL
);
BULK INSERT WeatherData
FROM '<PATH TO CSV FILE>'
WITH (
    FIRSTROW = 5,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    CODEPAGE = '65001'
);
SELECT * FROM WeatherData;

问题现象 #

执行过程无任何报错，但WeatherData表始终为空。已尝试添加ERRORFILE参数（未生成错误文件）、将ROWTERMINATOR改为0x0a，均未解决问题。

解决方法 #

1. 修正行终止符 #

Windows系统下CSV默认使用CRLF（\r\n）作为行终止符，替换原语句中的ROWTERMINATOR：

BULK INSERT WeatherData
FROM '<PATH TO CSV FILE>'
WITH (
    FIRSTROW = 5,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n',
    CODEPAGE = '65001'
);

若为Linux环境下的SQL Server，保留ROWTERMINATOR = '0x0a'即可。

2. 验证文件路径与权限 #

• 使用绝对路径（如'C:\Data\weather.csv'），避免相对路径导致的解析问题
• 确保SQL Server服务账户对文件所在目录拥有读取权限；若为远程文件，使用UNC路径（如'\\Server\Share\weather.csv'）并配置相应权限

3. 预处理CSV文件 #

删除CSV中的空行（第3行），然后调整FIRSTROW参数为4（此时气象数据行从第4行开始）：

BULK INSERT WeatherData
FROM '<PATH TO CSV FILE>'
WITH (
    FIRSTROW = 4,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n',
    CODEPAGE = '65001'
);

4. 使用格式文件明确列映射 #

创建XML格式文件（如WeatherFormat.xml），定义列的类型与分隔规则：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="5"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="10"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="time" xsi:type="SQLDATETIME2"/>
  <COLUMN SOURCE="2" NAME="temperature_2m" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="1"/>
  <COLUMN SOURCE="3" NAME="rain" xsi:type="SQLDECIMAL" PRECISION="3" SCALE="2"/>
  <COLUMN SOURCE="4" NAME="relative_humidity_2m" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="wind_speed_100m" xsi:type="SQLDECIMAL" PRECISION="4" SCALE="1"/>
 </ROW>
</BCPFORMAT>

修改BULK INSERT语句使用该格式文件：

BULK INSERT WeatherData
FROM '<PATH TO CSV FILE>'
WITH (
    FIRSTROW = 5,
    FORMATFILE = '<PATH TO WeatherFormat.xml>',
    CODEPAGE = '65001'
);

5. 检查文件编码 #

确保CSV文件为不带BOM的UTF-8编码：用记事本打开文件，选择“另存为”，编码选择“UTF-8”（非UTF-8 BOM），保存后重新导入。

内容的提问来源于stack exchange，提问作者Matias Pizzi