Home

BCP command with a format file to skip a header row issue.

%3CLINGO-SUB%20id%3D%22lingo-sub-557886%22%20slang%3D%22en-US%22%3EBCP%20command%20with%20a%20format%20file%20to%20skip%20a%20header%20row%20issue.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557886%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20using%20BCP%20command%20with%20format%20file%20to%20insert%20a%20large%20file%20i.e.%207GB%20in%20size%20export%20to%20database%20table%20without%20a%20header%20row%20then%20we%20unable%20to%20insert%20the%20first%20row%20of%20data.%3C%2FP%3E%3CP%3EI%20used%20-F1%20and%20-F2%20both%20in%20PowerShell%20command%20for%20skipping%20the%20header%20only%20but%20both%20are%20not%20working.%3C%2FP%3E%3CP%3EThe%20main%20problem%20is%20my%20header%20contains%20only%203%20columns%20and%20the%20data%20is%20about%2013%20columns%20that's%20why%20the%20first%20record%20is%20included%20with%20a%20header.%3C%2FP%3E%3CP%3EMy%20sample%20data%20is%20as%20follows%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHEADER%7CTAXLOT%7C20180104%3CBR%20%2F%3E%3CSTRONG%3E%3CU%3EA35972%7C01859M408%7C20170109%7C57.8270%7C504.84%7C0%7C0.00%7C0.00%7C%7C0%7C0%7CCLSIT1%7C%3C%2FU%3E%3C%2FSTRONG%3E%3CBR%20%2F%3EA35972%7C63872T729%7C20161027%7C211.3820%7C2088.46%7C0%7C0.00%7C0.00%7C%7C0%7C0%7CASXTU5%7C%3CBR%20%2F%3EA35972%7C63872T729%7C20161123%7C195.3990%7C1911.00%7C0%7C0.00%7C0.00%7C%7C0%7C0%7CASXTU5%7C%3CBR%20%2F%3EA35972%7C63872T729%7C20170404%7C3.8540%7C38.00%7C0%7C0.00%7C0.00%7C%7C0%7C0%7CASXTU5%7C%3C%2FP%3E%3CP%3ETRAILER%7CTAXLOT%7C20180104%7C18954837%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20sample%20data%2C%20the%20record%20that%20is%20in%20bold%20is%20not%20inserted%20in%20the%20database.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20please%20suggest%20any%20solution%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-557886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExclude%20first%20and%20last%20record%20using%20BCP%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Salman70
Occasional Visitor

While using BCP command with format file to insert a large file i.e. 7GB in size export to database table without a header row then we unable to insert the first row of data.

I used -F1 and -F2 both in PowerShell command for skipping the header only but both are not working.

The main problem is my header contains only 3 columns and the data is about 13 columns that's why the first record is included with a header.

My sample data is as follows : 

 

HEADER|TAXLOT|20180104
A35972|01859M408|20170109|57.8270|504.84|0|0.00|0.00||0|0|CLSIT1|
A35972|63872T729|20161027|211.3820|2088.46|0|0.00|0.00||0|0|ASXTU5|
A35972|63872T729|20161123|195.3990|1911.00|0|0.00|0.00||0|0|ASXTU5|
A35972|63872T729|20170404|3.8540|38.00|0|0.00|0.00||0|0|ASXTU5|

TRAILER|TAXLOT|20180104|18954837

 

In this sample data, the record that is in bold is not inserted in the database.

 

Can anyone please suggest any solution?