Post reply

Using a Linked Server to connect to an Excel File

  • February 18, 2005 at 9:49 am

    #164602

    Hello,

    I am less than a Newbie so this question may be very simple.

    I would like to be able to use a Linked Server to connect to an Excel file. The file is an inventory sweep that needs to be uploaded to an application that uses SQL Server for the backend. I have Linked other SQL servers but never a file.

    This has to be transparant. The end user will copy a file onto the server (in the linked location) and the Application will read the new file via the link.

    If anyone could provide steps to perform this link, I would appreciate it.

    Thanks,

    John Reiser


    John
    The star that burns twice as bright burns half as long.
    Pay close attention and be illuminated by its brilliance. - paraphrased by me

  • February 18, 2005 at 3:54 pm

    #543205

    There's an example of a linked server connection to Excel in SQL Books Online under "sp_addlinkedsrvlogin".

    E. Connects a specific login to an Excel spreadsheet (the linked server)

    This example first creates a linked server named ExcelSource, defined as the Microsoft Excel spreadsheet DistExcl.xls, and then creates a mapping to allow the SQL Server login sa to connect through to ExcelSource using the Excel login Admin and no password.

    EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\MyData\DistExcl.xls', NULL, 'Excel 5.0'

    GO

    EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

     
    That may help get you going -- you'll need to adapt as appropriate.
     
    - john

  • March 3, 2005 at 11:22 am

    #545300

    an alternative could also be to create a "File Watcher" package.

    http://www.databasejournal.com/features/mssql/article.php/10894_3319261_3

    Kindest Regards,

    Seth Washeck

    If nothing in this world satisfies me, perhaps it is because I was made for another world.
    C. S. Lewis

  • March 4, 2005 at 12:21 pm

    #545551

    hey,

    just be warned that sometimes linking to an excel file can give you data types other than you would expect. if your column has numbers in the first row, sometimes the importer will think it is number not text.

  • March 4, 2005 at 12:31 pm

    #545553

    Thanks for the replies.

    I ended up using a Data Transformation Service to create a database and import the information from the Excel file.

    I just schedule it to run once a day so yesterday's data are available today when we connect to the table.


    John
    The star that burns twice as bright burns half as long.
    Pay close attention and be illuminated by its brilliance. - paraphrased by me

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

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