Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SMBDeveloper
Starting Member
2 Posts |
 Posted - 2007-09-21 : 12:51:30
|
I'm having trouble reading from an Excel file in SQL server.I run this code:select * from OPENRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=c:\Inetpub\wwwroot\bvig\Intranet\projected_daily_input\projected_daily_tbl_82160002.xls','SELECT * FROM [Sheet1$]')When I use a normal Excel sheet (I create a new document, enter in the data in Excel), this works fine. However, I'm reading from an Excel file in tabular format. That is, if you open it in Wordpad, you get tabular html code: "<table><tr><td...".When I try to read from this sort of Excel file, I get the error:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].How can I modify the SQL code to work? |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-09-22 : 00:08:14
|
| Sounds like you have the latest version of Excel that stores it's data in XML format. Instead of jumping through all those hoops, why don't you make a Macro (or VBS) and a button that exports the Excel data as a CSV file......or, address the Excel data using the XML document functions in SQL Server?--Jeff Moden |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-09-22 : 08:58:42
|
| HiIs it a one-off or a regular import.?You could consider using DTS, which give s you a fair bit of control.Jack Vamvas--------------------Need an IT job ? http://www.ITjobfeed.com |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Trouble reading from Excel