Major data loss: Calc formatting numbers

[dotancohen]

A new OOo user is using Calc to store information on volunteers in an
organization. Israeli ID numbers are 9 digits in length, such as this:
317907977

However, Calc has reformated them to this:
317908000

This is a major dataloss for the organization. The person in charge is
pissed, and both myself and OOo are to blame. OOo is already coming
off their machines (14 desktops, and I think a few laptops as well)
and I am tasked with recovering the missing data. Is this possible?

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Gordon Burgess-Parker-5]

Dotan Cohen wrote:

> A new OOo user is using Calc to store information on volunteers in an
> organization. Israeli ID numbers are 9 digits in length, such as this:
> 317907977
>
> However, Calc has reformated them to this:
> 317908000
>
> This is a major dataloss for the organization. The person in charge is
> pissed, and both myself and OOo are to blame. OOo is already coming
> off their machines (14 desktops, and I think a few laptops as well)
> and I am tasked with recovering the missing data. Is this possible?
>
>  

Well you certainly seem to have something very ODD going on - Calc 3.01
here doesn't do that, it just displays the original 9-digit number...

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Graham P Davis-3]

Gordon Burgess-Parker wrote:

> Dotan Cohen wrote:
>> A new OOo user is using Calc to store information on volunteers in an
>> organization. Israeli ID numbers are 9 digits in length, such as this:
>> 317907977
>>
>> However, Calc has reformated them to this:
>> 317908000
>>
>> This is a major dataloss for the organization. The person in charge is
>> pissed, and both myself and OOo are to blame. OOo is already coming
>> off their machines (14 desktops, and I think a few laptops as well)
>> and I am tasked with recovering the missing data. Is this possible?
>>
>>  
> Well you certainly seem to have something very ODD going on - Calc 3.01
> here doesn't do that, it just displays the original 9-digit number...

No problem here either (version 3.1.0.98.1 on OpenSUSE 11.2-m2). I tried
formatting as both number and text and there was no difference.

How long after entering the data did reformatting occur?


--
Graham P Davis, Bracknell, Berks., UK.  E-mail: newsman not newsboy
"I wear the cheese. It does not wear me."


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

> Well you certainly seem to have something very ODD going on - Calc 3.01
> here doesn't do that, it just displays the original 9-digit number...
>

The spreadsheet in question was actually created in OOo 2.x (2.3 or
2.4 I think) as it was the very first machine used to test OOo. They
are now opening in 3.0 and 3.1. I would send to you the document, but
it contains private information.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

> How long after entering the data did reformatting occur?
>

A version or three ago. The document has always been a calc document,
never converted from or to excel format. From the timeframe, it may
have been over a year since the document was made. The rounding off
was just noticed now, I do not know for how long it was present. As
this document was only used on the one machine, it was not emailed
around so there are no old copies for me to check. Nor, like the rest
of the system, are there backups! I am desperately trying to find an
older version of the document, but it looks like there is none.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

Going through the document, I see that not all of the entires have
been rounded. I am trying to figure out if there is a certain date
from which all entires have been rounded, it does not appear so.

Furthermore, in some places where a four-digit number was used, it
shows as 1,234 instead of 1234. I do not know if this other formating
error is related.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Gordon Burgess-Parker-5]

Dotan Cohen wrote:
> Going through the document, I see that not all of the entires have
> been rounded. I am trying to figure out if there is a certain date
> from which all entires have been rounded, it does not appear so.
>
> Furthermore, in some places where a four-digit number was used, it
> shows as 1,234 instead of 1234. I do not know if this other formating
> error is related.
>
>  
Sounds to me as though either someone's been fiddling with it or there's
been some major corruption somewhere along the line - cell formats in
spreadsheets don't /normally/ change themselves....

Makes good propaganda for backups...... :-)

[dotancohen]

> Sounds to me as though either someone's been fiddling with it or there's
> been some major corruption somewhere along the line - cell formats in
> spreadsheets don't /normally/ change themselves....
>

Of course, the user is convinced that OOo is now the devil. And I am
his demon...


> Makes good propaganda for backups...... :-)
>

It's not the first propaganda for backups to be rejected. I don't
understand that decision either.


--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Per-13]

Dotan Cohen skrev:

Hi there, the demon of OO.o....  ;-)

interesting stuff happening here..


You are writing... A new OO.o user, have this user used Excel or another
spreadsheet-like app??

My first thought to this is that this user have - in some way -
fiddeling around with the copy and paste-commands in the sheet and with
this action filled the cells with other formats, but that´s maybe a
shoot right out in the blue...

