Fields missed out when importing from Excel to SQL Server

Posted on 2007-10-21
Last Modified: 2013-11-30

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?
Question by:tingleweb
    1 Comment
    LVL 142

    Accepted Solution

    with openrowset, you should be able to get the correct data using the connection parameter IMEX=1;

    Try openrowset with IMEX=1

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now