Solved

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

Posted on 2014-02-06
5
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

749 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