第 1 行第 4 列(年份)的批量加载数据转换错误(指定代码页的类型不匹配或无效字符) [英] Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)

问题描述

当我尝试将文本文件导入我的数据库时遇到转换错误.以下是我收到的错误消息:

第 1 行第 4 列(年份)的批量加载数据转换错误(指定代码页的类型不匹配或无效字符).

这是我的查询代码:

创建表学生(StudentNo Integer NOT NULL 主键，名字 VARCHAR(40) 非空，姓氏 VARCHAR(40) 非空，年份整数，GPA 浮点数 NULL);

这是来自文本文件的示例数据:

100,Christoph,Van Gerwen,2011101，阿纳尔，库克，2011102，道格利斯，鲁迪诺，2008

我想我知道问题出在哪里..下面是我的批量插入代码:

使用 xta9354批量插入 xta9354.dbo.Students来自 'd:\userdata\xta9_Students.txt'with (fieldterminator = ',',rowterminator = '\n')

对于样本数据，Year 属性之后没有','，即使Year 之后还有另一个属性Grade 为NULL

谁能告诉我如何解决这个问题?

解决方案

尝试使用
_{(来源:microsoft.com)}

本教程关于使用BULK INSERT跳过一列也可能有所帮助.

您的语句将如下所示:

使用 xta9354走批量插入 xta9354.dbo.StudentsFROM 'd:\userdata\xta9_Students.txt'WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt')

I'm getting the conversion error when I try to import a text file to my database. Below is the error message I received:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year).

Here is my query code:

CREATE TABLE Students
(
    StudentNo    Integer NOT NULL Primary Key,
    FirstName    VARCHAR(40) NOT NULL,
    LastName     VARCHAR(40) NOT NULL,
    Year         Integer,
    GPA          Float NULL
);

Here is the sample data from text file:

100,Christoph,Van Gerwen,2011
101,Anar,Cooke,2011
102,Douglis,Rudinow,2008

I think I know what the problem is..Below is my bulk insert code:

use xta9354
bulk insert xta9354.dbo.Students
from 'd:\userdata\xta9_Students.txt' 
with (fieldterminator = ',',rowterminator = '\n') 

With the sample data, there is no ',' after the Year attribute even tho there is still another attribute Grade after the Year which is NULL

Can someone please tell me how to fix this?

解决方案

Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table.

myTestFormatFiles.Fmt may look like:

9.0
4
1       SQLINT        0       3       ","      1     StudentNo      ""
2       SQLCHAR       0       100     ","      2     FirstName      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     LastName       SQL_Latin1_General_CP1_CI_AS
4       SQLINT        0       4       "\r\n"   4     Year           "

_{(source: microsoft.com)}

This tutorial on skipping a column with BULK INSERT may also help.

Your statement then would look like:

USE xta9354
GO
BULK INSERT xta9354.dbo.Students
    FROM 'd:\userdata\xta9_Students.txt' 
    WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt')

这篇关于第 1 行第 4 列(年份)的批量加载数据转换错误(指定代码页的类型不匹配或无效字符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！