Click here to Skip to main content
15,885,435 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Is there a way to convert the number back to it's original format without having to manually change it within csv? the format within the csv is general and i know by changing it to number it turns back to original but is there a way to fo it through c# code instead?

The scientific notation:
1.00004E+11

The original number:
100004438998

What I have tried:

The original number comes from a xls file which i converted to csv, I have tried changing the format from number to text and using a different type of csv converion file format but still the outcome is the same.
Posted
Updated 20-Jul-22 22:19pm
Comments
Graeme_Grant 12-Jul-22 4:49am    
What does the raw data look like? Is it from excel?
Allysha April 12-Jul-22 4:55am    
Yes originally from excel, format is already set as number and looks fine on excel and once converted into csv the number changes into scientific notation. I have to convert it into csv for some work purposes. I need that number specifically as it is an important part for determining some things for my work
Graeme_Grant 12-Jul-22 5:07am    
Then the cell is "general" format. I did a test in excel before responding with cell set as "number" with decimal places set to "0". Saved as ".csv" and the output was:
100004438998,Test
Allysha April 12-Jul-22 5:13am    
I tried out the same thing, still didn't work either. I am not sure where the problem lies
Graeme_Grant 12-Jul-22 6:57am    
Your cells are not correctly formatted in Excel. Fix that and it will work.

Quote:
How do I convert the scientific notation to it's original number?
The scientific notation:
1.00004E+11

The original number:
100004438998

In this case, you can't.
Because your initial conversion to scientific notation have lost a part of the initial number.
When I convert an excel file to csv, I never get a scientific notation unless I ask for it. There is nothing we can do for you until you tell exactly how you generate the csv file (including code used).
 
Share this answer
 
Comments
Allysha April 12-Jul-22 5:07am    
I just convert it directly using save as to CSV no code used, I only start using code after the finished converted csv file
What I'd suggest is abandoning CSV format: it's very old, and as such it's pretty basic. A better idea might be to Output Excel data as JSON - Office Scripts | Microsoft Docs[^] as you have a greater range of outputs than just "number" and "String"

But ... the problem is that when you export Excel data to any file type, it uses the number format you selected for the cell or column.
So if your cells have no specific format set, they are "General" - which is floating point which will get truncated and converted to scientific notation.
If it's "Number" format with zero decimal places, then it will be displayed and saved as an "integer" value with the full number of digits.

It's not a CSV problem, it's a spreadsheet problem!
 
Share this answer
 
Well...
If cell with scientific notation holds only 1.00004E+11, then you've got a problem. Why? Because 1.00004E+11 means 100004000000.
If this cell holds 100004438998 value, but uses scientific notation, you're able to save its original value to csv file.

You haven't show us the code which is responsible for conversion. I guess that you are treating all as a text (for example by using cell.Value.ToString()). That's why you're getting wrong values!

In case, you still want to convert values stored in csv file, take a look at example and its output:
C#
string line = @"1.00004E+11;100004438998;";

System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.GetCultureInfo("en-US");
long[] values = line.Split(new string[]{";"}, StringSplitOptions.RemoveEmptyEntries)
	.Select(x=> long.Parse(x, System.Globalization.NumberStyles.AllowExponent | System.Globalization.NumberStyles.AllowDecimalPoint , ci))
	.ToArray();


Result:
100004000000 
100004438998


Conclusion: save original values (rather then text) to csv file and you'll get proper values!
 
Share this answer
 
Comments
CHill60 21-Jul-22 5:49am    
It seems the OP is using File, Save As to save the Excel file into CSV format. They are then using C# to process the CSV :facepalm:
Maciej Los 21-Jul-22 6:22am    
You're right, Caroline. I've checked it out that [File -> Save As] method using rule "WYSIWYG" to save csv data. :(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900