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.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Import Excel Data to SQL Server Query Issue

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 1
Insert 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 ?
Go to Top of Page

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 1
Insert 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 names


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -