Jump to content
Sign in to follow this  
BobTheMystic

OleDb in VB.NET

Recommended Posts

So I have this bizzare problem dealing with VB.NET, Microsoft Excel and one MAJOR headache, any help or even hinting in the right direction would ge GREATLY appreciated.

 

I'm writing a program right now that is supposed to take an excel file (.xls), read it into a vb.net program and display the contents of the file in a custom graphing API written by the company I work for. Part of reading in this excel file is parsing a worksheet that contains configuration info for the test; maximum range, minimum range, y axis labels, x axis labels, plot title, graph colors etc. In order to read in this data I'm using the Jet 4.0 provider with Microsoft OleDb to access the excel file and read in the data. Unfortunately, Jet has this horrible limitation where it interprets the cells based on the majority data type of the first 8 rows of data of each column, casting everything of that type to something readable and returning "DBNull" for the rest. By hacking into the registry, this isn't a difficult problem to fix as you can fool the program into thinking everything in the excel file is text. This works by looking at the registry key KHEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel and reading the ImportMixedTypes field and the TypeGuessRows field. The TypeGuessRows field is the number of rows the provider looks at before determining the data type. The ImportMixedTypes field has one of two values, "Text" and "Majority Type." By setting the connection string for OleDb to include the extended properties IMEX=1, it forces the provider to honor the ImportMixedTypes field an interpret the results based on that key, rather than defaulting to "Majority Type."

 

Now the problem comes when you realize that you have fields in ye olde excel file that contain a large number of characters. The "text" type in OleDb is limited to a maximum field size of 255. The y grid label field of the configuration sheet can have thousands of characters in it. The only workaround I've found thus far involves writing in a string of garbage over 255 characters long into the first row of that column. This way, with the "Majority Type" option set in the registry, it interprets the field as a "memo" rather than as a text. Memo fields have a maximum size of around 32,000 characters, well within the limits I need with this program. This wouldn't be such a hard problem fix, just write in some garbage manually and then load the file, a little cumbersome, but workable. However, the test suite we're running that generates these files generates dozens of files every day. It is simply not practical to alter them by hand, given the volume of test data that we have. Thus comes the root of the problem: in order to correctly parse the y axis labels, I need to have a very long string of characters in the column header. However, I don't know how to write in a string longer than 255 characters programmatically. If I could do that it would be a simple matter to set the field to a load of garbage, grab the colum data in "memo" format and then erase the garbage.

 

Here's a bit of code to give you an idea of what I'm trying to do:

 

	pOleDbConMyConnection.Open()
		Me.RawData = New DataSet

		Dim pobjCmd As OleDbCommand = New OleDbCommand("UPDATE [" & Me.ConfigSheetName & "$B2:B2] SET F1='BIG HONKING STRING'")
		pobjCmd.Connection = pOleDbConMyConnection
		pobjCmd.ExecuteNonQuery()
		pobjCmd.Connection.Close()

		pstrConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" _
		  & "data source=" & Me.FileInfo.FullName _
		  & ";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO"""
		pOleDbConMyConnection = Nothing
		pOleDbConMyConnection = New OleDbConnection(pstrConnectionString)
		pOleDbDataAdaptDataAdapter = New OleDbDataAdapter(pstrSelectString, pOleDbConMyConnection)

		pOleDbConMyConnection.Open()
		pOleDbDataAdaptDataAdapter.Fill(pdatasetData)
		pOleDbConMyConnection.Close()

 

Thank you in advance if you've made it this far, I know this is a hefty post and a very obscure problem, ANY help, as previously mentioned will earn you a cookie. :thumbs-up:

Share this post


Link to post
Share on other sites

You can open an Excel spreadsheet from within VB .Net and read the contents of the cells directly. This would eliminate the need to use the OLEDB connection. That approach might not be as fas as what you're doing, but it allows you more control. Not knowing how the data are store w/in the spreadsheet makes it a little difficult to come up with an example, but I'm assuming all the information you need resides in a cell on the worksheet, right? You could just parse the worksheet from within your .Net application and fill your datatable that way.

Edited by Nemo

Share this post


Link to post
Share on other sites

Yes, but without knowing the exact binary layout of the Excel format, you need a provider (Like OleDb or Excel) to go through the binary data and interpret it in a meaningful way. I think Micro$oft would get a little touchy if their programmers started handing out proprietary file format info like that <_< Unfortunately, I can't use the Excel API because it would crash the test suite (which happens to be using the Excel API the whole time it's running).

 

Needless to say, an irritating problem. Thanks for the suggestion though.

Share this post


Link to post
Share on other sites
Yes, but without knowing the exact binary layout of the Excel format, you need a provider (Like OleDb or Excel) to go through the binary data and interpret it in a meaningful way. I think Micro$oft would get a little touchy if their programmers started handing out proprietary file format info like that <_< Unfortunately, I can't use the Excel API because it would crash the test suite (which happens to be using the Excel API the whole time it's running).

 

Needless to say, an irritating problem. Thanks for the suggestion though.

 

 

you can connect excel to vb in oledb and also in vba

 

just check this url

 

http://vb.net-informations.com/excel-2007/...7_tutorials.htm

 

http://vb.net-informations.com/excel-2007/...excel_oledb.htm

 

ian

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×