none
truncate at 255 characters with xlsx files. RRS feed

  • Question

  • Hi, using a vb.net program to read out information from an xlsx file by using the Microsoft.ACE.OLEDB.12.0 provider.
    Problem is that all long text data is truncated after 255 chars. With the old jet engine (for xls files) this was solved by using the typeguessrow modification in the registry. With the new provider for 2007 and 2010 files, this is not solved.

    Anybody who solved this?

    I've set my HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows to 0.

        Dim cs1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\b3.xlsx;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
        Dim oledbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(cs1)
        oledbConn.Open()
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
        Dim oleda As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        oleda.SelectCommand = cmd
        Dim ds As DataSet = New DataSet
        oleda.Fill(ds, "test")
        MsgBox(ds.Tables(0).Rows(13).Item(0).ToString)

    • Moved by Bessie Zhao Tuesday, November 23, 2010 2:43 AM For more support (From:Excel for Developers)
    Tuesday, November 23, 2010 12:44 AM

All replies

  • Hello Kurt,

    Thanks for posting. Since this issue is more than a ADO.NET issue, I will move this thread to ADO.NET managed providers for better support. Have a nice day.

    Best regards,
    Bessie Zhao - MSFT
    MSDN Subscriber Support in Forum
    If you have any feedback of our support, please contact msdnmg@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, November 23, 2010 2:46 AM
  • Hello,

    Thanks for your thread.

    Is there any special format for your columns in Excel. I tested with the same code snippet, however, it works fine (I use office 2010 beta version). Would you please generate a small project to reproduce and send it to v-roaluoATmicrosoftDOTcom? Thanks a lot in advance!

    By the way, for performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. "When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column."

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Tuesday, November 23, 2010 6:04 AM
  • I think this is a limitation of the provider. You could try to modify registry settings for it as described in next link, but I am not sure if it works with ACE provider

    http://msdn.microsoft.com/en-us/library/ms141683.aspx

    If you need only to read data, you can also try .NET managed provider (xlReader) from my website (http://www.xporttools.net/xlReader.aspx). It has no limitation for the strings.

     


    Val Mazur (MVP)

    http://www.xporttools.net

    Tuesday, November 23, 2010 11:49 AM
    Moderator
  • The TypeGuessRows setting is supported by ACE. Note the version numbers in the key may change depending upon the version of Office installed.

    For 32-bit Windows (32-bit Office):
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

    For 64-bit Windows (32-bit Office):
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, November 23, 2010 7:38 PM
  • Paul, i have a win7 64bit with office 32 bit.

    So i have this info in my registry;

    Key Name:          HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
    Class Name:        <NO CLASS>
    Last Write Time:   11/23/2010 - 12:44 AM
    Value 0
      Name:            DisabledExtensions
      Type:            REG_SZ
      Data:            !xls

    Value 1
      Name:            ImportMixedTypes
      Type:            REG_SZ
      Data:            Text

    Value 2
      Name:            FirstRowHasNames
      Type:            REG_BINARY
      Data:           
    00000000   01                                                 .

    Value 3
      Name:            AppendBlankRows
      Type:            REG_DWORD
      Data:            0x1

    Value 4
      Name:            TypeGuessRows
      Type:            REG_DWORD
      Data:            0

    Value 5
      Name:            win32
      Type:            REG_SZ
      Data:            C:\PROGRA~2\COMMON~1\MICROS~1\OFFICE14\ACEEXCL.DLL

     

     

    TypeGuessRows is set to 0, but the data is truncated.

    Is my provider string correct as I'm using Provider=Microsoft.ACE.OLEDB.12.0 against Excel 14? Any relationship here?

    Thursday, November 25, 2010 2:10 PM
  • Hello,

    Please check you email account and let me know if it does not help.

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Friday, November 26, 2010 8:55 AM
  • Paul, i have a win7 64bit with office 32 bit.

    So i have this info in my registry;

    Key Name:          HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
    Class Name:        <NO CLASS>
    Last Write Time:   11/23/2010 - 12:44 AM

    Value 4
      Name:            TypeGuessRows
      Type:            REG_DWORD
      Data:            0

    TypeGuessRows is set to 0, but the data is truncated.

    Is my provider string correct as I'm using Provider=Microsoft.ACE.OLEDB.12.0 against Excel 14? Any relationship here?


    I have the same configuration (except Office 2007) and changing TypeGuessRows to zero did work for me. Do you have a version 12.0 registry entry as well? Perhaps that is what you need to change:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, November 26, 2010 1:55 PM
  • Thanks Paul.

    No ACE under 12.0 in the registry. I created the ACE settings under 12.0 with no success.

    But i have more details now.

    with this code

     

     

    Dim cs1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\b3.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"

     

     

    Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(cs1)

     

     

    Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", conn)

     

     

    Dim oleda As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

    oleda.SelectCommand = cmd

     

     

    Dim ds As DataSet = New DataSet

    oleda.Fill(ds,

     

    "test")

    MsgBox(ds.Tables(0).Rows(13).Item(0).ToString)

     

     

    and an excel file that has a long (+255) value in A15 does not work. it returns nothing. But, when i place the value in B15 then it reads out the information. So it look it has a problem with the first column only.

     

    Friday, November 26, 2010 3:59 PM
  • Hello,

    Have you tried to reset the registry key?

    I can reproduce your issue on my side with your code snippet and the excel file. However, if I reset the value of the registry entry, it works well. How about things on your side if you reset the value?

    -Thanks a lot,
    Roahn Luo MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Tuesday, December 7, 2010 9:05 AM
  • Thanks. No, not working here.

    I've set the typeguessrow to 100 now.

    Can you maybe export your HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0 node for verification?

    Or do you have another suggestion?

    Monday, December 13, 2010 4:25 PM
  • Below is what I have in my Registry for the Excel settings:

    Windows Registry Editor Version 5.00
    
    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel]
    "DisabledExtensions"="!xls"
    "ImportMixedTypes"="Text"
    "FirstRowHasNames"=hex:01
    "AppendBlankRows"=dword:00000001
    "TypeGuessRows"=dword:00000000
    "win32"="C:\\PROGRA~2\\COMMON~1\\MICROS~1\\OFFICE12\\ACEEXCL.DLL"
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, December 13, 2010 5:14 PM
  • Hi,

    I guess maybe you can to refer KB281517 http://support.microsoft.com/kb/281517.

    Kevin

    Wednesday, November 16, 2011 8:09 AM
  • Hi,

    i have implemented this code:

          private string GetMemoField(string FieldName, string IdentityFieldName, string IdentityFieldValue, OleDbConnection conn)
            {
                string ret = "";

                OleDbCommand cmd1 = new OleDbCommand("SELECT " + FieldName + " FROM B2CItems WHERE " + IdentityFieldName + "=" + IdentityFieldValue, conn);

                var reader = cmd1.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);  // Create the DataReader that will get the memo field one buffer at a time

                if (reader.Read())
                {
                    long numberOfChars = reader.GetChars(/*Field pos*/ 0, 0, null, 0, 0);   // Total number of memo field's chars

                    if (numberOfChars > 0)
                    {
                        int bufferSize = 1024;
                        char[] totalBuffer = new char[64*bufferSize];    // Array to hold all memo field content

                        long dataIndex = 0;

                        do
                        {

                            char[] buffer = new char[bufferSize];   // Buffer to hold single read
                            long numberOfCharsReaded = reader.GetChars(0, dataIndex, buffer, 0, bufferSize);

                            if (numberOfCharsReaded == 0)
                            {
                                ret = new string(totalBuffer);
                                break;
                            }

                            Array.Copy(buffer, 0, totalBuffer, dataIndex, numberOfCharsReaded);     // Add temporary buffer to main buffer
                            dataIndex += numberOfCharsReaded;

                        } while (true);
                    }
                }

                return ret;
            }

    Giuliano Piergentili

    MCSD

    Tuesday, August 18, 2015 11:13 AM