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, ignore errors problem

Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:\data.txt -b1 -m100 -c -t, \n -Sdatabase -Uuser -Ppassword

here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors

please help.

thanks
madhuWhy not use BULK INSERT where it accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause, respectively, which allows the user to specify whether constraints are checked during a bulk load.

bcp right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

bcp Question

Without using format file, is there a way for bcp to ignore single quote in
an import file? Thanks.Why are you not wanting to use a format file?
mason wrote:
> Without using format file, is there a way for bcp to ignore single quote i
n
> an import file? Thanks.
>|||Try -q option. More information in BOL.|||I have no problem using format files, but it would be simpler to do without
in terms of maintenance. -c option works well when there is no quote in
import files (about 36 of them). I just want to verify whether there is an
option somewhere to ignore quotes for bcp.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:OvDmLQsQGHA.5768@.tk2msftngp13.phx.gbl...
> Why are you not wanting to use a format file?
> mason wrote:|||Just tried. It's not what I wanted. I think -q affects identifiers, not the
data elements in a data file.
For example, a data record looks like this. I would like bcp to ignore
single quotes when importing.
1,'John','2006-03-08 12:00:00.000'
"Green" <subhash.daga@.gmail.com> wrote in message
news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
> Try -q option. More information in BOL.|||Would it be possible to create the data file without the apostrophes?
Looking at your example, it seems that you have a comma-delimited file
with apostrophes delimiting text fields but not numeric fields.
I needed to deal with that situation once - I was faced with quotes
rather than apostrophes, but the concept was the same. The only thing I
was able to figure out was to import the text file to a SQL Server table
via DTS because of the fact that some fields have a delimiting
character, some do not.
Good luck - hope this was helpful.
Carl
mason wrote:

> Just tried. It's not what I wanted. I think -q affects identifiers, not
> the data elements in a data file.
> For example, a data record looks like this. I would like bcp to ignore
> single quotes when importing.
> 1,'John','2006-03-08 12:00:00.000'
>
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
>
>|||Of course. I will most likely do that at the export end. Since the files may
also feed Sybase and Oracle, it gets ugly. Thanks.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:eJ2605sQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Would it be possible to create the data file without the apostrophes?
> Looking at your example, it seems that you have a comma-delimited file
> with apostrophes delimiting text fields but not numeric fields.
> I needed to deal with that situation once - I was faced with quotes rather
> than apostrophes, but the concept was the same. The only thing I was able
> to figure out was to import the text file to a SQL Server table via DTS
> because of the fact that some fields have a delimiting character, some do
> not.
> Good luck - hope this was helpful.
> Carl
> mason wrote:
>
>

BCP Import file - how to ignore top line

I have an earlier issue that has now been resolved, but now I am dealing
with a CSV file that is causing problems with my BCP import.
The csv file has column headers in the first row. Unfortunately, they
have spaces in the labels and the BCP utility cannot handle it. My BCP
command line says to start importing on line 2, but it still does not
like the spaces in the top row. Any suggestions besides modifying the
csv file since that is not an option?
*** Sent via Developersdex http://www.examnotes.net ***IGNORE. This has been resolved.
*** Sent via Developersdex http://www.examnotes.net ***

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam Caffrey
You could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:

> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>

bcp - server-side failure ignore?

Hi,
Is there any way to cause bcp to carry on processing the load file
ignoring any duplicates (i.e. true duplicates in my case)? It aborts
the load at the first violation. (Dropping the primary key constraint
is not an option).
It appears that the -m option does not apply to constraint checks or
any server side errors.
I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
joy either.
I know that I can load into a staging table and insert/select 'where
not exists'.
Are there other alternatives using bcp alone? If not, it seems like a
glaring omission.
Liam CaffreyYou could change the index to IGNORE_DUP_KEY - might work.
"liam.caffrey@.gmail.com" wrote:
> Hi,
> Is there any way to cause bcp to carry on processing the load file
> ignoring any duplicates (i.e. true duplicates in my case)? It aborts
> the load at the first violation. (Dropping the primary key constraint
> is not an option).
> It appears that the -m option does not apply to constraint checks or
> any server side errors.
> I have tried ROWS_PER_BATCH=1, no joy... playing with the -b option, no
> joy either.
> I know that I can load into a staging table and insert/select 'where
> not exists'.
> Are there other alternatives using bcp alone? If not, it seems like a
> glaring omission.
> Liam Caffrey
>