Post reply

SQL Express with Excel

  • March 11, 2008 at 11:41 am

    #379168

    Hi All,

    Does anyone knows how to connect to Excel from SQL Express?

    Thanks.

  • March 12, 2008 at 10:09 am

    #788674

    Hi,

    Read about linked servers or openrowset function. There are examples how to connect to excel.

    Piotr

    ...and your only reply is slàinte mhath

  • March 27, 2008 at 9:02 am

    #794666

    This Microsoft Knowledge Base Article will give an overview of your options for working with Excel and SQL Server.

    How to import data from Excel to SQL Server

    http://support.microsoft.com/kb/321686

    --

    You will need to read up on the topics mentioned in SQL Server Books Online (BOL).

    Topics:

    SQL Server 2005 Books Online (September 2007)

    Linking Servers

    http://technet.microsoft.com/en-us/library/ms188279.aspx

    --

    SQL Server 2005 Books Online (September 2007)

    sp_addlinkedserver (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms190479.aspx

    Example for Linked Server:

    E. Using the Microsoft OLE DB Provider for Jet on an Excel spreadsheet

    To create a linked server definition using the Microsoft OLE DB Provider for Jet to access an Excel spreadsheet, first create a named range in Excel by specifying the columns and rows of the Excel worksheet to select. The name of the range can then be referenced as a table name in a distributed query.

    EXEC sp_addlinkedserver 'ExcelSource',

    'Jet 4.0',

    'Microsoft.Jet.OLEDB.4.0',

    'c:\MyData\DistExcl.xls',

    NULL,

    'Excel 5.0'

    GO

    To access data from an Excel spreadsheet, associate a range of cells with a name. The following query can be used to access the specified named range SalesData as a table by using the linked server set up previously.

    --

    OPENROWSET

    SQL Server 2005 Books Online (September 2007)

    OPENROWSET (Transact-SQL)

    http://msdn2.microsoft.com/en-us/library/ms190312.aspx

    Example for OPENROWSET:

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\book1.xls', [Sheet1$])

    Hope This Helps,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • May 9, 2008 at 4:06 am

    #812906

    Hi,

    I have SQL Server Management Studio Express in my machine.I want to connect an excel sheet,located in local drive.I have made a database on SQL and write a query to connect the excel:

    SELECT *

    INTO [dbo].[BG_test_Temp]

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=F:\BG Format.xls;IMEX=1',

    'SELECT * FROM [BG Registrer$]')

    a new table is created on the newly created database,automatically creating the table structure and populating all the data from excel file.

    Now problem is,

    1. If i want to insert a new row to the excel file,or modify or update it the updated data doesnot reflect to the SQL table.I have to execute the above query again to view the updatation.

    Is there any procedure the SQL table will automatically updated whenever i insert data in the excel file?

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

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