bulk insert from text file to datetime field using a bcp format file 1

[FJK77]

I thought I had a handle on bcp format files and bulk inserts until I was required to insert an 8 character field (eg. 20020622) from a text file to a DATETIME field in a sql database table. I tried using the single quotes, which work on regular inserts: '20020622' but I am getting this error:

----------------------------

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 12 (Hire_Dt).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 12 (Hire_Dt).

----------------------------

for every line of the text file. I am using a format file to sort out the order of the columns and trim off extra characters in the text file, and I am not sure how the date fields should look in the format file. currently those fields in the format file look like this:

----------------------------

12 SQLCHAR 0 10 "," 8 Hire_Dt SQL_Latin1_General_CP1_CI_AS

----------------------------


I made the Host File Data Length 10 to account for the quotes. Do you think that is a mistake? Also, the SQL code, which i am running in Microsoft SQL Query Analyzer looks like this:

-----------------------------

BULK INSERT PAY_WORK
FROM '\\hostname\d$\temp2.txt'
WITH (
FORMATFILE = '\\hostname\d$\payrollformat.fmt'
)

-----------------------------

Am I using the wrong Host file data type or the wrong Collation in my format file??? If anyone knows what the format file should look like, please let me know.

Thanks,

Frances

 

[jonbgallant]

Have you tried using # around your dates instead of ' ?

That might work.

 

[tlbroadbent]

You shouldn't need any delimiters around the date. Using SQL 2000, I created an import and format file to match your conditions. The file imported successfully. Which version of SQL are you using.

Here is my code with the import and format files used.
[tt]
T-SQL script:

create table #dt
(EmpID int, HireDt datetime, EmpName varchar(30))
set nocount on
go
bulk insert #dt
from 'e:\ddnxfr\data\tlbtest.txt'
with (FORMATFILE = 'e:\ddnxfr\data\tlbtest.fmt')

Select * from #dt

Drop table #dt
Go

Import file:

01234520020211Brown, Sarah
02345620020212Kruger, Paul
12984520020213Lester, Gumball
98734520020214Rodriguez, Miguel

Format file:

8.0
3
1 SQLCHAR 0 6 "" 1 EmpID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8 "" 2 HireDt SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 30 "\r\n" 3 EmpName SQL_Latin1_General_CP1_CI_AS[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FJK77]

I am using Version SQL 8.00.382

Thanks to the people who have responded - I will try both suggestions today

 

[FJK77]

Terry - I ran some tests and got the date to work using your formatting. Thanks for your help, I should be able to get it to work now.

Jon - It didn't work any better with #, but I suspect I was doing something else wrong. Thanks for the help.