BULK INSERT/BCP Problem

[MarkButler]

I get multiple files daily that are in a flat file format with fields delimited with a @ and each record delimited with a CRLF. The first field is a file type identifier, the second field is a record type identifier. The first record in the file is a header record that contains the creator name and date. The last record of the file is a trailer record that contains the number of records in the file.

The problem that I have is the BCP or BULK insert expect that every record in the files are the same. I really do not care about the first and last records but the problem is that the copy fails due to an invalid format. I can easily bypass the first record using the -F2 parameter of the BCP. I do not know the number of records in the file so I can't use the -L parameter to specify the last record.

What can I do about the last record? Do I need to write a program to strip the first and last records from the imput?

 

[nigelrivett]

Would be better to receive the control records in a control file.

You can bcp into a single column table and split the fields up from there.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.