473,379 Members | 1,330 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,379 software developers and data experts.

BCP import with Blank lines in text file

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,

Jan 4 '07 #1
6 9939
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
Jan 4 '07 #2

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

Jan 5 '07 #3
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
Jan 5 '07 #4
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.

Jan 8 '07 #5
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
Jan 8 '07 #6
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
Jan 9 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Ruben | last post by:
Hello. I am trying to read a small text file using the readline statement. I can only read the first 2 records from the file. It stops at the blank lines or at lines with only spaces. I have a...
1
by: DCM Fan | last post by:
Access 2K, SP3 on Windows 2K, SP4 All, I have an import spec set up with quoted Identifiers and comma-separated values. The text file is produced by a 3rd-party program of which I have no...
5
by: osmethod | last post by:
Hello, I'd appreciate any help which is offered to my problem. I'm using Acess 97. I have a flat text file which lists names and addresses. Sometimes, these can be 4 lines and sometimes 5...
16
by: klowe | last post by:
Hi, I'm an Access newbie and need to update an Access database from a web application. I'll need to add new records and also update existing ones. What I was thinking of doing is have my web app...
4
by: Ryan S | last post by:
I am trying to read an XML document generated by a web server using the XMLTextReader class, but the document generated appears to have some blank lines at the top that are causing problems. If...
1
by: rcamarda | last post by:
This is a tuffie, but I think I'll learn new techniques in SQL. I wish to put data from MS Active Directory and put it into a table. Specificly I want user information (first name, last name and so...
3
by: ninrulz | last post by:
I will try to explain my situation. I know that it is hard to offers solutions without fully understanding what people would like to achieve. I receive 2 csv files every month. The csv files...
0
by: erucevice | last post by:
I am trying to bcp a text file that is written out of a Java application. The text file has important order information that I need to bcp into a SQL Server 2000 database. The problem is that when...
3
by: MarcGA | last post by:
(Excel 2003, Access 2003, XP, novice user here) I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.