On Mon, 21 Mar 2011 11:56:55 -0700 (PDT) Rich Shepard <[email protected]> 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. _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
