Use Open Refine to clean up data

The techniques described in this article may be out of date.

Open Refine is a desktop app that helps you clean, standardize and transform a data set from its raw state to more polished state suitable for the next step in your analysis, visualization or collaboration.

You can use Open Refine to split text files into columns, combine multiple columns into one, and even auto-fill cell values when they've been left empty for a print version of a table. And much more!

These screencasts offer an excellent overview of the power of Open Refine. Install the software here and check out the help center for how-tos and even recipes for cleaning and transforming your data.

Import data into Fusion Tables from Refine

When you've cleaned up your data in Open Refine:

  1. Click Export in Open Refine.
  2. Select "Comma-separated value." This downloads a .csv file you can easily bring into Google Fusion Tables.
  3. Go to Google Drive in your browser.
  4. Click NEW > More > Google Fusion Tables.
  5. Click Choose file on the Import new table dialog box and click through the prompts to import your table.

Export data to Refine from Fusion Tables

There are three ways to bring data from Fusion Tables to Refine:

Option 1: Export a .csv file to import to Open Refine.

  1. Use File > Export, which downloads a .csv file from Fusion Tables.
  2. Open Open Refine.
  3. Use File import when you make a new project.

Option 2: Export directly from Fusion Tables online.

  1. Make sure the table is exportable (check File > About) and is either Unlisted or Public (use Share Settings as needed).
  2. Copy the URL for your Fusion Table from the address bar of your browser.  
  3. Open Open Refine application.
  4. Start a new project in Refine by pasting the table's URL.

Option 3: Export a query result directly from Fusion Tables online.

  1. Make sure the table is exportable (check File > About) and is either Unlisted or Public (use Share Settings as needed).
  2. Get the table ID for your Fusion Table.  Use File > About.
  3. Create a URL using the Fusion Tables API that finds the data you'd like to copy into Refine. To experiment with it, paste the URL into your browser address bar. This example will import only the named columns Product and Inventory:
    https://www.googleapis.com/fusiontables/v2/query?sql=SELECT Product, Inventory FROM 13Ib30dg5xqDFtcNs9xx6MFgJW00qYZwTxJFTgw
    
    
  4. Open Open Refine application.
  5. Start a new project in Refine by pasting in the URL.