Post reply

Problems with Excel Import

  • October 17, 2011 at 3:43 am

    #260947

    Hi,

    I have an annoying problem with the import of data from Excel 2003 and I could do with some advice please.

    In essence I have an XLS worksheet with one column with contains either a nine digit number e.g. 123456789 or a nine-digit number with characters at the end, e.g. 123456789_AVC. I'm trying to import this into a varchar column on a table. However, although the numbers import fine (by casting as an int then a varchar), no matter what I try the alphnumeric rows always import as NULL.

    I would be very grateful please for any help anyone can give me.

    Thanks very much

  • October 17, 2011 at 4:09 am

    #1395212

    Take a look at http://support.microsoft.com/kb/194124

    HTH

  • October 17, 2011 at 8:22 am

    #1395359

    Try making sure that your alphanumeric is in the first row. When importing the excel file, it only scans so many rows before determining what the data type and length are. If you make the first row contain the alphanumeric data, it should work.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • October 18, 2011 at 8:33 am

    #1396014

    I had this problem frequently, initially the IMEX setting on it's own worked fine. However, to work fine the excel sheet data has to have the mixed char/numeric data in the first few rows of data, as determined by TypeGuessRows, which is defaulted to 8.

    If it is not convenient to have the excel sorted, increasing the TypeGuessRows value will help with that. To decide how many rows to set this to requires you to look at the data in excel and make an estimate of how many rows will be required to hit a mixed type row.

    This particular setting is a registry setting,

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

    If you are using a connection string to get to excel, then this may also work, add the following to the connection string "MaxScanRows=0" which should force the driver to scan the entire column of data to determine data type.

  • October 19, 2011 at 3:34 am

    #1396516

    Thank you for your help everyone. I'm please to say I cracked it in the end.

  • October 19, 2011 at 5:52 am

    #1396569

    David Lester (10/18/2011)

    I had this problem frequently, initially the IMEX setting on it's own worked fine. However, to work fine the excel sheet data has to have the mixed char/numeric data in the first few rows of data, as determined by TypeGuessRows, which is defaulted to 8.

    If it is not convenient to have the excel sorted, increasing the TypeGuessRows value will help with that. To decide how many rows to set this to requires you to look at the data in excel and make an estimate of how many rows will be required to hit a mixed type row.

    This particular setting is a registry setting,

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows".

    If you are using a connection string to get to excel, then this may also work, add the following to the connection string "MaxScanRows=0" which should force the driver to scan the entire column of data to determine data type.

    Yes! I forgot exactly how to do that, but we decided against making it 0 for performance reasons. 9x out of 10 it worked just fine. In fact it had also helped us identify some bad data. Which of course is common when using Excel 🙂

    Jared

    Jared
    CE - Microsoft

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

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