January 25, 2007 at 4:12 am
Hi,
I am using the Bulk Insert Task to bulk copy data from a flat file to a SQL Server 2005 table. The flat file contains pipe ( | ) delimited columns and several thousand records which are {CR}{LF} delimited. The file also contains a header record and trailer record, which contain several words but also contains a ( | ) symbol among those words.
E.g.:
HEDR | yadi yadi yada
500 | Data Data | More Data | 600
460 | Datum | More More | 705
550 | Data | Data | 603
FOOTR | yadi yadi yada
I need to ignore the header and trailer records and just pickup the proper records. But even though I manually set the First Row property to 2 and the Last Row property to 4, It does not pickup the first true record i.e. the record which begins with 500, and if I set the First Row to 1, it throws me an error citing 'truncation error' or similar. I think it is taking the first record (i.e. header row along with the first row as one, and since there are now more pipes ( | ) the error is thrown)
I've tried setting different values for these properties but to no avail... Any help will be deeply appreciated...
Thanks
Gogula
January 26, 2007 at 8:18 am
i typically bulk insert EVERYTHING into a staging table, and then in the staging table i would Select * from the column where column not (like HEDR%') and not like ('FOOTR%') and then chop the remains up with the split() function based on the pipe character and insert them into the destination table.
when it is in the staging table, i can valdiate things like the number of pipe characters are valid for all rows and stuff like that, where that can trip you up if you are importing it directly.
alternatively you could pre-process the file and remove the rows of data with HEDR and FOOTR in them, before the bulk insert
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply