Re: BCP format file
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 4 Jul 2006 22:13:02 +0000 (UTC)
JP (JP@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have a data file that varies in no of field for each records
e.g.
field1|field2|field3
field1|field2
field1|field2|field3|field4
The fields are seperated by "|" and the records are seperated by '\r\n'
The field values are also a varying length.
I cannot use TERMINATOR="\r\n" for field2 since the 1st and the 3rd record
have TERMINATOR="|" for field2
I would like a generic solution to this problem. The field are not of any
fixed length.
The problem is somewhat vaguely state it, but I take it that if there
only two fields one line, the remaing fields are to be NULL.
There is no way you can do this with BCP, least of all generically. You
would need to preprocess the file, to add the missing delimiters. Or
use the bulk copy API and bulk from variables. A third option is to
bulk into a staging table which has the minimum number of fields in
in the file + 1. For the example above, that would be three fields,
with the last one being an overflow field that you would have to
split. But this sounds less palatable to me.
BCP is a very squared tool that reads field by field from a file, and
BCP does not really know what a row terminator is - it only knows
field terminators.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Tools to script a database?
- Next by Date: Maintenance Plan Error since installing SP1
- Previous by thread: Tools to script a database?
- Next by thread: Maintenance Plan Error since installing SP1
- Index(es):
Relevant Pages
|
Loading
