Shows error in creating excel sheet from sql.

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts