Bulk Insert
-
Hi, I have a data file which consists of data as below, Header xx|yy|zz aa|bb|cc Footer While iam inserting into table using bulk insert, this pipe(|) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert. BULK Insert #TmpStList FROM 'D:\PSC07\HRDATA\HR2SSTS_TRAINING.TXT' WITH (FirstRow=1,LastRow= 3,DATAFILETYPE='char',FIELDTERMINATOR = '|', ROWTERMINATOR = '\n',keepnulls) I want result like this fields1 2 3 xx yy zz aa bb cc but i got this fileds1 2 3 Headerxx yy zz aa bb cc I don't want to insert (|) into Header .
-
Hi, I have a data file which consists of data as below, Header xx|yy|zz aa|bb|cc Footer While iam inserting into table using bulk insert, this pipe(|) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert. BULK Insert #TmpStList FROM 'D:\PSC07\HRDATA\HR2SSTS_TRAINING.TXT' WITH (FirstRow=1,LastRow= 3,DATAFILETYPE='char',FIELDTERMINATOR = '|', ROWTERMINATOR = '\n',keepnulls) I want result like this fields1 2 3 xx yy zz aa bb cc but i got this fileds1 2 3 Headerxx yy zz aa bb cc I don't want to insert (|) into Header .
Your header row should have as many columns as data rows. So you should modify your data to:
Header||
xx|yy|zz
aa|bb|cc
Footer||Although you can start from second row using
FirstRow=2, note that: The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows.The need to optimize rises from a bad design. My articles[^]