April 20, 2011 at 9:03 pm
Here's the problem:
I have an excel sheet that has a list of tasks on the left side and then people's names going across the top. Under each person's name is a list of numbers.
I want to import this list into SQL which should be no big deal. The first cell has some text about that the spreadsheet so I am unchecking the "first row has column names" checkbox. When I get the data in the columns that has the people's names, it gets imported as a float. So I get all the numbers and none of the names. I am using a basic data import in SSMS.
I have also tried importing using SSIS and trying to converting the data but the data doesn't even make it to the convert stage. I tried changing the column name in the excel spread sheet but that didn't work either.
I tried the reg change too. Found that one here:
http://www.bigresource.com/Tracker/Track-ms_sql-CemKbuW6/ [/url]
I also tried querying the spread sheet and got this beauty:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
This is from Excel 2003 .xls to SQL 2008 R2 dev.
Any help would be appreciated.
April 20, 2011 at 9:33 pm
I'm wondering if OPENROWSET would do the trick. Does the spreadsheet contain sensitive info? Could you attach it to this thread?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 9:47 pm
Here you go. It's not the same one I am using but I am getting the same results when importing this spreadsheet.
April 20, 2011 at 10:17 pm
Here is what worked for me...
1. Install the latest ACE provider then restart the SQL Server service: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
2. Then modify some setting on the provider in SQL Server:
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
3. And then code along these lines can be used to pull the data into a SQL table:
-- then you can get the data from the sheet using OPENROWSET
USE my_test_database_name
go
IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
SELECT IDENTITY( INT, 1,1 ) AS id ,
*
INTO #tmp
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\@\Example_SS_2.xls;',
'SELECT * FROM [Sheet1$]') ;
GO
SELECT *
FROM #tmp
WHERE id > 1
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 10:48 pm
Thanks for the reply. I was able to use the openrowset with Microsoft.Jet.OLEDB.4.0
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DevWork\example_ss.xls;Extended Properties=Excel 8.0')...[Sheet1$]
When I added your updates I got this:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What's the difference between Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0?
I was able to get results though and got this (image attached)
It seems that SQL see's the data in the row and since most of them are numbers, it automatically thinks they should all be a float.
April 20, 2011 at 11:06 pm
Yin Halen (4/20/2011)
Thanks for the reply. I was able to use the openrowset with Microsoft.Jet.OLEDB.4.0
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\DevWork\example_ss.xls;Extended Properties=Excel 8.0')...[Sheet1$]
Not sure if it matters but I was using OPENROWSET, not OPENDATASOURCE.
When I added your updates I got this:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
You can receive that error if you have the file open in Excel when you run your query.
What's the difference between Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0?
I am not sure...ACE is supposedly preferred for reading Excel 2007 and above and Microsoft released a 64-bit version of the ACE provider however has not to-date released a 64-bit Jet provider...read into that what you will.
I was able to get results though and got this (image attached)
It seems that SQL see's the data in the row and since most of them are numbers, it automatically thinks they should all be a float.
That is true, but to my knowledge it's not SQL Server doing that, the Excel driver reads ahead into the file evaluating the data in each column before making a best guess as to the data type before returning results.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 25, 2011 at 10:36 pm
FINALLY!!!
I got it.
This worked perfectly.
I guess I need to read more about excel's extended properties.
April 26, 2011 at 9:45 am
There is a lot going on in the thread you linked to...can you please post the connection string you are now using, the one that solved your data type problem?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 2:11 pm
Sorry about that.
Here is what solved my problem:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DevWork\ExcelTest\Example_SS.xls;Extended Properties="EXCEL 8.0;HDR=NO; IMEX=1"
""IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative."
April 26, 2011 at 2:20 pm
Thanks for posting back. Still using Jet huh 🙂 I was hoping you were using ACE.
I am looking to move everything to the ACE 12.0 under the new 2010 Office drivers since they work on 32 and 64-bit platforms with no changes to the connection strings and Jet still has no 64-bit option.
I did some quick searching around and it looks like IMEX is supported as an extended property on ACE 12.0, but I have not had a chance to try it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 5:02 pm
Dreaded Msg 7303!!!
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Can't get Ace to work.
Updated the latest ACE provider, restarted, spreadsheet isn't open. I can't get passed this error. From my searches it seems it is driving everyone crazy.
April 26, 2011 at 8:03 pm
Alright. Talk about monstrous journey into unknown territory.
I finally figured out the what was the matter. It had nothing to do with the spreadsheet. It had everything to do with permissions.
I had my service account running for SQL as my local user. This didn't have permission to the temp drive that SQL was trying to use.
I might not be explaining it well but for more info read here - http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/b9ad4df2-b256-4a33-911b-a06001250f9e/[/url]
Basically, I changed my service account back to LocalSystem and it worked.
It's hard to solve an "Unspecified error" but hopefully someone else will run into this thread in the future.
So finally problem solved.
And I was able to get what I want using ACE
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\DevWork\ExcelTest\example_ss.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1" ')...[Sheet1$]
April 26, 2011 at 8:33 pm
That's fantastic! I am happy you figured it out...and yes, I hope someone else finds this thread in a time of need. It's great to see that IMEX works with ACE too! Thanks for posting back.
PS you can can use "Excel 12.0" in your Extended Properties which should help when accessing workbooks created in newer versions of Excel...I think 8.0 is for workbooks created in Excel/Office 97 but the documentation is spotty as to which values will prevent/allow access to workbooks created in which versions...just for for thought if you have a working solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply