Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Fields missed out when importing from Excel to SQL Server

Posted on 2007-10-21
1
Medium Priority
?
158 Views
Last Modified: 2013-11-30
Hello

Is anyone aware of any Excel import problems to SQL Server?

Basically - I'm importing from Excel using SQL Server 2005 SSIS.  
Weve had problems where some values just dont get imported.  

For example, we have size columns (for example, womens sizes) which were set as text (weve tried number also) format importing to varchar(255).  If the size included a hypen (such as 10-12) then it imported but if the size was a single value, such as 12, it didnt import.  So we added a fullstop after the single values, for example 12 become 12. and this worked.  Has anyone experienced this problem before?

We now have a new related problem where other sizes (men's neck sizes) are not all getting imported.  They are set to text format and importing to varchar(255).  Adding a fullstop after the value makes no difference this time.

Bit weird. Not sure what to do. Hope you can help?
0
Comment
Question by:tingleweb
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20120913
with openrowset, you should be able to get the correct data using the connection parameter IMEX=1;


Try openrowset with IMEX=1

INSERT INTO TBLA
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES;IMEX=1',
'SELECT * FROM [SheetName$]')
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question