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 |
|
hollyquinn
Starting Member
31 Posts |
 Posted - 2009-01-19 : 14:04:56
|
| Hi, I am trying to Import data from an Excel spreadsheet to an existing SQL Server 2005 table. The problem is that I don't want to import every column from the Excel spreadsheet. There are just a few that I need to import. I also won't be importing data into every column in the SQL Server table.I found the following query on this site, but I'm unsure how to manipulate it to get it to do what I want:Insert into tblCustomer Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Customers.xls;HDR=YES', 'SELECT * FROM [tblCustomer$]')I figured you would change 'SELECT * FROM [tblCustomer$]') to reflect the columns you wish to import, but it doesn't work. If I try that, I get the following error message:Msg 213, Level 16, State 1, Line 1Insert Error: Column name or number of supplied values does not match table definition.Can anyone help me out? Thank you in advance. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-19 : 20:00:26
|
| what did u actually type when you get the error ? |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-01-20 : 02:05:49
|
quote:
Originally posted by hollyquinn
Hi, I am trying to Import data from an Excel spreadsheet to an existing SQL Server 2005 table. The problem is that I don't want to import every column from the Excel spreadsheet. There are just a few that I need to import. I also won't be importing data into every column in the SQL Server table.I found the following query on this site, but I'm unsure how to manipulate it to get it to do what I want:Insert into tblCustomer Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Customers.xls;HDR=YES', 'SELECT * FROM [tblCustomer$]')I figured you would change 'SELECT * FROM [tblCustomer$]') to reflect the columns you wish to import, but it doesn't work. If I try that, I get the following error message:Msg 213, Level 16, State 1, Line 1Insert Error: Column name or number of supplied values does not match table definition.Can anyone help me out? Thank you in advance.
Make sure the number of columns of EXCEL and table are equal. Otherwise explicitly type the column namesMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Import Excel Data to SQL Server Query Issue