Bulk Import with header in text file

[cbanks]

I would appreciate some help on a procedure that I have. Using Bulk
Insert, I would like to import records from a text file. The issue I
have is the file contains a header - '1AMC_TO_Axiz' and a footer
'1AMC_TO_Axiz2". Using a format file, I can get the import to work by
editing the file and removing these two entries. Is there a way to
setup the format file to skip these two entries? My file currently
looks like this:
7.0
16
1 SQLCHAR 0 50 "|" 1 keyMemberNo
2 SQLCHAR 0 10 "|" 2 fldEffdate
.................
16 SQLCHAR 0 10 "\r\n" 16 fldNewRecord

Thanks
Charles

[getinked]

you should be able to write a batch file to open the text file and
replace those words w/ a NULL string.

[Erland Sommarskog]

(cb****@bjtsupport.com) writes:

I would appreciate some help on a procedure that I have. Using Bulk
Insert, I would like to import records from a text file. The issue I
have is the file contains a header - '1AMC_TO_Axiz' and a footer
'1AMC_TO_Axiz2". Using a format file, I can get the import to work by
editing the file and removing these two entries. Is there a way to
setup the format file to skip these two entries? My file currently
looks like this:
7.0
16
1 SQLCHAR 0 50 "|" 1 keyMemberNo
2 SQLCHAR 0 10 "|" 2 fldEffdate
................
16 SQLCHAR 0 10 "\r\n" 16 fldNewRecord

BCP is very squared, and there is no way to specify a footer or a header.
The one exception is when you can hide the header in the format somehow.
If the headers fits into the format, you can the FIRSTROW and LASTROW
options to skip them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[cbanks]

Thanks for the replies! At least I now know that I did not miss
something!