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

I would suggest making this into an SSIS package if it is to occur daily. — user847990
– user847990, Commented Jan 23, 2012 at 7:46

BartekR · Accepted Answer · 2012-01-23 08:26:53Z

2

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:

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

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

answered Jan 23, 2012 at 8:26

BartekR

3,9973 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

samar Over a year ago

Thanks for response But After executing I got an error message like Cannot process the object "SELECT * FROM [Sheet1$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object. Kindly suggest..

BartekR Over a year ago

I assume that you want to get data from Sheet1 and excel file is on the server ?

BartekR Over a year ago

Make sure of the following: 1. Excel file is on the server where you trying to import 2. Excel file does not have any spaces in it 3. Excel file has column names (first row) 4. Close the file if it is already being used by some other process

samar Over a year ago

Thanks, It is creating new table but not appending the record next time ! Any suggestion ? Samar

BartekR Over a year ago

Please, don't go further in comments. Try to update your question or ask a new one.

|

ceth · Accepted Answer · 2012-01-23 08:10:08Z

0

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

answered Jan 23, 2012 at 8:10

ceth

45.5k63 gold badges191 silver badges300 bronze badges

Comments

vmvadivel · Accepted Answer · 2012-01-23 16:47:01Z

0

--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.

answered Jan 23, 2012 at 16:47

vmvadivel

1,0675 silver badges7 bronze badges

Collectives™ on Stack Overflow

Data uploading from excel to database

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related