Converting Scientific notation values into numeric one while reading csv file which is serialized automatically

Question

I have a csv file which I have to import into data base. When reading the file by stream reader, some values are converted into scientific notation like "5.00E+11". I have to restore this into original values. This is being done by a job, so I can not format the cell to "text" or "special" manually. When that cell is formatted as "text" or "special" it is working fine.

I need result as follows :

    "5.00E+11" should be converted into "500000000000"
    "8.12E+12" should be converted into "8122280000000"

My code for reading the file is as below:

    /// <summary>
    /// This is used to read the csv file
    /// </summary>
    using (StreamReader reader = new StreamReader(comepleteFilePath))
    {
       values = reader.ReadToEnd()
                      .Split(new string[] 
                      { Environment.NewLine },
                       StringSplitOptions.RemoveEmptyEntries
                      ).ToList();
     }         

    var _tempNuber = Convert.ToString(splits[13].Trim());

like below

Possible duplicate of [Convert numbers with exponential notation from string to double or decimal](https://stackoverflow.com/questions/7877855/convert-numbers-with-exponential-notation-from-string-to-double-or-decimal) — Chetan, Mar 26 '19 at 05:45
Not duplicate exactly, it is issue of reading values which is serialized by excel automatically and same field have different type of data as well. — Satish Chandra Maurya, Mar 26 '19 at 06:21

TheGeneral · Answer 1 · 2019-03-26T06:01:19.093

Firstly, you should use a dedicated CSV parser like CsvHelper, this will potentially save you a lot of time.

Secondly, if you want to parse a number use something like decimal, or double Parse with one of the options. In this case NumberStyles.Float

decimal.Parse(number, NumberStyles.Float);

NumberStyles.Float

Indicates that the AllowLeadingWhite, AllowTrailingWhite, AllowLeadingSign, AllowDecimalPoint, and AllowExponent styles are used. This is a composite number style.

AllowExponent

Indicates that the numeric string can be in exponential notation. The AllowExponent flag allows the parsed string to contain an exponent that begins with the "E" or "e" character and that is followed by an optional positive or negative sign and an integer. In other words, it successfully parses strings in the form nnnExx, nnnE+xx, and nnnE-xx. It does not allow a decimal separator or sign in the significand or mantissa; to allow these elements in the string to be parsed, use the AllowDecimalPoint and AllowLeadingSign flags, or use a composite style that includes these individual flags.

Update

you can either use regex to try and determine if the field does contain an actual number. Like \d.\d+E[+-]\d+ or even better just use decminal.TryParse with the appropriate options.

That field is not only numeric; some data are text and some are numeric and some are combination of both text and numeric so I can not hard code any formatter directly. Issue are only with large numeric values. — Satish Chandra Maurya, Mar 26 '19 at 05:56
I have to check if it is a numeric value and in scientific notation as well then have to use : decimal.Parse(number, NumberStyles.Float); — Satish Chandra Maurya, Mar 26 '19 at 06:10

Prasad Telkikar · Answer 2 · 2019-03-26T06:07:24.683

1

You can parse that string to decimal

    string s = "5.00E+11";
    decimal d = decimal.Parse(s, NumberStyles.Float);

Output

500000000000

If you want to apply parsing only one exponential element, then you can check given string is exponential or not

double d = 0;
if(str.Contains("E") && double.TryParse(str, out d))
{
   //Your conversion
}

POC : .net fiddle

edited Mar 26 '19 at 06:07

answered Mar 26 '19 at 05:44

Prasad Telkikar

15,207
5
21
44

I can not check for "E" since a text value might contain that character also. So I have to check for "+" char instead of that ie. str.Contains("+") – Satish Chandra Maurya Mar 26 '19 at 06:42
I have additional condition check of `TryParse` that is important to check that passed string is of double type and Exp number always contains `+ and E/e` – Prasad Telkikar Mar 26 '19 at 06:45

score 0 · Answer 3 · answered Mar 26 '19 at 06:33

0

Check if that field is numeric and in scientific notation as well then format as :

var _tempNuber =  splits[13].Trim().Contains('+') ? decimal.Parse(splits[13].Trim(), NumberStyles.Float).ToString() 
                                                  : splits[13].Trim();

answered Mar 26 '19 at 06:33

Satish Chandra Maurya

43
1
8

What if `splits[13]` contains something like `1+2` – Prasad Telkikar Mar 26 '19 at 06:38
No chance since no special characters are allowed there – Satish Chandra Maurya Mar 26 '19 at 07:10
Your last comment is contradictory as you mentioned exponential number in question (it contains `+` in string) and in last statement you said no special characters... I don;t khow what values present in `splits[13]`, but solution suggested by you in an answer is not sustainable (I believe) if it works for you then I am happy about your answer – Prasad Telkikar Mar 26 '19 at 07:15
@Prasad let me clear; In database table from where the data is exported into the csv file, special characters are not allowed into that column from front end of the application. Now in Cell[13] of csv file the value is "500000000000" but when I read that field by above code, it is being read as "5.00E+11" due to automatic serialization of excel automatically. – Satish Chandra Maurya Mar 28 '19 at 09:27

Converting Scientific notation values into numeric one while reading csv file which is serialized automatically

3 Answers3