2
Vote

Numeric strings are converted to numbers

description

When reading an .xlsx sheet with IExcelDataReader.AsDataSet strings that only contain numbers are converted to decimals
like 0100 becomes 100.0
If I use ' to force a string it works.

I would be greatful for a quick update.

comments

RamakantVerma wrote Aug 22, 2015 at 8:17 AM

I also have the same issue

RamakantVerma wrote Aug 22, 2015 at 8:21 AM

In Excel Sheet i Change the Cell Data Type to Number with 0 Decimal Place, Then also it mapping all integer to the Double Data type

sholle wrote Aug 24, 2015 at 3:10 PM

If the data is stored in the excel sheet as some type of numeric the reader is working correctly. Any formatting performed on the column to make it look like 0100 will not affect the data output. What I could suggest is selecting the entire sheet in excel and forcing it to text format.

I generally do all my data formatting in whatever target system I'm using the data in. There are various ways to right justify zero fill data in most languages. How are you using the data?

LandLubberNL wrote Aug 24, 2015 at 4:02 PM

The spreadsheet cell has a formula to extract a piece of a string and the resulting string is a General Ledger name. I can't rely on it to be a certain length, I have to retrieve the actual string that is the result in Excel. (Excel shows it correctly as a string). If it happens to be 00100, the reader returns a datatable with a field of type double and a value of 100.0 , it should be a field of type string, value "00100".

sholle wrote Sep 1, 2015 at 5:02 PM

How you format the cell data type does NOT effect the data stored in the cell. If the data is stored as a numeric type it will come out as a decimal type. As far as I know, the reader has no way to determine what formatting you have put on that cell. The formatting is only for viewing in excel, not modifying the stored data. The way you can tell what the underlying excel data looks like is to change the cell to text.

All you have to do is convert 100.0 into whatever format you want once you read it out. Whatever language you are using when you read the data has information on the web on how to turn a numeric value into a formatted left justified 0 filled string. Formatting the excel sheet to look different will not help you at all.

LandLubberNL wrote Sep 2, 2015 at 8:58 AM

The data is stored in another cell as '00100 and then collected onto this sheet by means of a lookup. The contents of the cell is 000100 (notice the missing quote). The reader provides me with 100.0, so I can't tell whether it was 100 or 0100 or 00100, which in this application is significant.

Other than that the reader works great.