SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import Excel Spreadsheet to Database Tables


Import Excel Spreadsheet to Database Tables

Author
Message
peterhe
peterhe
SSC Eights!
SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)SSC Eights! (954 reputation)

Group: General Forum Members
Points: 954 Visits: 451
Comments posted to this topic are about the item Import Excel Spreadsheet to Database Tables



Servercentral-1016448
Servercentral-1016448
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 6
Thanks for your article. Do you also have a solution for sheet xml imports if the sheet name is change randomly or with a certain string?
Madhivanan-208264
Madhivanan-208264
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 476
For 32 bit version, here are some methods used to import/export to EXCEL from SQL Server table

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926



Madhivanan

Failing to plan is Planning to fail
Jim Cheong
Jim Cheong
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 11719
I recently came across this problem when we moved from Windows 32bit to Windows 64bit, using SQL 2005/2008 64bit. There are a lot of openrowset/opedatasource for older Excel 2003 xls.

I've finally found a free solution. It seems like Microsoft has replacement Office driver for "Microsoft.Jet.4.0" with its "Microsoft.ACE.OLEDB.12.0".

For 32 bits download the 2007 Office System DriverBigGrinata Connectivity Components

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

For 64 bits download the 2010 beta (Release June 2010)

http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=ja&displaylang=en

Installation of both doesn't require reboot. The 32/64 bits odbc will have the ACE driver for Access/Excel/dbase/cvs/txt after installation and the SQL Provider drivers "Microsoft.ACE.OLEDB.12.0" will be in your 32/64bit SQL Engine.

After installation, in SQL Engine, run the following

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

The "Microsoft.ACE.OLEDB.12.0" driver is backward compatible to Excel 2003, so you really can open xls files with this driver.

On a separate note, I've installed Office 2010 beta application, and it did not come with Microsoft.Jet.4.0, so my question is, is Microsoft replacing "Microsoft.Jet.4.0" with "Microsoft.ACE.OLEDB.12.0" on all their future development and slowly phrasing out the jet?
jvos.gm
jvos.gm
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 54
Great article, do you have a solution for importing a large csv file aswell? I tried your solution but keep on getting the “Invalid Excel worksheet. No data in the worksheet” error.
Servercentral-1016448
Servercentral-1016448
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 6
For import of csv files i would go for someting like this:
SELECT *
INTO tabelA
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data;','select * from tabelB.csv')
rcouldry
rcouldry
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 47
Great article - I'm looking for clues to solve the following problem.

Right now, my scheduled SSIS package (SQL08) to import Excel spreadheet data into SQL 08 works when the Excel spreadsheet is closed. It also works when the spreadsheet is open if I run the job manually from the Import Data wizard.

However, the scheduled SSIS package fails with the 'Jet engine cannot open file' error when the spreadsheet is open.

Is it possible to schedule an SSIS package to import data from an open Excel file to SQL Server?

Many thanks for any tips.
evolutional
evolutional
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 39
Jim Cheong (2/17/2010)
I recently came across this problem when we moved from Windows 32bit to Windows 64bit, using SQL 2005/2008 64bit. There are a lot of openrowset/opedatasource for older Excel 2003 xls.

I've finally found a free solution. It seems like Microsoft has replacement Office driver for "Microsoft.Jet.4.0" with its "Microsoft.ACE.OLEDB.12.0".

For 32 bits download the 2007 Office System DriverBigGrinata Connectivity Components

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

For 64 bits download the 2010 beta (Release June 2010)

http://www.microsoft.com/downloads/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en




Useful, thanks Jim
Rod Weir
Rod Weir
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 160
I wrestled with this for quite awhile. I ended up doing a very manual thing. I blogged about it here
http://www.prd-software.com/blogs/entryid/60/importing-or-converting-microsoft-excel-data-into-sql-server.aspx

Hope it helps someone.
Rod
RichB
RichB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1601 Visits: 1058
So have you also got a nice bit of TSQL that will parse a directory of 1000s of excel sheets, open them all and save them as xml?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search