Post reply

SQL Server 2008 import wizard - importing excel xls workbook

  • October 8, 2012 at 9:28 am

    #263660

    I frequently utilize the import feature of SQL Server management studio for moving excel spreadsheet data in Sandbox tables on my organization's server. I wanted to ask a questions about an occurrence I recently came across when importing an Excel 97-2003 workbook into my sandbox db. I typically use the special data type in Excel when formatting columns in the sheet that'll wind up getting entered as float/nvarchar types then handling any conversions where necessary. When working through a specific spreadsheet, I noticed that various cells were missing from the import data and wanted to ask if anyone has experienced cases where random rows of data don't appear in the preview-view. Has this happened to anyone else when importing excel data into SQL Server? There isn't really any specific pattern to these entries which is why I'm puzzled as to why only some entries are missing. Just to re-iterate, I'm importing an excel 97-2003 worksheet (.xls) to a SQL Server Native Client 10.0 destination. Is there a way to attach screenshots? I've taken a few snippets that would probably help visualize what's going on.

  • October 8, 2012 at 1:44 pm

    #1546898

    Hit Reply to this post and then scroll down, look for the Edit Attachments button.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • October 8, 2012 at 2:20 pm

    #1546918

    opc.three, thanks for your response. I've attached what the import preview looks like. The rows with spaces result in NULL value entries when I SELECT ALL from the Sandbox table.

  • October 8, 2012 at 2:55 pm

    #1546936

    The attachment didn't appear to make it. Did you click the "Upload Attachments" button after choosing your file(s)?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • October 8, 2012 at 3:33 pm

    #1546959

    Here is the attachment.

  • October 8, 2012 at 4:01 pm

    #1546963

    Thanks. For future posts you can make the picture appear in your post by using the Image icon above the text window. Copy the link of the uploade image, yours is https://qa.sqlservercentral.com/Forums/Attachment12536.aspx, and put that in an image IFCode tag:

    [img]https://qa.sqlservercentral.com/Forums/Attachment12536.aspx[/img]

    And you'll get this:

    As for your issue, what else is in the column? Excel is somehwat like a database except that data-type is not strictly enforced. As such the Excel driver used to connect to the Workbook has to try to predict the data type of the incoming column and if the column contains mixed data this problem can show up. When you open the Excel file what is in the missing cells? Text? Are the columns formatted in a specific way? Youc an check by right-clicking and selecting 'format cells'.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply