[libreoffice-users] Re: sum function in libreoffice calc doesn't seem to work

John

CSV does not format values as anything it is a simple text file where
values are separated by commas (CSV = Comma Separated Values)
The problem is that depending on your locale settings the comma may be
a comma or a semi-collon and the decimal value may be a comma or a
point.

And this is where LibreOffice (inherited from OpenOffice) fails.

IMO the importer should assume everything is values (after all you are
importing to a spreadsheet not a text editor). If AFTER trying (or
asking) with both comma and point ONLY then they should be assumed as
text.

On 11/7/10, John McAtee [via Document Foundation Mail Archive]
<ml-node+1859211-310443519-251143@n3.nabble.com> wrote:
> Bill
>
> As is the usual I rushed to help before I read all of the posts.  I am glad
> that you have found the solution to your question.  I did not.  My csv file
> brings the field in as text because the smartphone exports the field as
> text.  I did some testing and found the following:
>
> 1 In the Text Import dialog box I set the Seperated By to Comma and the Text
> Delimiter to Apostrophy (not sure why this is the way it has to be but it
> is)
> 2 Select the Other Options, Quoted Fields a Text (This will put " around the
> data in the sheet that is text)
> 3 Once the sheet is open I do a Find & Replace with Regular Expressions
> turned on to Find "*" and Replace with *.  This replaces all of the " with
> *.
> 4 Then Find * and Replace with (nothing) to get rid of the * and the numbers
> in the field are now numbers instead of text.
>
> This solution is not as clean as yours but it got the job done.
>
> John McAtee
>
>
>
> From: bill woodruff <reikibill2002@aol.com>
> To: users@libreoffice.org
> Cc:
> Sent: Saturday, November 6, 2010 9:07:04 PM
> Subject: [libreoffice-users] Re: sum function in libreoffice calc doesn't
> seem to work
>
> plino <pedlino <at> gmail.com> writes:
>
> > Bill,  do the cells in the range contain only natural numbers?
> >
> > I suspect that you are having a problem with the decimal separator.
> >
> > Your cells are probably identified as text because of that (are the values
> > aligned to the left?)
> >
> > Paste this in cell D2 to check
> > =VALUE(C2)
> Hello, Plino:
>
> This is a second reply. After I wrote you earlier, I tried re-importing
> the .csv file. This time, I clicked on the "Standard" heading on top
> of the column in question, hoping there would be a format for "Currency."
> There was none, but after I clicked "Hidden" then clicked back to
> "Standard," the file imported with the currencies properly recognized
> and aligned to the right.
> Now the =SUM(c2:c46) function works just fine. I am sorry for wasting
> so many people's time, but I could not figure this out by myself. I am
> very grateful to everyone involved for their suggestions.
> One final question: Shouldn't the Import function recognize currencies
> automatically rather than making the user jump through these hoops?
>
> Sincerely/Bill Woodruff
>
>
>
>
> --
> E-mail to users+help@libreoffice.org for instructions on how to unsubscribe
> List archives are available at http://www.libreoffice.org/lists/users/
> All messages you send to this list will be publicly archived and cannot be
> deleted
>
>
>
> --
> E-mail to users+help@libreoffice.org for instructions on how to unsubscribe
> List archives are available at http://www.libreoffice.org/lists/users/
> All messages you send to this list will be publicly archived and cannot be
> deleted
>
>
>
> ______________________________________
> View message @
> http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p1859211.html
>
> To unsubscribe from sum function in libreoffice calc doesn't seem to work,
> click
> http://nabble.documentfoundation.org/template/TplServlet.jtp?tpl=unsubscribe_by_code&node=1839208&code=cGVkbGlub0BnbWFpbC5jb218MTgzOTIwOHwtMTcxMDU2NTk1NA==

-- 
View this message in context: 
http://nabble.documentfoundation.org/sum-function-in-libreoffice-calc-doesn-t-seem-to-work-tp1839208p1859774.html
Sent from the Users mailing list archive at Nabble.com.

--
E-mail to users+help@libreoffice.org for instructions on how to unsubscribe
List archives are available at http://www.libreoffice.org/lists/users/
All messages you send to this list will be publicly archived and cannot be deleted