BCP import with Blank lines in text file

[EricR]

I am trying to bcp import a text file into a SQL Server 2000 database.
The text file is coming out of a java application where order
information is written to the text file. Each record is on it's own
row, so the last item in each record has a new line character at the
end of it to create the next row. This works well in creating the file
however bcp does not like to import this text file with the extra blank
line at the end. If I change the new line character to the beginning of
the records then there is a blank line at the top of the text file,
which bcp also does not like. Does anyone have any suggestions for me
to get around this issue?

Thanks,

[Erland Sommarskog]

EricR (er*******@gmail.com) writes:

I am trying to bcp import a text file into a SQL Server 2000 database.
The text file is coming out of a java application where order
information is written to the text file. Each record is on it's own
row, so the last item in each record has a new line character at the
end of it to create the next row. This works well in creating the file
however bcp does not like to import this text file with the extra blank
line at the end. If I change the new line character to the beginning of
the records then there is a blank line at the top of the text file,
which bcp also does not like. Does anyone have any suggestions for me
to get around this issue?

For BCP questions it helps if you post the table definition, any format
file you use and a sample of the data file (if possible as an attachment).

If I understand this correctly, your file has a blank line beetween
every line with tect on:

231;yadayada;some more yadayada

2345;tuttelitugård;straight on!

Specifying the row terminator as \r\n\r\n or \n\n if the file has
Unix format.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[EricR]

Sorry for not including an example. My text file looks like this.
Order Number~Company Name~Product~Quantity~ID~User~Date~Site
Order Number~Company Name~Product~Quantity~ID~User~Date~Site
Order Number~Company Name~Product~Quantity~ID~User~Date~Site
Order Number~Company Name~Product~Quantity~ID~User~Date~Site

My format file is this.

8.0
8
1 SQLCHAR 0 50 "~" 1
OrderNum_
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "~" 2
SoldTo SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "~" 3
Product SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 "~" 4
FulfillCount ""
5 SQLCHAR 0 50 "~" 5
HostID SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 10 "~" 6
User SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 12 "~" 7
Date SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 10 "\r\n" 8
Site SQL_Latin1_General_CP1_CI_AS

When the text file comes out of my Java application the last line
record has a carriage return which creates the blank line at the end of
the file. There are no blank lines between records. Thanks

[Erland Sommarskog]

EricR (er*******@gmail.com) writes:

When the text file comes out of my Java application the last line
record has a carriage return which creates the blank line at the end of
the file. There are no blank lines between records. Thanks

Ah, I think misunderstood you. So you file looks like this:

Order Number~Company Name~Product~Quantity~ID~User~Date~Site\r\n
Order Number~Company Name~Product~Quantity~ID~User~Date~Site\r\n
Order Number~Company Name~Product~Quantity~ID~User~Date~Site\r\n
Order Number~Company Name~Product~Quantity~ID~User~Date~Site\r\n
\r\n

Since I don't know Java, I need to ask a really stupid question: do
you really need that extra \r\n at the end? Can't you prevent Java
from adding it? Because, it can be quite difficult to get BCP to handle
that file. BCP believes in complete records so to speak.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[EricR]

Since I don't know Java, I need to ask a really stupid question: do
you really need that extra \r\n at the end? Can't you prevent Java
from adding it? Because, it can be quite difficult to get BCP to handle
that file. BCP believes in complete records so to speak.

I can stop it from adding the new line character in Java but that is
easier said than done considering how my code is written. i have no way
of knowing how many records are going to be written to the text file
before the file is written. That is why I was hoping that there may be
some way within bcp to get it to ignore the last line, but the more I
play around with it the more I see that this is probably not possible.
Thanks for your suggestions.

[Erland Sommarskog]

EricR (er*******@gmail.com) writes:

I can stop it from adding the new line character in Java but that is
easier said than done considering how my code is written. i have no way
of knowing how many records are going to be written to the text file
before the file is written.

Not that I know how your code is written, but it does sound strange to
me, because as I understand it, you have two line feeds at the end.

That is why I was hoping that there may be some way within bcp to get it
to ignore the last line, but the more I play around with it the more I
see that this is probably not possible.

There is an option -L to specify the last record to bulk-copy, but I would
not expect this to prevent any error since that last record is incomplete.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Roy Harvey]

On 8 Jan 2007 06:45:53 -0800, "EricR" <er*******@gmail.comwrote:

>I can stop it from adding the new line character in Java but that is
easier said than done considering how my code is written. i have no way
of knowing how many records are going to be written to the text file
before the file is written. That is why I was hoping that there may be
some way within bcp to get it to ignore the last line, but the more I
play around with it the more I see that this is probably not possible.
Thanks for your suggestions.

Perhaps you can simply write a simple utility program that removes the
problematic line, and run the file through that before BCP. That is
the approach I have used for such problems in the past.

Roy Harvey
Beacon Falls, CT