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

Hi :)
Feature requests are often a good plan.  The QA team and then the devs can
then work out how to move things forwards.
https://wiki.documentfoundation.org/QA/BugReport
CSV is notoriously non-standardised so differences between different
people's experiences is not uncommon.  It is an extremely efficient format
for transmission of data though so it's used quite widely and that is
unlikely to change.
Regards from
Tom :)








On 20 April 2014 11:43, Paul <paulsteyn1@afrihost.co.za> wrote:

> 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

-- 
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