Retirement, History, and Google Spreadsheets


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!

Advertisements

About JoeDuck

Internet Travel Guy, Father of 2, small town Oregon life. BS Botany from UW Madison Wisconsin, MS Social Sciences from Southern Oregon. Top interests outside of my family's well being are: Internet Technology, Online Travel, Globalization, China, Table Tennis, Real Estate, The Singularity.
This entry was posted in Computer Tips, computers, Google, Google Docs, technology and tagged , , , , , . Bookmark the permalink.

4 Responses to Retirement, History, and Google Spreadsheets

  1. bytehead says:

    I guess I’m too much of a programmer, but I would have manipulated the CSV file to my liking first, before importing it. Basic works well if you have a 32-bit Windows installed, otherwise PowerShell makes it a breeze to handle CSV files, which I had to learn since I lost my beloved Basic by moving to 64-bit Windows 7. My wife would have written an Office macro.

  2. tourpro says:

    Joe, what you’re looking for is Concatenate function

    Here’s the formula joining two columns.

    =A1&B1

    or, if you need to insert a space between the two items

    =A1&,” “,&B1

    http://www.openofficetips.com/blog/archives/2005/02/text_manipulati.html

  3. Howdy! This is kind of off topic but I need some help from an established blog.

    Is it difficult to set up your own blog? I’m not very techincal but I can figure things out
    pretty quick. I’m thinking about creating my own but I’m not sure where to
    begin. Do you have any tips or suggestions? Many thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s