locked
Dynamic connection with Excel 2007 in SQL Server 2008 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello everyone,

    My requirement is: merge multiple Excel files in one place (like SQL Server) and then, when I change one Excel sheet, it should reflect in SQL Server.

    I have made connection using ".NET Framework Data Provider for ODBC".With this data has successfuly imported from excel to SQL Server, But when I make change in an excel sheet It is not reflecting the changes. I have made DSN and used it in connection string.

    So, how to reflect changes , or is it possible to reflect the changes from excel to SQL Server. Plaese help.

    Wednesday, September 9, 2015 10:53 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    Hi NoviceBee,

    Based on my understanding, if you'd like to reflect the changes in SQL Server, you don't need to import the data. Rather query the excel as a linked server so that it could get the data as it is in excel at current time.

    You could use the provider below to access an Excel directly. A VIEW can be created if you don't like to access the excel data every time in that statement kind of complicated.

    SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 XML;Database=c:\test\test.xlsx',
                'SELECT * FROM [Sheet1$]')

CREATE VIEW VIEW4TEST AS
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0 XML;Database=c:\test\test.xlsx',
                'SELECT * FROM [Sheet1$]')

SELECT * FROM VIEW4TEST

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support

    • Edited by Eric__Zhang Thursday, September 10, 2015 6:44 AM
    • Proposed as answer by Eric__Zhang Thursday, September 17, 2015 9:35 AM
    • Marked as answer by Eric__Zhang Monday, September 21, 2015 8:58 AM
    Thursday, September 10, 2015 6:43 AM