Bulk Insert flatfile question

Question

Bulk Insert flatfile question

Dan Cumpian

SSC Rookie

Points: 38
More actions
January 16, 2006 at 6:56 pm

#91379

I've been tasked with writing a text file parser and I'm having problems getting the file into a table (as raw text rows) in order to parse the file. We have a table with an AutoIncrement key and a Varchar(1000) field. We want to perform a "BULK INSERT" into the table by treating the input file as a file containing a single column. Unfortunately, I haven't been able to find a way around the what appears to be a limitation of BULK INSERT: Since the key column doesn't exist in the file to be imported, BULK INSERT refuses to perform the insert. Any ideas or suggestions on how to do this?
One alternative would be to DTS the file into a table, but we want the process to loop and process as many files as are waiting to be imported. Is there a way to do this using DTS? (We've already created a stored proc to read the filenames from the directory where the files are waiting to be processed.)
Thank you for any advice,
Dan

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Since you have one less column in the target table, you can use a format file to specify the required format. Type 'format files' in BOL to find out more.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

To do this, you will need a BCP format file OR, lose the IDENTITY column. BCP Format files can actually do all of the parsing in the final format for you. Look up {Format Files, Using Format Files} in Books OnLine.

Yes, I know you are using Bulk Insert but that uses the same format file as BCP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dan Cumpian SSC Rookie Points: 38 More actions · Answer 3

Wow, thanks for the quick response. Off to BOL...

Regards,

Dan