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

Question

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:userdataxta9_Students.txt' 
with (fieldterminator = ',',rowterminator = '
') 

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?

Answer 1

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       "
"   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:userdataxta9_Students.txt' 
    WITH (FORMATFILE = 'C:myTestFormatFiles.Fmt')