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.

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.