Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Import Excel Spreadsheet to Database Tables Expand / Collapse
Author
Message
Posted Tuesday, February 16, 2010 8:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:54 AM
Points: 531, Visits: 432
Comments posted to this topic are about the item Import Excel Spreadsheet to Database Tables


Post #866701
Posted Tuesday, February 16, 2010 11:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 4:56 PM
Points: 2, 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?
Post #866770
Posted Tuesday, February 16, 2010 11:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470

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
Post #866776
Posted Wednesday, February 17, 2010 12:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 9:31 AM
Points: 4, Visits: 11,713
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 Driver:Data 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?

Post #866781
Posted Wednesday, February 17, 2010 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 4:26 AM
Points: 1, Visits: 51
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.
Post #866784
Posted Wednesday, February 17, 2010 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 4:56 PM
Points: 2, 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')
Post #866790
Posted Wednesday, February 17, 2010 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 11:26 PM
Points: 1, 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.




Post #866825
Posted Wednesday, February 17, 2010 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 4:17 AM
Points: 5, 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 Driver:Data 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
Post #866839
Posted Wednesday, February 17, 2010 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 3:51 PM
Points: 6, Visits: 155
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
Post #866870
Posted Wednesday, February 17, 2010 4:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 1,070, Visits: 907
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?


Post #866872
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse