3rd Repost - Issue in Importing Excel spreadsheet into dataset

[Guest]

I am sorry but I am reporsting this because It was little urgent.
I need some one to help me out here to understand the problem.

I am trying to read an excel sheet and I am using the following connection
string-
"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel
8.0;HDR=YES;IMEX=1';Data Source=FILENAME"

While reading a first column (there are several columns) of one of the
spreadsheets I started having problem that for few rows the dataset I get
DBNULL values but the spreadsheet has the value for that row. I checked the
spreadsheet and I found that there is error notification on that cell "The
number in this cell is formatted as text or preceded by an apostrophe"; I
just converted those values to numbers and everything went well.

But I do I have anoother spreadsheet which has the same error descriptions
in it but when I import it without correcting it, everything goes fine
without any issues.

I am not able to understand why it's behaving in different way. I need some
one to help me here. Would appreciate.

Thanks,
Dinesh

 

[AMDRIT]

I don't have your answer, but is it possible to export the data to either
xml or csv and then import the data?

It would seem that on the first sheet, the cells data format is treated as
general while on the second sheet the data is formatted as number. I don't
know how the sheets were populated to begin with, is it something you can
control?

 

[Guest]

The only difference I see is the first sheet the first column does not start
with the cells which has error descriptions i mentioned in previous mail and
ends with few cells which has error descriptions.
The other worksheet starts with the cells which has error descriptions and
ends with the cells does not have error descriptions.

But I am finding an instances in the other sheet where everything goes fine
that the cells has error descriptions but the dataset gets the values of
those columns successfully.

I can attach the 2 spreadsheets in case anyone interested.

 

[RobinS]

AMDRIT is probably on the right track. Where did the data come from? Excel
is really picky. Once it calls a column text, it's text forever, no matter
how you format it. Edit one of the cells and see if there is an apostrophe
before the displayed number; this means that Excel treats it as text.

Try saving it as a CSV or TXT file and then open it in Excel as a new file
and see if that makes the columns numbers.

Good luck.
Robin S.
---------------------------

 

[Guest]

The input to the prgram is currently an excel file.
If I open the excel file and simply convert the cells having error
descriptions to number everything works fine. But we want to achieve this
without modifying the excel sheet because there is nothing wrong with the
data. It might be in text but there is no apostrophe in it.

Is there any way to do it or this as designed?

Thanks,
Dinesh

 

[RobinS]

without modifying the excel sheet because there is nothing wrong with the

data. It might be in text but there is no apostrophe in it.

Obviously there *is* something wrong with the data, or at least the data as
it is represented by Excel.

If you recreate the spreadsheet and copy and paste that column in with
Paste Special / Values, then use the same spreadsheet forever after, that
might work. That's why I asked where the spreadsheet came from. If it's
something that people edit and you just pick it up repeatedly, you should
be able to fix the spreadsheet and then use it. If it's something that gets
created, how does it get created? What creates it? Can that be fixed to
create it correctly?

Or you can try converting it to CSV (programmatically) and then importing
it.

It's obviously not going to work as it is. This text/number problem has
been an annoyance for as long as I've been using Excel.

Good luck.
Robin S.
----------------------------