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,
GPA Float NULL
bulk insert xta9354.dbo.Students
with (fieldterminator = ',',rowterminator = '\n')
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 "
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')