SQL Bulk Insert -- Mismatch

Question

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

what does row 129 look like? (actually show 127 - 130 please) — Cos Callis
– Cos Callis, Commented May 18, 2011 at 18:58

Tom H · Accepted Answer · 2011-05-18 18:58:31Z

1

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.

answered May 18, 2011 at 18:58

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

GrafixMastaMD Over a year ago

tried to convert it. It does not consider it a string because if I try to varchar it it gives me a mismatch as well.

Tom H Over a year ago

You could import it as an INT (my_dt_col = CAST(CAST(my_int_col AS VARCHAR) AS DATETIME)) and then do the conversion in T-SQL or a format file might solve the problem.

GrafixMastaMD Over a year ago

I re looked at my code, I set the VARCHAR to little. I noticed this when I checked the error a bit more careful. Thanks for making me go back down that step. It didn't make sense I couldn't do this. Thanks again.

GrafixMastaMD Over a year ago

Shoot, just tried again the correct way I remember the error I got. Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

GrafixMastaMD Over a year ago

I found the issue, the issue was in the data. I had some dates that were 19550229

|

Ted Hopp · Accepted Answer · 2011-05-18 18:58:50Z

0

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')

answered May 18, 2011 at 18:58

Ted Hopp

235k48 gold badges412 silver badges533 bronze badges

1 Comment

GrafixMastaMD Over a year ago

Yes, I had the gender in there as well at one point. It was a typo thank you. I have it in there on my real code. Sorry.

HLGEM · Accepted Answer · 2011-05-18 19:17:43Z

0

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.

answered May 18, 2011 at 19:17

HLGEM

97k15 gold badges120 silver badges191 bronze badges

2 Comments

GrafixMastaMD Over a year ago

I have, I have also even created a super simple csv and it still failed.

HLGEM Over a year ago

Do you have null values in the ID column? It is a required field.

IAmTimCorey · Accepted Answer · 2011-05-18 23:45:46Z

0

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.

answered May 18, 2011 at 23:45

IAmTimCorey

16.8k5 gold badges42 silver badges77 bronze badges

Comments

Chains · Accepted Answer · 2011-05-19 00:09:11Z

0

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.

answered May 19, 2011 at 0:09

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Collectives™ on Stack Overflow

SQL Bulk Insert -- Mismatch

5 Answers 5

9 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related