Excel column and VB.NET

  • Thread starter Thread starter Prasun
  • Start date Start date
P

Prasun

Hello:

I an trying to extract info from an excel file and populate datatables in a
dataset. One of the columns (a modifier column) I use to filter the info
obtained from the excel file contains both alpha and numeric entries. If i
create my own test file with the same values and using the same modifiers in
this specific column it works fine. Unfortunately if I use a file that was
made by another user, my SELECT statement cannot read / detect the rows that
have the numeric modifiers. Is there a way to solve this. I have tried
changing the cell formating from General to text to number.....But it
doesn't work

Here is an Example Table/Spreadsheet

Pcode RCode Modify
2222 1024
2222 1024 26
2222 1024 TC
2222 4343
2222 4343 TC
2222 4343 26
2222 5656
2222 3535 26
2222 3535







In this example table. The SELECT statement would not read the rows with 26
as a modifier, but has no problem with TC.
How can I remedy this situation. I know it has something to do with the way
the cells are formatted.

Here are my SELECT statments


Code:

"SELECT * FROM [Work] WHERE Modify = 'TC'"
"SELECT * FROM [Work] WHERE Modify = '26'"



Thank You
Prasun
 
Prasun

This is a real stab in the dark but try no single quotes around the numeric
or a combination of double, single quotes. (If that makes sense) or maybe
double, double quotes. I suspect some combination will work

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I figured it out.

In the connection string's Extended Properties I needed to add the following
IMEX = 1; this sets the values as intermixed, I believe.

Here is an example:

ExcelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& InputFileName & _

";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""



Thanks

Prasun
 
Back
Top