I opened a CSV file. It came through correctly for columns but some of the numbers (both -ve and +ve) came through as text. How can I prevent this and/or convert after opening?
Hello,
Question 1: How can I prevent this?
Your numbers in your CSV file are not recognized as numbers during import. And this is most likely due to the fact that your locale uses another default decimal delimiter that used in the CSV. For testing purpose just create a simple CSV file containing
user@system:~> cat 1.csv
1.00;1,00
Now open the file and you’ll see that one of the "numbers* is recognized as a number (right aligned) and the second as text (left aligned). Which one depends on your locale (for my german local the comma “,” is the decimal delimiter and thus 1.00 is recognized as text). Obviously there are 2 solutions
- Change your locale (either globally or during import / opening a CSV file - carefully watch the dialog which offers on category Import to select the Language, which allows to adapt to the delimiter used in the CSV file.
- Change the CSV file to use the delimiter matching your Calc default locale delimiter
Question 2: How to convert ?
The solution derives from above. So change the delimiter using Find & Replace. To do so:
-
Select the column containing the data with wrong (with respect to your locale) decimal delimiter
-
Edit -> Find & Replaceand set
Find field: incorrect delimiter (e.g.,)
Replace field: correct delimiter (e.g.)
Opther options: Current selection only -
Click OK
Note: You defintely can’t convert just changing the format. Don’t even try, it won’t work, since formatting a cell doesn’t change the data type of existing data.Formatting just changes the appearance of a data type.
Hope that helps.