none
Problem reading excel cell value. Value got truncated RRS feed

  • Question

  • Hi All,

    Can any one help me, how to read the excel cell value without truncating?. I am using OledbConnection string like @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ excelFilePath +";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"). i am not able to get the excel field cell value fully. I have changed HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\ TypeGuessRows

    Thanks

    Kesav


    Monday, May 30, 2011 10:09 AM

Answers

All replies

  • Hi Kesavaraj Mani,

    Thank you for posting.

    Frist of all, we want to know how many characters have one cell with. The OLEDB provider for excel will attempt to automatically determine the data types based off of the first rows of data. I suggest you can try to set your regirsty on HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows. Please check this KB for more detailed information. http://support.microsoft.com/kb/281517

    Hope this helps.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 31, 2011 9:26 AM
  • You should be using the Jet 4.0 entry instead of the the Access Connectivity Engine entry.

    Also, keep in mind that when running under 64-bit Windows the location for Jet would be:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, June 1, 2011 4:30 PM
  • Hi Larcolais,

    Thanks for kind response. My excel sheet cell mauy contains more than 300 characters. So in this what should i do? if we use OLEDB providers, it throws error. Can u provide me some other way to do this?

    Note: I have changed the HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows decimal value is 16. but stiil getting  error. Please help me out.

    Thanks,

    Kesav

    Wednesday, June 8, 2011 10:21 AM
  • Hi Kesav,

    It looks that you used Office 2007 on your machine. I suggest you can install Office 2010 and try it again. The 64-bit version enables you to work with much larger sets of data. Please check here for more information. http://msdn.microsoft.com/en-us/library/ee691831.aspx In addition, your question is related with Office Excel one. I'm not expert on Excel. I suggest you can ask your question into Excel for Developer forum. Please check: http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    If you have any concern, please feel free to let me know.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, June 9, 2011 8:22 AM
  • You should change the value of TypeGuessRows to 0 so that all rows are scanned. It might result in slower queries but will ensure that the the data type of a column containing more that 255 characters will be Memo.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, June 9, 2011 12:16 PM
  • Setting HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to a value of 0 worked perfectly!!!

    The Registry strikes again!

    Thanks

    Tuesday, August 16, 2011 5:14 PM