• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

vb.net office 2010 queries getting cut off at 255 characters

I'm running queries in vb.net to pull data from excel 2010.  Sometimes the character limit for a single a field is cutoff at 255 characters.  Is there a way around this?
0
HyperBPP
Asked:
HyperBPP
  • 3
  • 2
1 Solution
 
CodeCruiserCommented:
The OLEDB provider for excel will attempt to automatically determine the DataTypes based off of the first 8 rows of data, this can be set with the HDR=Yes/No property in the connection string. Additionally, there are multiple types that it can apply to text columns. The memo type holds over 255 characters, so if none of the first 8 rows have that then it will incorrectly set the data type.

The way to change this is by changing a registry setting called TypeGuessRows, as described here: Microsoft Support

NOTE: The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. So if you have a very large file make sure the biggest rows are first.
0
 
HyperBPPAuthor Commented:
@CodeCruiser Nothing linked for the microsoft support article :)
0
 
CodeCruiserCommented:
0
 
HyperBPPAuthor Commented:
The workaround does not provide a key that exists in my registry.  Also my connection string is not through JET but ACE (not sure this makes a difference)

.Open("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""Excel 12.0 Macro;HDR=" & hasHeaderRow & ";IMEX=1""")

Thanks!
0
 
HyperBPPAuthor Commented:
Nevermind.  Found the key.  Forgot I was using a 64 bit system.  thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now