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]

Re: BCP import with Blank lines in text file

EricR (erucevice@gmai l.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;so me 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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[EricR]

Re: BCP import with Blank lines in text file


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

My format file is this.

8.0
8
1 SQLCHAR 0 50 "~" 1
OrderNum_
SQL_Latin1_Gene ral_CP1_CI_AS
2 SQLCHAR 0 50 "~" 2
SoldTo SQL_Latin1_Gene ral_CP1_CI_AS
3 SQLCHAR 0 50 "~" 3
Product SQL_Latin1_Gene ral_CP1_CI_AS
4 SQLCHAR 0 12 "~" 4
FulfillCount ""
5 SQLCHAR 0 50 "~" 5
HostID SQL_Latin1_Gene ral_CP1_CI_AS
6 SQLCHAR 0 10 "~" 6
User SQL_Latin1_Gene ral_CP1_CI_AS
7 SQLCHAR 0 12 "~" 7
Date SQL_Latin1_Gene ral_CP1_CI_AS
8 SQLCHAR 0 10 "\r\n" 8
Site SQL_Latin1_Gene ral_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]

Re: BCP import with Blank lines in text file

EricR (erucevice@gmai l.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~Qu antity~ID~User~ Date~Site\r\n
Order Number~Company Name~Product~Qu antity~ID~User~ Date~Site\r\n
Order Number~Company Name~Product~Qu antity~ID~User~ Date~Site\r\n
Order Number~Company Name~Product~Qu antity~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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[EricR]

Re: BCP import with Blank lines in text file

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]

Re: BCP import with Blank lines in text file

EricR (erucevice@gmai l.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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Roy Harvey]

Re: BCP import with Blank lines in text file

On 8 Jan 2007 06:45:53 -0800, "EricR" <erucevice@gmai l.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