Re: [libreoffice-users] Calc importing csv

At 14:41 09/07/2011 +0200, Luuk Noname wrote:
> I tried to import some CSV-data
> [...]
> The start of the downloaded data looks like:
>
> ,Date,Price,Volume
>
> ,28/12/1995,228.72766,7730000
> ,29/12/1995,220.73718,3557000
> ,02/01/1996,212.41376,10939000
> ,03/01/1996,216.409,15284000
> ,04/01/1996,219.0725,12418000
> ,05/01/1996,213.07964,5932000
> ,08/01/1996,214.41138,3694000
> ,09/01/1996,208.4185,7937000
> ,10/01/1996,209.08438,15280000
> ,11/01/1996,206.42088,13783000
> ,12/01/1996,205.755,8403000
> ,15/01/1996,211.08202,6972000
> ,16/01/1996,217.74074,7689000
> ,17/01/1996,217.07487,10208000
>
> When i paste this data into a new worksheet (and follow the import 
> text wizard) ...

The Text Import wizard allows you various options.  I'm guessing that 
you chose "Separated by" and "Comma".

> ... the first line looks like:
> 28/12/1995      228.72766       7730000
> which is expected

You may expect that, but I suggest you should not be happy with 
it!  Since the Price figure has a point instead of a comma, the value 
you have (in your locale) is a piece of text, not a number.

> But two lines of this export do NOT look like expected:
> 03/01/1996      216409  15284000
> 12/01/1996      205755  8403000
>
> The '.' in the second column is missing..... ;(
>
> Any ideas ??
>
> My locale setting are set to Dutch (decimal ',' )

My guess is that the Dutch locale setting allows the point as a 
thousands separator - as, for example, 123.456,789 .  Most of the 
values have four or five digits after the point, so cannot be 
interpreted as numbers; they appear instead as text.  But the few 
values with precisely three digits after the point, for example, 
216.409, can be read as numbers - here as two hundred and sixteen 
thousand, four hundred and nine; such values are interpreted as 
numbers and appear (in default format?) with no separator as the 
continuous digits of an integer.

I can see two alternative ways around your problem:

1.  Edit your .csv file first in a text editor (Writer will do) first 
replacing commas with some other separator, for example semi-colons, 
and then replacing points with commas.  (If using Writer, ensure you 
have "Regular expressions" unticked for the second part!)  Then 
import this revised version, using semi-colon as the separator.

2.  Set your locale to somewhere for which the point is the 
fractional separator (such as the UK of your source web site), import 
the material, and reset your locale to Dutch.

I trust this helps.

Brian Barker


--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted