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,
6  9998 
EricR (er*******@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, es****@sommarsk og.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
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
EricR (er*******@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, es****@sommarsk og.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
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.
EricR (er*******@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, es****@sommarsk og.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
On 8 Jan 2007 06:45:53 -0800, "EricR" <er*******@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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 while statement checking
for an empty string "" which I understand represents an EOF in Python.
The text file has some blank lines with spaces and other with blanks.
Thanks a lot.
|
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
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.
This has been working for 2 years, until today, when one of the data fields
|
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 lines, with a blank line
separating them...
e.g.
|
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 create a text file with
Access SQL INSERT/UPDATE statements and get Access to import and
execute this file.
Is this possible?
|
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 I connect directly to the URL, when I call the ".Read" method on the
XMLTextReader, I get the message:
"The XML declaration is unexpected. Line 8, Column 3"
If I open the URL in my browser and copy and paste the XML stuff (starting
| | |
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 forth)
and the groups that they belong into a SQL table.
LDIFDE is a utility that can create a csv file from an AD server. This
is a sample output:
dn: CN=rob camarda,OU=Corporate,OU=Geographic
Locations,DC=strayer,DC=edu
changetype: add
|
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 change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.
I have created a database that helps me sort out the data. Prior to
importing into the database, I have been copying the csv...
|
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
records are written out by the Java application the last item in each
record has a new line character attached to it so that a new line is
started for the next record. BCP gives me an error and doesn't import
anything if there is this blank line...
|
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 spreadsheets to the same table. I'm not getting a formatting error, just a "file did not import" message.
Also, when I do import one-to-one, Access inserts thousands of blank lines before the first line of imported data. How do I keep Access from doing...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
