Re: [libreoffice-users] SOLVED: cannot convert text to number in spreadsheet from csv file

I just ran some tests on my system, and it seems like all I need to do
is choose a "Language" that uses the correct decimal specifier, and the
numbers are imported as numbers.

Then I experimented a bit with the "Column type" for the number column.
If I use a comma as decimal separator in the file, and choose a
"Language" that uses commas, then the numbers are imported as numbers,
both when I leave the "Column type" as "Standard" and when I choose "US
English". If, however, I use a full stop as decimal separator in the
file, and choose a "Language" that uses commas during import, then
leaving the "Column type" as "Standard" means the numbers are imported
as text, but changing the "Column type" to "US English" means the
numbers are now imported correctly as numbers.

One of my first thoughts for solving this problem was simply to choose
the column type on import, and it seems like this will solve the
problem, although just choosing a correct "Language" should have as
well, and apparently it didn't. But I am still surprised to see so
little choice in the column types. The "Standard", "Text" and "Hide"
options make perfect sense, but I'm not sure why a "US English" is
needed. Maybe because it's the most common, so you might need the
overall document to be one "Language", and specific columns to be
another? But then why only the choice of "US English", and not any
language? Date types also make sense, although some testing shows that
you don't have any choice for the separator. And why isn't there any
number types?

Maybe a feature request needs to be added to expand the column types?
Maybe with options to specify the date format more completely, to
specify a number format, and to choose any language? Sounds right, but
will it be easy enough to do? And if it is, maybe even more power,
maybe the option to use functions on input columns, so that you could
transform data in more ways? That's probably getting beyond the scope
of imports, though, and is best done in the document itself after
import. But at least the ability to import numbers and dates with a
little more flexibility?

Just my thoughts, as I see nobody else has mentioned the column types
yet.

Paul



On Sun, 20 Apr 2014 10:02:23 +0200
"M. Fioretti" <mfioretti@nexaima.net> wrote:

> On Sat, Apr 19, 2014 20:05:39 PM +0200, Marco Fioretti wrote:
> > Greetings,
> >
> > I have the problem below with LO 3.5.7.2 on a Fedora box.
> >
> > I have a shell script that generates and save to a text file called
> > synthesis.csv many lines like this:
> >
> > |||2013-02-15|Payment A|-100.25|008|fae|
> > |||2013-03-15|Payment B|-50.25|008|fae|
>
> I had a flash (some would call it a desperate shoot in the dark...),
> and modified the script to enclose in double quotes the numbers above:
>
> > |||2013-02-15|Payment A|"-100.25"|008|fae|
> > |||2013-03-15|Payment B|"-50.25"|008|fae|
>
> now everything works as expected. When I open the CSV file with calc,
> I can just write in another cell, WITHOUT any manual cell
> reformatting,
>
> =sum(F1:F2)
>
> and I get -150.50 displayed in that cell. Yay!
>
> Now, if someone could explain **why** this works, or even better: why
> this was necessary on my system, but not for other people who wrote to
> me privately to help debugging the problem, it would be even better...
>
>    Marco
> --
>
> M. Fioretti http://mfioretti.com
> http://stop.zona-m.net
>
> Your own civil rights and the quality of your life heavily depend on
> how software is used *around* you


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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