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
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/techinfo/productdoc/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/techinfo/productdoc/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"
news:1109366729.311250.16040@l41g2000cwc.googlegroups.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/techinfo/productdoc/2000/books. asp