Bulk Insert: end of file marker and end of line

Question

Post reply

Bulk Insert: end of file marker and end of line

Viewing 11 posts - 16 through 25 (of 25 total)

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

newbie-569492 Right there with Babe Points: 730 More actions · Answer 1

But doesn't the bulk insert need that marker for the end of file?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

It will. It doesnt need a blank line to end the file.

My blog: http://jahaines.blogspot.com

newbie-569492 Right there with Babe Points: 730 More actions · Answer 3

I'm sorry, I'm confused. My txt file does contain the square bracket on the last line, is this correct or not? If it is my bulk insert is reading it in as a record, how can this be corrected?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

If you have a little square on the last line. This usually means you have a hard return. To resolve simply delete the last line. It should look blank when you open the file in notepad.

My blog: http://jahaines.blogspot.com

newbie-569492 Right there with Babe Points: 730 More actions · Answer 5

So the bracket is really invisible to the user? It's just something SQL knows somehow?

Thanks for your patience!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Well the little square is hard return, in this case. The hard return consists of the CR\LF characters, which is the default row terminator. SQL was thinking you had an extra row when you did not.

And yes the CR\LF is invisible when you open notepad.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

You guys are crossing your wires just a bit. If you're seeing a square bracket at the end of the text FILE, you should remove that last line. The square bracket is not the EOF marker... I guarantee it.

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

newbie-569492 Right there with Babe Points: 730 More actions · Answer 8

i've got the bracket or end-of-file issue resolved, however now I have this issue:

As stated previously, my bulk insert sp inserts records into a table from a txt file. The txt file has each record on a separate line, meaning we have \r

However, in the table the first char of the first row has a value, but all of the following records who a blank in that position. What could the issue be?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

Does the row in question contain anything else or just the one character?

It sounds like your field/column terminator is set incorrectly.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

newbie (3/5/2008)
i've got the bracket or end-of-file issue resolved, however now I have this issue:
As stated previously, my bulk insert sp inserts records into a table from a txt file. The txt file has each record on a separate line, meaning we have \r
However, in the table the first char of the first row has a value, but all of the following records who a blank in that position. What could the issue be?

We can only guess... if there's nothing private in the file, upload it as an attachment and let's see...

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

newbie-569492 Right there with Babe Points: 730 More actions · Answer 11

Unfortuanatly I cannot upload the file, however the values are similar to this example:

This is what is in the text file:

12345

54678

90876

34205

In the table, after the bulk insert, it has the first value correctly, but all the following values shift over by one and the first char is blank.