Bulk Insert into SQL Server table of file created by TransferText method inserting only one row

Question

Post reply

Bulk Insert into SQL Server table of file created by TransferText method inserting only one row

Larry Burch

SSC Rookie

Points: 27
More actions
November 1, 2007 at 7:00 pm

#73790

I have a file created by the DoCmd.TransferText method that contains several rows. When loading this file into a SQL Server table using Bulk Insert (with the default row terminator of ) only the first row is being inserted.
I've tried other possible row termination options (\r, \r, \\ etc) without luck.
Does anyone know what row terminator the TransferText method uses to mark row ends?

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

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

??

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

Larry Burch SSC Rookie Points: 27 More actions · Answer 2

Well that was helpful. Was the ?? because you don't know or because the the fact that the '\ n' terminator character from my post went missing in action which made my message somewhat confusing?

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

Sorry, I didn't check my post and I submitted it... I had included backslash n and apparently, it didn't take on this forum....

Have you tried backslash n ??

(and, now you know where the double ?? marks came frome 😉 )

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

Larry Burch SSC Rookie Points: 27 More actions · Answer 4

Yes, backslash n was the set of characters that disappeared from my message and left it confusing.

I have found the problem and its interesting. The file had a header record (not a column-header, it held some data describing the file and a record-count) which had a different column content to the rest of the records, but had the same row terminator.

Although my BULK INSERT statement included FIRSTROW = 2, SQL Server got confused and combined the header row and the first data row and started its insert at the second data row (which was the third record in the file - which only had three records). I stripped out the header row via a VB Script and all was good.

What surprised me was that SQL Server expected all the file records to be the same and didn't seem to be able identify the terminator for the first record.

Never mind, thank you for your help.