BULK INSERT fixed length

  • 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?

     

     

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

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

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

  • 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

  • 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