Bulk Insert: end of file marker and end of line

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

Adam Haines

SSC-Insane

Points: 23217

March 5, 2008 at 2:49 pm

#785784

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

newbie-569492

Right there with Babe

Points: 730

March 5, 2008 at 2:51 pm

#785785

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

Adam Haines

SSC-Insane

Points: 23217

March 5, 2008 at 3:05 pm

#785794

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: 1000539 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)
Intro to Tally Tables and Functions

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

Adam Haines

SSC-Insane

Points: 23217

March 5, 2008 at 6:06 pm

#785839

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: 1000539 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)
Intro to Tally Tables and Functions