CSV files and having numbers and strings in there...
That seems to be it
People say I should use , (COMMA separated....) - however the separator here is ; (semicolon), the comma is not a separator, so it will read , as a part of a field
Therefore - you are right - using , as a decimal separator here will work as . seems to be for dates (unless the values are out of range then they are floats just to make it more fun)
Now I only need to store data as strings - currently by adding ' in front of them
On Tuesday, April 22, 2014 10:17:51 PM UTC+3, joeu2004 wrote:
<sonnichjensen wrote:
I am saving CSV files from a PHP app, but I face 2 problems:
1. stock numbers are sometimes just numbers, but I'd like to
keep them as strings
2. prices are e.g. 5.2 which Excel translates as a date.
Say:
Item;Name;Price;Amount;Total
123;Test;5.2;1;5.2
124;Test2;1.2;2;2.4
Total;;;;=sum(e2:e3)
Look at your Regional and Language Options control panel. Is the "decimal
separator" really a period (.), not a comma (,)?
I assume your date separator is a period (.).
If the decimal separator is a comma (,), Excel always interprets 5.2 as a
date as long the component parts (5 and 2) are valid day and month numbers.
The only work-around I know is to input the column as Text (see below), then
enter formulas in a parallel column to interpret the text as numbers. Lots
of work!
If the decimal separator is a period (.), my version of Excel interprets 5.2
as a number, despite the ambiguity with the date syntax.
As for interpreting 123 and 124 as text, I think the best thing is not to
open the CSV file directly in Excel, but instead to import it as a text
file. In Excel 2007 and later, click on Data, Get External Data, From Text.
Click on Next repeatedly, changing the separator as needed. When you get
Step 3, select the first column, and click on Text for the column data
format.
PS: I am surprised that Excel interprets =sum(e2:e3) as a formula, not
simple text. Caveat: the reference to e2:e3 works only if the entire data
is imported into the correct range, starting with A1 in the upper-left in
this case.
|