How is the sheet constructed?

for instans... ???

Name         ID-number         Date         Other stuff      Another
other stuff.....  


????

Good luck with the hard work...

> A new OOo user is using Calc to store information on volunteers in an
> organization. Israeli ID numbers are 9 digits in length, such as this:
> 317907977
>
> However, Calc has reformated them to this:
> 317908000
>
> This is a major dataloss for the organization. The person in charge is
> pissed, and both myself and OOo are to blame. OOo is already coming
> off their machines (14 desktops, and I think a few laptops as well)
> and I am tasked with recovering the missing data. Is this possible?
>
>  

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

> You are writing... A new OO.o user, have this user used Excel or another
> spreadsheet-like app??
>

The only two spreadsheet apps on that machine are Calc and Excel 2003,
and as the document is in ods format, I doubt that Excel had ever
opened it.


> My first thought to this is that this user have - in some way - fiddeling
> around with the copy and paste-commands in the sheet and with this action
> filled the cells with other formats, but that´s maybe a shoot right out in
> the blue...
>

More likely, the text may have been copy/pasted out of an email. Are
you suggesting that the data that was pasted was already rounded? I
will look into that possibility.


> How is the sheet constructed?
>
> for instans... ???
>
> Name         ID-number         Date         Other stuff      Another other
> stuff.....
>

Pretty much.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Alan Lord (News)]

On 01/07/09 17:46, Dotan Cohen wrote:
<snip />
> More likely, the text may have been copy/pasted out of an email. Are
> you suggesting that the data that was pasted was already rounded? I
> will look into that possibility.


Have you checked the "Precision as shown" setting on OOo?

"Precision as shown
Specifies whether to make calculations using the rounded values
displayed in the sheet. Charts will be shown with the displayed values.
If the Precision as shown option is not marked, the displayed numbers
are rounded, but they are calculated internally using the non-rounded
number."

Tools-Options-Open Office.org Calc-Calculate...

Just a thought.

Alan


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Graham P Davis-3]

Dotan Cohen wrote:

> More likely, the text may have been copy/pasted out of an email. Are
> you suggesting that the data that was pasted was already rounded? I
> will look into that possibility.

I had something odd happen to some imported data yesterday. It was formatted
as numbers but I saw it was left-justified with leading blanks in the cells
instead of the usual right-justified. When I clicked on a cell, the value on
the input line of the toolbar appeared with a leading single quote. I fixed
this by transferring the data to another sheet by multiplying each cell by
1.0.

Another frequent problem I encounter with sheets where data is
added/inserted is that although a check on the formatting for a row/column
says one thing, that may not be true for all cells - some may have had other
formats imported with the data. The cure I apply for that is to change the
format for the row/column to something different, date format say, then back
to the required format.

What I'm saying is that I'm agreeing that the original importing could be at
the heart of the problem. I assume you've looked at the data as it shows up
in the input line?

Sorry in advance if the foregoing appears like I'm teaching my grandmother
to suck eggs.


--
Graham P Davis, Bracknell, Berks., UK.  E-mail: newsman not newsboy
"I wear the cheese. It does not wear me."


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

> Have you checked the "Precision as shown" setting on OOo?
>

The option was not marked, but marking it made not difference.
Furthermore, selecting the text then copying it gives the rounded
number.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Johnny Rosenberg]

If you go to an empty cell and make sure it is formatted properly, and
then enter =A1 (if A1 is one of the cells that is corrupted) in it,
will the corrupted value or the correct value be displayed?

Johnny Rosenberg


2009/7/2 Dotan Cohen <dotancohen@...>:

>> Have you checked the "Precision as shown" setting on OOo?
>>
>
> The option was not marked, but marking it made not difference.
> Furthermore, selecting the text then copying it gives the rounded
> number.
>
> --
> Dotan Cohen
>
> http://what-is-what.com
> http://gibberish.co.il
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

2009/7/2 Graham P Davis <newsboy@...>:

