bcp/BULK INSERT and blank lines

Does anyone know of a way to make BULK INSERT or bcp ignore blank lines in the file? I am having trouble with a bunch of data files coming back with 1 or 2 blank lines at the end, and it causes the entire bcp to fail.

I suppose I could write a utility to trim the files but that seems a bit overkill. Any thoughts?

You will need to trim the data 'cuz bcp/bulk insert is just a _dumb_ data loader.|||One option is to use the -L parameter of BCP to specify the last row. This will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT.|||ah, -L! Thanks for the correction. I've never used that flag. It seems much simpler to just trim the blank lines...

BCP Transaction Does not roll back

Hi All,
I'm trying to import some data using the BCP command line utility.
I've set the maxerrors switch to 0. In case of an exception such as a
cast exception the import fails leaving the table state dirtied.
ie...with partial data imported.
From what I understand from a few other posts the transaction logs
only store the space alocated and not the actual data.
How does one ensure that the import is done in a transaction ?
Any suggestions/ideas will be great.
Regards,
Avinash
Can you post the command you are running
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Avinash" <avinashraj@.gmail.com> wrote in message
news:f22f61b0.0412050250.3d6ef381@.posting.google.c om...
> Hi All,
> I'm trying to import some data using the BCP command line utility.
> I've set the maxerrors switch to 0. In case of an exception such as a
> cast exception the import fails leaving the table state dirtied.
> ie...with partial data imported.
> From what I understand from a few other posts the transaction logs
> only store the space alocated and not the actual data.
> How does one ensure that the import is done in a transaction ?
> Any suggestions/ideas will be great.
> Regards,
> Avinash

BCP template using quoted text qualifiers

I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?I'd say the other table does not match your structure of the first table

Where's the bcp command and the DDL of the tables.|||The tables are exactly the same. They are duplicates of one another just in different databases.
When I tried to manually import the file that that was exported using BCP I got the error stating that I was trying to put a string into a numeric field. When I exported the table manually with quotes, I had no problem importing it in to the other table.
Do you know how to export a table with the quotes around the text in the file using bcp?|||You can either:
- create a view or;
- create a procedure.

Within either you can embed any character to be part of the data you're trying to export. If it's a view, - use OUT, else - QUERYOUT.|||So there is no switch with the bcp command to put in quotes?sql

BCP import error

I BCP out a table and then import back. While BCP IN, i get the following error:
#@. Row 303, Column 5: Invalid character value for cast specification @.#
#@. Row 296, Column 4: String data, right truncation @.#
Help plz!I think the data you try to insert into the table does not match the the ddl of the table itself. Are there any strange characters on row 303? What does row 303 look like anyway? The bcp-command you execute: does it consider strange characters and how does it map the file to the table? What's the layout of the table?|||Hi, thanx.
I don't suspect if there's any column binding problem, coz
I create a txt file from the same table, then truncate that table and import the data back from this text file.
The problem is multiple commas ",,," in the note-content column text.
Here's an example:

CREATE TABLE mydbo (id int, notedate datetime, note_content varchar(900), note_user char(3))
go
INSERT MYTAB
SELECT 1 , '2003-12-24 00:00:00' , 'AS PERINSTRUCTION ENTRD SC#@.12/16/03...71', 'SF2'
UNION ALL
SELECT 2 , '2004-02-07 00:00:00', 'CHECKED INF SCA@.#02/06/04.............17', 'SF2'
UNION ALL
SELECT 3 , '2004-03-26 00:00:00', 'GO TO, #, as per data .', 'SF2'

When i BCP out this table and then BCP into same table, i get the error for line 3. (right trucation)
How do i handle this, i have a CSV file that i have to import into a table, but data in single field contains multiple commas.

Howdy!|||Would it be possible for you to choose a different field seperator such as ; or [tab] ?

BCP fails with 134 columns

I'm trying to BCP from a csv file to a table, both have 134 columns. The error message coming back from BCP "SQLState = 37000, NativeError = 170

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '1'."

By reducing the number of columns in both csv file and table to around 20 column , the bcp call will work and the data will load into the table.Here's a sample that shows the problem

The command line I'm using to call bcp:-

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" em.dbo.mytable1 in c:\temp\_rrs\datafile.csv -S SQL_SERVER_NAME -U username -P password -t "|" -c -F 2

The version of BCP this is using :- 8.00.194 on SQL Server 2000.

Sample data in datafile.csv

1|Position|BBPLC|20060927|ALL|RDPBB092701.DAT||ALL

1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1

2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2

3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3

Creation script for table

