Create an excel sheet
In the first 2 rows put some data, save the excel sheet as testing.xls on the c drive
Execute the command below
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
You will see 1 row since the first row will be used for header names
If you want 2 rows you need to add HDR=No like this
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;HDR=NO','SELECT * FROM [Sheet1$]')
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=SS:\testing.xls','SELECT * FROM [Sheet1$]')
The path can't be found (SS) you will get this error
Server: Msg 7399, Level 16, State 1, Line 1
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.].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing2.xls','SELECT * FROM [Sheet1$]')
When you spell the filename (testing2) wrong you get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet11$]')
When you spell the sheet name (Sheet11) wrong you get this error
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Select * from [Sheet11$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=Select * from [P2 2003 DJIA updates$]'].
Run the following OPENROWSET command
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls','SELECT * FROM [Sheet1$]')
When you type the wrong path (D) but the path exists, then you get this error
Server: Msg 7399, Level 16, State 1, Line 1
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.].
So hopefully the next time you get an error you can quickly figure out if it's the file name, sheet name or path name that is wrong
3 comments:
Hi,
I have one problem, Please give me the solution.
I have 30 columns in my database table "Importstage", and in my excel sheet I have 9 columns,
I'm importing excel data into Importstage table I'm getting the following error
"Server: Msg 213, Level 16, State 4, Line 3
Insert Error: Column name or number of supplied values does not match table definition."
I want insert 9 columns in my table and remaining 21 columns must be insert as null s
plz help me out
insert into table
SELECT *,null,null,null,null,null (add 16 more nulls) FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
or name the columns
insert into table(col1,col2,col3..list all 9 columns here)
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
suggest choose and insert 9 columns only (not verify it success or not)
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\testing.xls;HDR=YES', 'SELECT [the 9 columns] FROM [Sheet1$]')
SELECT [the 9 columns] FROM Importstage
Post a Comment