truncate at 255 characters with xlsx files.

Article
2010-11-23

Question

_{Tuesday, November 23, 2010 12:44 AM}

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)

All replies (13)

_{Tuesday, November 23, 2010 2:46 AM}

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 [email protected].

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Tuesday, November 23, 2010 6:04 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 [email protected]

_{Tuesday, November 23, 2010 11:49 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 7:38 PM}

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)

_{Thursday, November 25, 2010 2:10 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?

_{Friday, November 26, 2010 8:55 AM}

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 [email protected]

_{Friday, November 26, 2010 1:55 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 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 3:59 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.

_{Tuesday, December 7, 2010 9:05 AM}

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 [email protected]
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!

_{Monday, December 13, 2010 4:25 PM}

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 5:14 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)

_{Wednesday, November 16, 2011 8:09 AM}

Hi,

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

Kevin

_{Tuesday, August 18, 2015 11:13 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

Share via

truncate at 255 characters with xlsx files.

Question

All replies (13)

Additional resources