plino Thank you for the description of cvs. I had not thought that far in to the data format. I contacted the smartphone company about that particular field and they responded that it is supposed to be a text field. LibreOffice interpreted all the data in the cvs correctly. The problem was an IO (idiot operator) error. Thanks again. John McAtee
From: plino <[email protected]> To: [email protected] Cc: Sent: Sunday, November 7, 2010 5:37:04 PM Subject: [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] <[email protected]> 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 <[email protected]> > To: [email protected] > 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 [email protected] 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 [email protected] 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 [email protected] 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 [email protected] 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
