Post reply

BULK INSERT fixed length

  • July 24, 2006 at 2:09 am

    #173487

    Hello, I'm currently using Access to read those files in a temporary table. I was wondering if there was a more performant import possible for fixed length files, since BULK INSERT requires field separators.

    SKSLOVAQUIE                          SLOVAKIJE                          Y240706083426

    SISLOVENIE                             SLOVENIE                           Y240706083426

    Countrycode 2 characters

    CountryName1 35 characters

    CountryName2 35 characters

    European 1 character

    DatetimeField 14 characters

    Any hints?

     

     

  • July 24, 2006 at 9:27 am

    #651104

    Look up the SUBSTRING function.  I believe that will give you what you need...

    I wasn't born stupid - I had to study.

  • July 24, 2006 at 10:09 pm

    #651246

    Bulk Insert does not necessarily require field separators if you use a format file.  Or, just use Bulk Insert to import to a single column tables and then use SUBSTRING to parse it as Farrell suggested.  Both are quite fast.

    --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)

  • July 25, 2006 at 7:28 am

    #651324

    Use a text file source in DTS. Select fixed field format and just click the desired starting position for each field. Check the Use fast load option on the Transform Data Task properties.

    Kemp

  • July 25, 2006 at 2:12 pm

    #651466

    Thanks for the suggestions. I will try them out.

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

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