Post reply

Regarding loading of flat file data

  • December 29, 2011 at 6:31 am

    #252621

    Hi,

    I have a data file and the structure of it looks like below (H-for header, D-for Data and T-for Trailer row)

    H|UKGI|2011-11-25|Year End|1.01|ASS_D_Cash|Legal_Entity|Market|Currency|Opening_Value

    D|AIUK|CAND|CAD|6234.151

    D|AIUK|EUR|EUR|5.21831

    D|AIUK|UK|GBP|77103713

    D|AIUK|US|USD|7631642

    D|Ocean Marine|CAND|CAD|0

    D|Ocean Marine|EUR|EUR|0

    D|Ocean Marine|UK|GBP|5523786

    D|Ocean Marine|US|USD|3449148

    D|Gresham|CAND|CAD|0

    D|Gresham|EUR|EUR|0

    D|Gresham|UK|GBP|4216715

    D|Gresham|US|USD|0

    T| 15| 97931243

    Point 1 - Now I want to load the data part of that data file in a sql server table. Means in the above file there are actually 4 columns and I want to load those 4 columns for those rows where the first column value is D.

    Also the structure of the data part of the data file can change dynamically. Means instead of 4 it might have 5 columns for any specific run. I can generate a dynamic sql like "Insert into <table_name> (col1,col2,..) Select col1,col2,.. from <table/file>".

    Now can this kind of dynamic sql be used to load the data directly from flat file to sql server table abiding point 1?

    If any solution like .net code or bulk insert can do that, then request you please provide a sample code for the same.

    Regards,

    Koushik

  • December 29, 2011 at 6:52 pm

    #1426755

    About the only way I've ever been able to import this type of Header/Detail/Trailer type of data is to first import it into a staging table just a long text string. Just create a table with 1 VARCHAR type column that can accommodate the widest record from your input file.

    You can import it with BULK INSERT or OPENROWSET and specify SINGLE_BLOB.

    Then delete all rows from the table that don't start with 'D' (the header/trailer records).

    From there you can use either parse the rows yourself or split each row into rows using Jeff Moden's Delimited8KSplit function.

    Todd Fifield

  • December 30, 2011 at 5:07 am

    #1426888

    ooops... bad post... removed

  • December 30, 2011 at 8:39 pm

    #1427149

    @koushikchandra,

    Are you all set or do you still need help with this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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