I’m working hard on our Retirement website and upcoming retirement blog project called “Retire USA” and wound up having a heck of a time with some data conversions, so I want to outline that process here in case others have the same troubles.

Process ONE – copying records from a Google Docs Spreadsheet to an Open Office “ODS” format spreadsheet:   This sure seemed non-trivial unless I missed something or had data glitches buried in the sheets.   I could NOT complete direct cut and pastes from Google docs to my Open Office “ODS” spreadsheet so….

What DID WORK was this:

1.  Save Google spreadsheet in CSV format within Google Docs

2.  Import that CSV sheet to Open Office and save in ODS format.

3.  Cut and paste between the two Open Office spreadsheets.   IMPORTANT:  When pasting, select ONLY the upper left cell as if you are going to paste everything into that single cell.

 Process TWO – Merge two cells into a new single cell containing contents of BOTH original cells.     In my case I wanted a new combined field name.

Update – review the “DATA/Text to Columns” and the related “Concatenate” functions within Open Office.

Again, I’m not an expert so there may be easy ways to do it, but this did not seem to work directly in Google Docs spreadsheet.    Merge cells is an option, but it only preserved ONE of the cells.    However in Open Office you can celect “Merge Cells” and it should immediately prompt you to include contents of BOTH cells.   Unfortunately I had to merge about 8000 cells and since there seemed to be no bulk process I created a keystroke macro and did them (pretty quickly) manually because I was not clever enough to get a macro that would run this process by itself.

FYI note that you may want to keep your old fields (cells) as well as have the new combined field.   In that case simply make copies of the columns so you have extra ones to combine.

The reason I needed this was to create a CityCategory field name that will work well with our Retirement website.   One painful alternative to this little merge routine would have been to type the extra word into five thousand records!