if exists (select * from dbo.sysobjects where id = object_id(N'[mytable1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [mytable1]
GO

CREATE TABLE [mytable1] (
[1][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[2][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[3][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[4][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[5][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[7][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[9][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[10][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[11][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[12][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[13][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[14][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[15][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[16][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[17][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[18][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[19][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[20][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[21][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[22][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[23][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[24][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[25][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[26][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[27][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[28][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[29][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[30][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[31][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[32][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[33][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[34][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[35][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[36][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[37][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[38][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[39][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[40][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[41][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[42][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[43][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[44][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[45][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[46][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[47][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[48][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[49][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[50][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[51][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[52][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[53][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[54][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[55][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[56][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[57][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[58][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[59][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[60][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[61][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[62][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[63][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[64][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[65][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[66][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[67][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[68][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[69][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[70][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[71][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[72][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[73][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[74][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[75][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[76][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[77][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[78][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[79][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[80][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[81][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[82][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[83][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[84][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[85][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[86][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[87][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[88][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[89][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[90][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[91][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[92][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[93][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[94][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[95][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[96][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[97][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[98][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[99][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[100][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[101][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[102][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[103][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[104][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[105][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[106][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[107][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[108][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[109][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[110][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[111][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[112][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[113][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[114][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[115][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[116][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[117][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[118][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[119][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[120][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[121][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[122][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[123][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[124][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[125][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[126][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[127][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[128][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[129][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[130][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[131][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[132][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[133][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[134][varchar](6) COLLATE Latin1_General_CI_AS NULL
) ON [em_Group1]
GO

Any pointers on this would be very much appreciated

Regards

RichardS71

Eureka!

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table.

BCP error 22001 - String data, right truncation

I am using bcp to copy a table from one sql server to another. The bcp out
seems to work fine. However, when I attempt to bcp the data back in, I get
the following errror:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
The source SQL Server is: Microsoft SQL Server 2000 - 8.00.760
The destination SQL Server is: Microsoft SQL Server 2000 - 8.00.871
What do I need to do to resolve this error?
Thanks,
Leslie
First be sure the table schema on the target server matches the one on the
source server. After that, be sure the column delimiters in your data file
are such that any imbedded commas within the data are not causing a problem
when reading the data. As a possible solution, try using the Native Type
option (-n) when you export/import the data rather than saving it as text.
Beyond that, you'd need to post more information detailing exactly how you
are performing the bcp.
--Brian
(Please reply to the newsgroups only.)
"Leslie" <mason@.newsgroup.nospam> wrote in message
news:21F78426-D27C-474C-A48D-09CEEB22581D@.microsoft.com...
>I am using bcp to copy a table from one sql server to another. The bcp out
> seems to work fine. However, when I attempt to bcp the data back in, I get
> the following errror:
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> The source SQL Server is: Microsoft SQL Server 2000 - 8.00.760
> The destination SQL Server is: Microsoft SQL Server 2000 - 8.00.871
> What do I need to do to resolve this error?
> Thanks,
> Leslie
|||Brian,
Thanks for the input. The schema for the tables match. I think the problem
was imbedded CRLFs. The text data is entered via the web and I think people
are pressing enter at the end of a line and then starting a new line. Would
this sound like the kind of thing that would cause the problem?
Once I switched to native mode (-n) the problem went away.
Thanks for your help.
"Brian Lawton" wrote:

> First be sure the table schema on the target server matches the one on the
> source server. After that, be sure the column delimiters in your data file
> are such that any imbedded commas within the data are not causing a problem
> when reading the data. As a possible solution, try using the Native Type
> option (-n) when you export/import the data rather than saving it as text.
> Beyond that, you'd need to post more information detailing exactly how you
> are performing the bcp.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Leslie" <mason@.newsgroup.nospam> wrote in message
> news:21F78426-D27C-474C-A48D-09CEEB22581D@.microsoft.com...
>
>
|||The imbedded CRLF's would definitely cause the problem. Generally I have my
application developers strip those types of garbage characters out before
allowing it to be saved to the database. The characters usually provide
them as much of a problem when they later try to redisplay the data as when
you try to use it directly in the database.
Glad to hear it's working now!
--Brian
(Please reply to the newsgroups only.)
"Leslie" <mason@.newsgroup.nospam> wrote in message
news:66ED84D5-A469-4E29-BE10-0033B6F17A9A@.microsoft.com...[vbcol=seagreen]
> Brian,
> Thanks for the input. The schema for the tables match. I think the problem
> was imbedded CRLFs. The text data is entered via the web and I think
> people
> are pressing enter at the end of a line and then starting a new line.
> Would
> this sound like the kind of thing that would cause the problem?
> Once I switched to native mode (-n) the problem went away.
> Thanks for your help.
> "Brian Lawton" wrote:
|||Another option is to use DTS - which is IMHO much more convenient for
transfer between two SQL Server instances. That will avoide some of the
obstacles you encounter while transforming to CSV and back.
Kind regards
robert
|||A good option, thanks.
"Robert Klemme" wrote:

> Another option is to use DTS - which is IMHO much more convenient for
> transfer between two SQL Server instances. That will avoide some of the
> obstacles you encounter while transforming to CSV and back.
> Kind regards
> robert
>
|||"Robert Klemme" wrote:

> Another option is to use DTS - which is IMHO much more convenient for
> transfer between two SQL Server instances. That will avoide some of the
> obstacles you encounter while transforming to CSV and back.
> Kind regards
> robert
>
|||Which DTS connector do you recommend to import a BCP'd file? I usually use
BCP to pop tables out of one server into another one at a different site. As
BCP is not as user friendly, which DTS connector best simulates the BCP.
Thanks!
Irish
"Robert Klemme" wrote:

> Another option is to use DTS - which is IMHO much more convenient for
> transfer between two SQL Server instances. That will avoide some of the
> obstacles you encounter while transforming to CSV and back.
> Kind regards
> robert
>
|||Irish wrote:
> Which DTS connector do you recommend to import a BCP'd file? I
> usually use BCP to pop tables out of one server into another one at a
> different site. As BCP is not as user friendly, which DTS connector
> best simulates the BCP.
If you have direct connection between the two databases then a direct
transfer is the best option. Otherwise a backup might work, too. If you
have to do this often / on a regular basis then I'd look into replication.
robert
|||Robert:
There is not a direct connection between the two databases. Usually the
tables that I need to add into the database are for data repair of databases
and the addition of new rows for merging multiple database, etc. Therefore
rreplication is not going to work.
Is there a connector that can simulate the BCP format?
Irish
"Robert Klemme" wrote:

> Irish wrote:
> If you have direct connection between the two databases then a direct
> transfer is the best option. Otherwise a backup might work, too. If you
> have to do this often / on a regular basis then I'd look into replication.
> robert
>