Post reply

Error importing from EXCEL

  • February 9, 2005 at 4:49 am

    #185064

    Hi People,

    really hope you can help me with this one - I'm trying to import some data from an excel (2003) spreadsheet. It's just straight text in four columns and I have specified that all columns are nvarchar(255) apart from the fourth column which is nvarchar(1000)

    This should be plenty for the data in the  fourth column as the max length of an item is 433.

    My import package falls over at the same spot every time with this error message:

    'Error at Source for Row number 43. Errors encountered so far in this task:1. Data for source column 4 ('Notes1') is too large for the specified buffer size.'

    Am I doing something basically wrong?  Row 43 isn't a large row (180), I just think that some critical number is reached at this point.

    Thanks in advance...

    Tim

  • February 9, 2005 at 6:33 am

    #541342

    hello people,

    Sorted this one myself... managed to find this

    http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3BQ281517

    cheers anyway

     

    Tim

  • February 10, 2005 at 8:04 am

    #541584

    I feel your pain as I have run into this one a few times myself.  I finally decided that it is easier to find the row in question and move it to the top of the spreadsheet.  Thanks for giving me a second way to accomplish this without moving the row

     

    Marvin

    Marvin Dillard
    Senior Consultant
    Claraview Inc

  • February 10, 2005 at 10:41 pm

    #541719

    I copped out and moved the row....!!!LOL

  • February 14, 2005 at 7:20 am

    #542041

    This is usually to do with the data in the first rows.  Assumptions are made about the data type in the first 100 rows, if something gets picked up later that doesn't fit (e.g. text after all previous rows had numbers) then it either errors or completely ignores it.

  • February 14, 2005 at 8:40 am

    #542086

    Hi Julie,

    Apparently the default is the first 8 rows only, this value being stored in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

    If you set the value to 0, the number of source rows scanned is 16384!

    There's some logic there somewhere... like I said, easier just to move the row...

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

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