Hi,

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.

Please suggest any ideas

Thanks
JP

JP (J*@discussions.microsoft.com) writes:
Show quoteHide quoteThe 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.

backup remote database to local hard drive or local database?
Error 0x80041010 WinMgmt when launching SQL SERVER MANAGEMENT STUD
How do you insert a carriage return in Management Studio?
SQL Agent keeps failing
SQLCMD error -100