Post reply

Bulk Insert Task - Where Does The First Row Begin

  • January 25, 2007 at 4:12 am

    #116037

    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

    #685848

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply