Solved

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

Posted on 2014-02-06
5
350 Views
Last Modified: 2014-02-11
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
Comment
Question by:HyperBPP
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39839075
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
 
LVL 6

Author Comment

by:HyperBPP
ID: 39839092
@CodeCruiser Nothing linked for the microsoft support article :)
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39839710
0
 
LVL 6

Author Comment

by:HyperBPP
ID: 39848402
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
 
LVL 6

Author Comment

by:HyperBPP
ID: 39850782
Nevermind.  Found the key.  Forgot I was using a 64 bit system.  thanks!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now