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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With