Skip to main content
nikos101
Inspiring
December 10, 2008
Question

BULK INSERT problem

  • December 10, 2008
  • 5 replies
  • 3285 views
I'm trying to import data from a csv file using the following query:

BULK
INSERT HIGH_LOW
FROM 'C:\Documents and Settings\administrator\Desktop\e.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

but am getting errors like this:

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



the csv file looks like this:

6,01/01/1999,1.4079,1.4079
6,04/01/1999,1.4153,1.3928
6,05/01/1999,1.4024,1.3985
6,06/01/1999,1.4276,1.3994
6,07/01/1999,1.4237,1.4096
6,08/01/1999,1.4215,1.4045
6,11/01/1999,1.4257,1.4132

And the table definition is

dataID int Unchecked Primary Key
currencyPairID int Checked
date datetime Checked
high float Checked
low float Checked

Thanks for any advice you can give me 🙂
This topic has been closed for replies.

5 replies

Inspiring
December 11, 2008
> I decided to do it like so: ...

Bulk insert is probably a lot faster. Though you do not need the parenthesis. Just use multiple selects, aliasing the values in the first statement

select 'value1' as colAlias1, 'value2' as colAlias2 union all
select 'value1', 'value2' union all
select 'value1', 'value2' union all
....

> but the datetime column all contained
> 01/01/1900 00:00:00

You left off the single quotes. So MS SQL treats the value as a numeric representation of a date: In other words, 1 divided by 1 divided by 1999, instead of the date '01/01/1999'.

However, even with single quotes "mm/dd/yyyy" is a very ambiguous format. It would be far better to use a format like "yyyy-mm-dd" instead.




nikos101
nikos101Author
Inspiring
December 11, 2008

I decided to do it like so:

INSERT INTO myTable
([currencyPairID]
,[date]
,[high]
,[low])

(select 6,01/01/1999,1.4079,1.4079) union all
(select 6,04/01/1999,1.4153,1.3928) union all
(select 6,05/01/1999,1.4024,1.3985) union all
(select 6,06/01/1999,1.4276,1.3994) union all
(select 6,07/01/1999,1.4237,1.4096) union all


etc
etc
20000 lines or so.

After 40 mins it finished but the datetime column all contained

01/01/1900 00:00:00

:(
nikos101
nikos101Author
Inspiring
December 10, 2008
So I would create the temporary table then do the query like so




BULK
INSERT @tempTable
FROM 'C:\Documents and Settings\administrator\Desktop\e.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
Inspiring
December 10, 2008
> So I would create the temporary table then do the query like so

Yes, if you decide to go that route. However, I do not know if you can use a @tableVariable. It may have to be a #temporaryTable. But you should verify that ...
nikos101
nikos101Author
Inspiring
December 10, 2008
Thanks Azadi for your helpful comment. I'm rather at a loss about how to implement the format file and the documentation is hard to understand. Can anyone give me a start on this?
Inspiring
December 10, 2008
See the "Using a Format File to Skip a Table Column" section in that link. It provides very detailed instructions
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Another option is to insert the data into a #temporary table first. The #temporary table would mirror the columns in your csv file. From there you could transfer the data into your permanent table. That is the approach I prefer because I get a lot of dubious data. It needs to be validated first, so it does not end up "trashing" the information in my permanent tables.
Inspiring
December 10, 2008
looks to me like you either need to include the values for your dataID
column in your data file, or create a format file and use the FORMATFILE
argument of BULK INSERT statement to to specify that the identity column
in the table is to be skipped when importing data...

this may be helpful: http://msdn.microsoft.com/en-us/library/ms188365.aspx


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/