[PLUG] OO.o Calc Question

[John Jason Jordan]

On Mon, 21 Mar 2011 11:56:55 -0700 (PDT)
Rich Shepard <rshepard at appl-ecosys.com> dijo:

>On Mon, 21 Mar 2011, Fred James wrote:
>> Obviously there is the "type it in answer" ... but maybe that's not
>> what your are looking for?

>   Not when there are dozens of such empty cells on the page.

>I have several dozens of client spreadsheets (all Excel, of course) with
>about 30 years' worth of compliance monitoring data. Different
>operators over the years and absolutely no standards for data
>organization or page structure. Or anything else.
>
>To import these data into postgres for statistical analyses with R I need
>to code missing values (blank cells) as NA (not available). I could do
>this after exporting the page as a .csv file, but wondered if there
>was a way to have the search field in "Find & Replace" blank the way
>we can have the replace field blank to remove cruft.

Disclaimer: I am a master with Writer, but have launched Calc less than
a hundred times in my entire life. Take what I say with a whole shaker
of salt.

Calc can be macroed along the same lines as Writer. I love to use
macros in Writer because I can just record the keystrokes and clicks -
lots easier for those of us who are code challenged. I've even manually
modified the code on occasion, e.g., where I needed to do successive
search and replaces in a specific order. In such a case I just recorded
the first search and replace, then copied and pasted the search part of
the code for as many things as I needed to search for, modifying each
one for the specific string. 

Of course, you can write your macros completely manually if you like. I
am thinking of a macro that would search for an empty cell and replace
it with "NA." In the process you could also set the cell properties to,
e.g., text instead of numeric. If you run the macro on a file with lots
of empty cells it might take a while to run, but that's frequently just
right for a trip to the coffeepot. It would certainly be faster than
doing it one at a time manually.

You might also see if you can Select All, and then deselect the ones
containing data, followed by a paste of "NA." I think the tricky part
would be to figure out what you need to say in the macro for "if
empty." I know in Writer ^$ returns an empty paragraph, but I don't
know how to specify an empty cell in a spreadsheet.

I believe that, instead of using the default OOo Basic, you can write
macros in python, and possibly other languages.