Apache OpenOffice (AOO) Bugzilla – Issue 77215
CSV Import/Export is lossy due to default numerical rounding
Last modified: 2007-07-06 13:21:28 UTC
I regularly use the CSV import and export features in Calc to edit simple parameter input files for numerical computation models. By default Calc displays 2 decimal point precision in all cells. This seems to be arbitrarily based on the notion that any floating point number is a currency. It is irritating to have to change the displayed precision manually (especially when this typically results in resized columns) More importantly, upon export (file, save as) Calc by default outputs the displayed 2dp precision instead of the actual precision. I eventually figured out I could change this behavior by editing filter settings, but these setting reset upon restart. Thus, I always face the risk of losing precision if i don't check my export settings carefully. I do not have the same problems in Microsoft Excel. My proposals are: 1. default behavior maintains full precision for non-native file types that do not support display formatting: a) on display after csv import unless the user selects otherwise. b) on output after csv export (again unless the user selects otherwise). 2. persistent text import settings be stored (output formatting, delimiters etc) allowing one click opening under preferred settings
dlm, please attach sample file.
Created attachment 45759 [details] Sample CSV file
I've attached the sample. If you open this with default settings you will notice two annoying things: massive column widths, rounding by default. Next save the data using default settings then reload it and you will see that decimal precision is lost (check out columns O through Y before and after)
Confirming with 2.2 on WinXP - numbers are rounded. Big column width seems to be expected - you have that much text in column.
If there were 100,000 characters before the first comma would the column width be 100,000 characters wide? by default, truncating the display of data to a standard width and running into the next column if the cells is empty is much more usable. If I want a quick overview of what's in a file it's painful to have to shrink the col width down, adjust precision etc...
just to clarify: text is a second class citizen in spreadsheets. ideally column width should be maximum of standard width or minimum width to display "widest" number in the column.
dlm, please describe how competing spreadsheet sets column width for long strings.
I'm not sure how other spreadsheet apps handle column widths for imported data, but excel uses a default width that is large enough to display a double in scientific notation with 2 dp of precision (or alternatively a date in DD-MMM- YY/MMM-DD-YY format) at standard font size. Text that is too large to fit within a column will spill into the next if it is empty, or is otherwise truncated. Numbers that are too large (such as numbers in currency format) typically display as "######", although perhaps it would be better to extend the width to the minimum width required to display dates and numeric types in full. cheers.
basically double to Issue 3687. @mmp please comment on Issue 3687 and consider this one for this case also. Frank *** This issue has been marked as a duplicate of 3687 ***
closed double