Solved

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

Posted on 2014-02-06
5
366 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

807 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