none
Reading text values from Excel using OLE DB. RRS feed

  • Question

  • Hello all,

    I am trying to read an Excel file via OLE DB and have run into the problem that the values of cells containing text longer than 255 characters get truncated. Is it possible to read the full values, and how?

    Thanks in advance,
    Anton

    Friday, February 10, 2012 3:15 PM

Answers

  • check Data truncated to 255 characters with Excel ODBC driver

    If you need more help using the Jet OLEDB provider, you probably want to visit the Acccess Development forum under the Office development category.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP

    • Marked as answer by Anton Shepelev Monday, February 13, 2012 8:52 AM
    Friday, February 10, 2012 6:24 PM
  • This is a limitation of the provider.There are few hack, like Registry modification or insert a rows between 1-8 to XLSX before readng, but both got its own limitation.
    Say if you will modify the registry and set TypeGuessRows key to 0,As per this KB article, the number of source rows scanned would be 16384.So it will fails for 16385th row.

    For similar kind of requirement, i am using FileHelper Component. Or you can have a look at exceldatareadercomponent.Both are free and give good output in my production code.


    Lingaraj Mishra


    Sunday, February 12, 2012 6:56 AM

All replies

  • check Data truncated to 255 characters with Excel ODBC driver

    If you need more help using the Jet OLEDB provider, you probably want to visit the Acccess Development forum under the Office development category.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP

    • Marked as answer by Anton Shepelev Monday, February 13, 2012 8:52 AM
    Friday, February 10, 2012 6:24 PM
  • There is a Registry setting called TypeGuessRows that you will probably need to change to a value of 0 (default is 8). What this value does is tell the Excel driver how many rows to scan in order to determine the data type of each column. If there are no columns with more than 255 characters in the first row and the text is alphanumeric, then those columns will be a Text data type (which is limited to 255 characters).

    If there are any columns containing text that has a length greater than 255 characters (in the first eight rows) then those columns will be a Memo data type. If the first occurence of a Memo is beyond the first eight rows then the data type will be Text and any data beyond 255 characters will be truncated.

    There will be TypeGuessRows entries in the Registry for Jet and ACE (if installed) under the Excel sub key. Changing the value to 0 will cause the driver to scan all rows in order to accurately determine the data types of each column.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 10, 2012 7:03 PM
  • Thank you for your help, Sheng and Paul.

    As I see, the only sure way to prevent truncation of text cells is to tell Excel to scan the whole table before determining the columns' types.
    This indeed may cause serious performance problems, so I'd like to know if it is possible to force the Memo type for all text columns, even if the first eight rows contain less than 255 chars?

    Another way could be to insert a hidden first row with, say, 256 characters, but it's dirty and inconvenient...

    Anton
    Saturday, February 11, 2012 9:19 AM
  • This is a limitation of the provider.There are few hack, like Registry modification or insert a rows between 1-8 to XLSX before readng, but both got its own limitation.
    Say if you will modify the registry and set TypeGuessRows key to 0,As per this KB article, the number of source rows scanned would be 16384.So it will fails for 16385th row.

    For similar kind of requirement, i am using FileHelper Component. Or you can have a look at exceldatareadercomponent.Both are free and give good output in my production code.


    Lingaraj Mishra


    Sunday, February 12, 2012 6:56 AM