Data uploading from excel to database

Question

I have one excel file which is updated daily, and I have to update the records in database(sql server 2005) daily.

What will be the query to fetch data from excel and update on database?

e.g the table structure like:

Empid   EmpName Emp.Des

001     Robert  Engineer

002 Philip  Trainer

003 John    Engineer

New excel is like:

004 Tom Engineer

005 Harry   Trainer

006 Samm    Engineer

So after the database update, the table will be look like:

001 Robert  Engineer

002 Philip  Trainer

003 John    Engineer

004 Tom Engineer

005 Harry   Trainer

006 Samm    Engineer

Answer 1

--Data Exporting from Excel to a NEW table in SQL Server

SELECT * INTO tblImportedFromExcel 
FROM OPENROWSET
(
    'Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=c:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]'
)

--Data Exporting from EXCEL to an EXISTING table in SQL Server

INSERT INTO tblImportedFromExcel 
SELECT * FROM OPENROWSET
(
    'Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=c:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]'
)

This would work for Excel 2003 file.

Answer 2

You can use SSIS to import Excel data to Database. Here is starting point.

Answer 3

If you have to do it using SQL you can do something like:

SELECT * INTO tableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\xmlimport_test.xls', 'SELECT * FROM [Test$]')

More examples

But there are some requirements to meet:

1. You have 32bit SQL Server (there is no 64 bit driver for Excel)

2. You have to allow adhoc distributed queries

   sp_configure 'show advanced options', 1;
   GO
   RECONFIGURE;
   GO
   sp_configure 'Ad Hoc Distributed Queries', 1;
   GO
   RECONFIGURE;
   GO
   

If you have 64bit SQL Server then you will get error: MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is used to run in apartment mode. There are some workarounds to this: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/4887d91f-6ac7-40c0-9fc8-5cdd0634e603

But the best way would be to create SSIS package and schedule it as SQL Server job