Bulk Insert newbie

[Muddmuse]

I'm trying to load data from a .txt file into SQL Server using the following:

BULK INSERT tblHours
FROM 'c:\test.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

My text file contains:

HourId,HourCnt,WorkDt,ProjId,TypeCd
1,2,'2005-07-30',3,'COMP'
2,2,'2005-07-31',3,'COMP'

I'm getting the following error:

"Bulk insert data conversion error (type mismatch) for row 2, column 3 (WorkDt)"

I've tried many different date formats with no success. Any ideas?

 

[DotNetGnat]

what is the datatype that you used for WorkDt in your sql server table...

-DNG

 

[gmmastros]

After playing with this a little, I notice that the apostrophes in the data file are actually considered to be part of the data. I create a temp table, but changed the date field to a varchar instead.

Do you have control over the format for the data file? If not, you could import the data in to a temp table, remove the apostrophes, and then insert in to the real table.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Muddmuse]

DotNetGnat - the datatype for WorkDt is datetime.

gmmastros - I do have control over the format of the data file. What should be changed to avoid the error when inserting the date field?

 

[DotNetGnat]

you are trying to insert a text string into the datetime and i think this is what is causing the problems...

try using cast() or convert() functions...something like

Convert(datetime,WorkDt)

-DNG

 

[gmmastros]

Well, it worked for me when I removed the apostrophes.

Data File:

HourId,HourCnt,WorkDt,ProjId,TypeCd
1,2,2005-07-30,3,COMP
2,2,2005-07-31,3,COMP

To test this, I created a table like this...

Create
Table tblHours(
HourId Integer,
HourCnt integer,
WorkDt smalldatetime,
ProjId integer,
TypeCd varChar(100))

Then, I used your code without changing it at all.

BULK INSERT tblHours
FROM 'C:\test.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ''',''',
ROWTERMINATOR = '\n'
)


Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Muddmuse]

Works like a charm (except I had to use FIELDTERMINATOR = ',', instead of FIELDTERMINATOR = ''',''', otherwise no rows were being inserted - but no errors either).

I appreciate the help!