Problem with using a column from an excel file in filling datatable

  • 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
 
Hi,

What you could do is to use IMEX=1 option for the extended properties in
your connection string to the Excel file. In this case Jet provider will
treat all the values in all the columns as strings. It could help
 
Back
Top