Hi
I have to build a C# program that makes CSV files and puts long numbers (as string in my program). The problem is, when I open this CSV file in Excel the numbers appear like this:
1234E+ or 1234560000000 (the end of the number is 0)
How I retain the formatting of the numbers? If I open the file as a text file, the numbers are formatted correctly.
Thanks in advance.
Format those long numbers as strings by putting a ' (apostrophe) in front or making a formula out of it: ="1234567890123"
You can't. Excel stores numbers with fifteen digits of precision. If you don't mind not having the ability to perform calculations on the numbers from within Excel, you can store them as Text, and all of the digits will display.
Choose Format->Cells and select a number format. Make sure the column is wide enough for the number.
When I generate data to imported into Excel, I do not generate a CSV file if I want control over how the data are displayed. Instead, I write out an Excel file where the properties of the cells are set appropriately. I do not know if there is a library out there that would do that for you in C# without requiring Excel to be installed on the machine generating the files, but it is something to look into.
As others have mentioned, you can force the data to be a string. The best way for that was ="1234567890123". The = makes the cell a formula, and the quotation marks make the enclosed value an Excel string literal. This will display all the digits, even beyond Excel's numeric precision limit, but the cell (generally) won't be able to be used directly in numeric calculations.
If you need the data to remain numeric, the best way is probably to create a native Excel file (.xls or .xlsx). Various approaches for that can be found in the solutions to this related Stack Overflow question.
If you don't mind having thousands separators, there is one other trick you can use, which is to make your C# program insert the thousands separators and surround the value in quotes: "1,234,567,890,123". Do not include a leading = (as that will force it to be a string). Note that in this case, the quotation marks are for protecting the commas in the CSV, not for specifying an Excel string literal.
Hi Gold,
My two cents:
I think it's important to realize there is a difference between "Data" and "Formatting". In this example you are kind of trying to store both in a data-only file. This will, as you can tell from other answers, change the nature of the data. (In other words cause it to be converted to a string. A CSV file is a data only file. You can do some tricks here and there to merge formatting in with data, but to my way of thinking this essentially corrupts the data by merging it with non-data values: ie: "Formatting".
If you really need to be able to store formatting information I suggest that, if you have time to develop it out, you switch to a file type capable of storing formatting info separately from the data. It sounds like this problem would be a good candidate for a XML Spreadsheet solution. In this way you can not only specify your data, but also it's type and any formatting you choose to use.