Home Forums SQL Server 2005 Development Bulk insert from file having varying number of columns RE: Bulk insert from file having varying number of columns

  • November 12, 2013 at 8:14 am

    #1665740

    Lowell (11/12/2013)

    Dorthy Jeff Moden has a couple of posts on this, which i'd saved in the past:

    here's the specific threads in question:

    http://www.sqlservercentral.com/search/?q=%22Headerless+Ragged+Right%22

    basically, he uses some dos commands to make sure the file has headers if it didn't already, and then uses a text-file linked server which automatically treats the files as having NULLS for missing columns(which in turn make the columns for the import somewhere else.)

    those old posts refer to 32 bit linked servers usign the JET driver, so if you need a 64 bit text based linked server, it's like this example:

    select * from OpenRowset('MSDASQL',

    'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\;'

    ,'select top 10 * from C:\Data\MailItems.txt')

    Sadly, there are 3 things that are now wrong with the method I posted... 32 bit limitations, performance, and privs. A flat load followed by a split and a crosstab are frequently more effective not to mention the fact that OPENROWSET requires "SA" privs to run. I'll see if I have the time to demo some code after work.

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