> Dotan Cohen wrote:
>
>> More likely, the text may have been copy/pasted out of an email. Are
>> you suggesting that the data that was pasted was already rounded? I
>> will look into that possibility.
>
> I had something odd happen to some imported data yesterday. It was formatted
> as numbers but I saw it was left-justified with leading blanks in the cells
> instead of the usual right-justified. When I clicked on a cell, the value on
> the input line of the toolbar appeared with a leading single quote. I fixed
> this by transferring the data to another sheet by multiplying each cell by
> 1.0.
>
> Another frequent problem I encounter with sheets where data is
> added/inserted is that although a check on the formatting for a row/column
> says one thing, that may not be true for all cells - some may have had other
> formats imported with the data. The cure I apply for that is to change the
> format for the row/column to something different, date format say, then back
> to the required format.
>
> What I'm saying is that I'm agreeing that the original importing could be at
> the heart of the problem. I assume you've looked at the data as it shows up
> in the input line?
>
> Sorry in advance if the foregoing appears like I'm teaching my grandmother
> to suck eggs.
>
>

Thanks. I have the original email where one of the rounded numbers was
copy/pasted from and the number is not rounded in the email.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

2009/7/2 Johnny Rosenberg <gurus.knugum@...>:
> If you go to an empty cell and make sure it is formatted properly, and
> then enter =A1 (if A1 is one of the cells that is corrupted) in it,
> will the corrupted value or the correct value be displayed?
>
> Johnny Rosenberg
>

I see the rounded (corrupted) number.


--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Twayne-2]

"Dotan Cohen" <dotancohen@...> wrote in message
news:880dece00907010235v3af9d508oea0987d1f5319ea4@...

> A new OOo user is using Calc to store information on volunteers in an
> organization. Israeli ID numbers are 9 digits in length, such as this:
> 317907977
>
> However, Calc has reformated them to this:
> 317908000
>
> This is a major dataloss for the organization. The person in charge is
> pissed, and both myself and OOo are to blame. OOo is already coming
> off their machines (14 desktops, and I think a few laptops as well)
> and I am tasked with recovering the missing data. Is this possible?

From the Peanut Gallery:

Have you checked to see that a user-defined cell format hasn't been
created?  Where you set the format to text, number, etc., take a look at
the user-defined and see if something has been created there.

In Excel, there is something called an auto-open macro.  Any chance
there is such a thing in OO.o?

Long shots I know, and I can shoot them down easily myself, but ... I'd
still check them if it were me.  I can't find anything else related to
rounding of numbers.

HTH,

Twayne`





---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

> Have you checked to see that a user-defined cell format hasn't been
> created?  Where you set the format to text, number, etc., take a look at
> the user-defined and see if something has been created there.
>

I do not see anything that looks like it is simply rounding off the
data. It seems that the data was rounded off at an earlier time, and
only the rounded number is now stored.


--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[NoOp-4]

On 07/02/2009 02:16 PM, Dotan Cohen wrote:

>> Have you checked to see that a user-defined cell format hasn't been
>> created?  Where you set the format to text, number, etc., take a look at
>> the user-defined and see if something has been created there.
>>
>
> I do not see anything that looks like it is simply rounding off the
> data. It seems that the data was rounded off at an earlier time, and
> only the rounded number is now stored.
>
>

So it seems that the issue is resolved then?




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[dotancohen]

 > So it seems that the issue is resolved then?
>

The organization has suffered some dataloss and I've been working like
hours to go through emails and paper files and calling people trying
to get the data back. When I'm done I will then have to convert about
30 spreadsheets and I haven't even looked at how many word processor
documents, and convert then to MSO 2003's native format, then check
again for formatting and dataloss. OOo will not be used in the end,
this whole OOo switch has been a headache for all involved and while
I'm not sorry that it happened as I have learned a lot about what a
real organization needs from an office suite, quite a few people are
sorry that they tried this free thing and listened to Dotan. I've lost
quite a bit of respect from some people but gained a bit from some
less important people. I have a list of bugs to file and features to
request as a result, I will get to that when I finish with this
project.

I cannot reproduce the rounding incident, but from my familiarity with
the project and the person operating the machine, I do not suspect
user error. I suspect that moving from OOo 2.2 (as it turns out, this
whole thing started off as an experiment over two years ago!) to 2.3
to 2.4 to 3.0 to 3.1 had something to do with the loss. I simply don't
have the time to perform the upgrades and test so thoroughly, but if
anyone wnats to try I can provide information.

There were other dealbreakers as well, such as the American dates
issue when editing a date in Calc. That was and continued to be a huge
issue, and has caused dataloss in the past. Why is the least
significant figure (days) in the American date format in the middle?!?
Do American didgital watches use the HH:SS:MM format by any chance?

And what of backups? Some files, but not all due to space
considerations, will simply be put in a BackMeUp directory that will
be copied to DVD whenever someone feels like it.

--
Dotan Cohen

http://what-is-what.com
http://gibberish.co.il

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...