BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility - Skip rows..

Hello All,

Does the BCP utility enable you to selectively import rows from a flat
file to a table ?

For example:

The first column in my flat file contains a record type - 1, 2..7
I only need to import types 1, 2, & 3

Can this be specified in the .fmt file ?

Thanks in advance
hharryhharry (paulquigley@.nyc.com) writes:
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?

It depends on the format of the records, but I would say that it is
highly unlikely. In fact, if the record types are heterogeneous, you may
not be able to write a format file to describe the file at all.

If they record types are homegeneous to fit into one format, you can bulk
load into a staging table, and the move on to the target table from there
with the interesting rows.

Also, if the records appears evenly in strict order: 12345671234567...
you can handle all as one big record. But I would not really expect
your file be like that...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There are 7 different record types within the file - all different
lengths.
I am going to parse the file via a dotnet exe.
Thanks for the assistance

Erland Sommarskog wrote:
> hharry (paulquigley@.nyc.com) writes:
> > Does the BCP utility enable you to selectively import rows from a
flat
> > file to a table ?
> > For example:
> > The first column in my flat file contains a record type - 1, 2..7
> > I only need to import types 1, 2, & 3
> > Can this be specified in the .fmt file ?
> It depends on the format of the records, but I would say that it is
> highly unlikely. In fact, if the record types are heterogeneous, you
may
> not be able to write a format file to describe the file at all.
> If they record types are homegeneous to fit into one format, you can
bulk
> load into a staging table, and the move on to the target table from
there
> with the interesting rows.
> Also, if the records appears evenly in strict order:
12345671234567...
> you can handle all as one big record. But I would not really expect
> your file be like that...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||One of our people created a table that was, essentially:

record_type int
rest_of_record varchar(long_enough)

loaded the table with a bcp format that was just a field for the record type
and a varchar field for the rest.

Then, he created a SP that used a cursor to walk through the table and T-SQL
decoded the records into various fields and did the inserts into destination
tables.

I found out what they'd done after we upgraded from SQL Server 6.5 and the
process stopped working because the rows no longer came out in
First-In/First-Out order. The SP was almost entirely unmaintainable,
anyway.

We wrote something maintainable and understandable in VB6 (with record
types, liberal use of constant declarations) in a few hours. Much better. *

I wonder if you could use DTS to accomplish what you want? If you haven't
already, why not drop the question into microsoft.public.sqlserver.dts?

* If I remember correctly, the emergency workaround was to add an identity
column to the work table and have the cursor pull rows in sorted order on
the identity column.

"hharry" <paulquigley@.nyc.com> wrote in message
news:1109366729.311250.16040@.l41g2000cwc.googlegro ups.com...
> Hello All,
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?
>
> Thanks in advance
> hharry|||dh (dh@.news.net) writes:
> One of our people created a table that was, essentially:
> record_type int
> rest_of_record varchar(long_enough)
> loaded the table with a bcp format that was just a field for the record
> type and a varchar field for the rest.
> Then, he created a SP that used a cursor to walk through the table and
> T-SQL decoded the records into various fields and did the inserts into
> destination tables.

Yeah, that's a normal approach to do it. You write a program that reads
the file. But T-SQL is a poor choice for the task, since it's not good
on string handling. And while you can add an IDENTITY, you cannot be
really sure that it works anyway.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP

Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?
It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:

> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) only
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?

BCP

Hi
I am trying to do a bulk insert of a minimum of 77000 records from a flat
file using the bcp utility. When I run the utility (in a batch process) only
46214 records go into the table. No errors are registered for the same.
Does the utility have some sort of data or time limit? If so, how do I
bypass/increase this?It looks like there is an EOF character at the end of the faulty record.
Have you try to open the flat file in a arich editor to locate the record
and check if there were something wierd wit it compare with others?
Have you tried to split your flat file in 2 at the faulty record and try to
load it having it on top?
You can batch the amout of record bcp will load but that does not look to be
the problem, benn loading much more bigger files than that in the past with
no problem. Most of the time the file is bad.
Hope this helps
Andre
"kailux4" wrote:

> Hi
> I am trying to do a bulk insert of a minimum of 77000 records from a flat
> file using the bcp utility. When I run the utility (in a batch process) on
ly
> 46214 records go into the table. No errors are registered for the same.
> Does the utility have some sort of data or time limit? If so, how do I
> bypass/increase this?