[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-02-06
5
Medium Priority
?
391 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

650 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