Shows error in creating excel sheet from sql.

Posted by Sathiya_Narayanan under Sql Server on 8/24/2012 | Points: 10 | Views : 1977 | Status : [Member] | Replies : 1
Hi,

INSERT INTO OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=\\localhost\satya\test123.xlsx;Extended Properties=Excel 12.0 XML;HDR=YES',
'SELECT * FROM [Sheet1$]')

when executing the query it shows error as.

Insert Error: Column name or number of supplied values does not match table definition.

SATHIYANARAYANAN


Responses

Posted by: Pandians on: 8/24/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!

You trying to Insert some record into the Excel!

You can not create a new Sheet! You just have Sheet1 on that excel file

Root Cause of the Err is:
Provider could not refer multiple column(s)

Normally, Provider can refer single column. If you try to insert only single column then, It'll work fine!
INSERT INTO OPENROWSET

('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=d:\backup\book3.xlsx;Extended Properties=Excel 12.0 XML;HDR=YES',
'SELECT * FROM [Sheet1$]')
select Column1 from TableName
But, If you try to insert multiple columns then, you have to do some initial step here!

1. Just open the Excel
2. Give column name on each columns in first Row of Sheet1
3. save the Excel
4. execute the script again.. It'll work!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response