Styjun

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

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.

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

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();