Problems using csv file in Calc

3 replies [Last post]
nickb
Offline
Last seen: 3 years 24 weeks ago
Title:
Joined: 27 Jan 2011
Posts: 2

Hi,

I'm using LO 3.3 Final on Windows (Vista) and have come across a couple of problems with using/manipulating data loaded as a csv file.

1. I have a number of columns that contain numeric data but when I either select a few cells or the entire column Calc does not display the Sum in the Status area. It says Sum = 0!

If I type in some numbers into unused cells it does display the Sum.

2. In order to try and solve #1 I tried to reformat the cells. When I set a numeric format LO doesn't change the format and also it appends an apostrophe/single quote to the front of the data in each of the cells!

Both these functions work in OO3.2. When I import the file into LO the data is displayed differently - OO converts cells that contain numeric data into numbers - even when they are contained in " " while LO appears to be treating them as strings.


My main use of calc is manipulatiing data in csv files that has either been extracted or is to be processed by a legacy system (Cobol running on Linux). These problems render it pretty useless for that at the moment and I've had to go back to using OO for the time being.

abcuser
Offline
Last seen: 2 years 20 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Hi, if I understand
Formatting is only for changing how data is displayed, but does not change the data in cell itself (single quote character that is added during import process). The main problem is that your source data are INCORRECTLY formatted as text (double quotes) instead of numbers (without double quotes), so there are actually not in the CSV format! The problem can be solved to delete single quotes from cells after importing into Calc. This can be done manually or with extension "Convert Text To Number".
1. Download extension: like: http://extensions.services.openoffice.org/en/project/CT2N
2. Tools | Extension manager | Add button.
3. Close Calc and reopen it.
4. There should appear new toolbar icon if not then make sure View | Toolbars | CT2N is checked.
5. Mark cells you would like to convert into numbers and click on icon from toolbar to convert to numbers.
Regards
abcuser
Offline
Last seen: 2 years 20 weeks ago
Title: ★★★★★
Joined: 29 Jan 2011
Posts: 146
Ahhh, there is even simpler

Ahhh, there is even simpler way of doing it, you don't need extension. Double click a csv file in Windows Explorer of File | Open in Calc and select csv file. "Text Import" window opens. In "Other options" section there is "Quoted fields as text", uncheck this settings and Calc will convert all double quoted text to numbers when importing (see attachment). This must be done only once because setting is remembered.

AttachmentSize
uncheck_quoted_field.png 18.53 KB
nickb
Offline
Last seen: 3 years 24 weeks ago
Title:
Joined: 27 Jan 2011
Posts: 2
Thanks

Thanks,

That worked a treat - I should of spotted that myself but sometimes you can't see what is staring you in the face! :)

I just summed a column containing approx 7400 rows with various monetary amounts and it came up with this ...... 220064.890000001 but hey that's close enough!! :)

Nick


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.