CODEWITHSUNDEEP
sqlt-sql
GrafixMastaMD
GrafixMastaMD

Reputation: 147

SQL Bulk Insert -- Mismatch

Ok, So I am getting the following error, which seems odd.

Msg 4864, Level 16, State 1, Line 3 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 129, column 4 (BirthDay).

My CSV File is formatted in the following Format

1,Aaron,Aaberg,19700926,MALE

But if I do the following:

INSERT INTO Test.BulkInsert (FirstName, LastName, Birthday, Gender)
VALUES ('1' 'Test', 'Me', 19851118, 'Male')

Works fine? What gives? I have tried everything under the sun. If I use INT as the data type for Birthday import works fine. I have tried to then CAST and or CONVERT the int and I get an arithmetic overflow obviously.

I forgot to give you the table Code:

CREATE TABLE Test.BulkInsert (ID int NOT NULL,
                  FirstName VARCHAR(40),
                  LastName VARCHAR(40),
                  BirthDay SMALLDATETIME,
                          Gender VARCHAR(6)

)
GO

Upvotes: 3

Views: 3360

Answers (5)

Chains
Chains

Reputation: 13157

Sounds like your birthdate is not matching the smalldatetime requirements (?)

Import it as an int first (since you know that works).

then some things you might query it for
1) length where length < 8
2) birthdate < 19000101
3) birthdate > 20790601
(January 1, 1900, through June 6, 2079 is the range limit)
4) make sure every character is a number 0-9

and then...

hopefully you don't have to go through this every time. But consider putting some kind of validaiton on the .csv creation process, if you do.

Upvotes: 0

IAmTimCorey
IAmTimCorey

Reputation: 16757

I would look at your data to see what you are passing in on line 129 for a date. I believe the issue you are seeing is because the date is outside the range for a SMALLDATETIME value (the range is January 1, 1900 through June 6, 2079). Maybe the year has a couple numbers switched around (for example, 9185 instead of 1985) or something like that. This would make sense then why you are getting the message "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value" when you convert the date to varchar first.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96570

CSv files are almost as bad as XLS and XLST files for containing bad data. I woudl suggest opening it and looking at the data in the row referenced and the rows near them.

Upvotes: 0

Ted Hopp
Ted Hopp

Reputation: 234807

One possiblity (or perhaps a typo in your post): your table has four columns; your CSV row has five values.

Another is that your SQL statement has an integer value. The CSV insert may be treating it as a string; in effect, this SQL:

INSERT INTO Test.BulkInsert (FirstName, LastName, Birthday, Gender)
VALUES ('1' 'Test', 'Me', '19851118', 'Male')

Upvotes: 0

Tom H
Tom H

Reputation: 47464

Have you checked your regional settings? I believe that BCP will use that when trying to import a datetime column.

Alternatively (and what it seems most people do), you can import into a staging table as a VARCHAR and then do an INSERT into your real table with a CONVERT statement over the datetime column.

Upvotes: 1

Related Questions