Importing Spreadsheets

Introduction

In this document we will walk you through the process of importing data from a spreadsheet application into Graph Earth.  Although we are using Microsoft Office 2007 in our example, this process should work for most spreadsheet applications. In previous examples, we have created Google Earth documents (KML) that display data by State.  To illustrate the flexibility of Graph Earth, we will use this opportunity to generate a 3D map of cities, however, you could just as easily graph by country, state, county, city or zip code. Data for this project was retrieved from Wikipedia and includes the population of all cities in the United States with more than 100,000 persons.

Exporting Excel Data

1.  Download the population_by_city.xlsx file and open in Microsoft Excel 2007
2.  Click on the office orb icon in the upper left-had corner of Excel
3.  Select Save As -> Other Formats

 

4.  Set 'Save as type' to CSV (Comma delimited)

5.  Click 'Save'

6.  A CSV file is a simple way to store tabular data.  Support for the format is included in virtually every spreadsheet and database application.  However, CSV file types do not support multiple worksheets the way an Excel Workbook does.  Here, Excel is simply informing you of this.  Click 'Ok'.

7.  Simply click 'Yes'

8.  Once the file is saved to disk, launch Graph Earth.

9.  Click File -> Import -> CSV File top open the Import CSV File dialog.

10.  Check the box labeled 'First row contains column names'.

11.  Click 'Import'.

12.  A new tab should appear at the bottom of Graph Earth.  Click this tab to view the data you imported from the excel workbook.

Congratulations, you have successfully imported your data.  Next we will generate a simple Prism graph using this new data and display it in Google Earth.

 

Creating a Google Earth KML / KMZ Graph from Microsoft Excel Data

Now that we have successfully imported our data, we can begin building a visualization.  For this example, we will create a prism graph.

1.  Click Graph -> Prism to open the Create Prism Graph dialog.

Graph Earth currently supports creating maps of several regions, including country, state, county, city and zip code.  Geometry for each of these regions is included as part of the application, without the need to supply KML, databases or shapefiles (.shp).   However, in order for Graph Earth to find the appropriate region, we need to give it some information.  In the case of countries, states and zip codes, this is usually a single simple name.  A state could be identified with a name like 'Alabama' or state code such as 'AL'.  This is simple because in the collection of states, there is only one Alabama.  In the case of cities and counties, two identifiers are required.  City and county names are not unique.  Take for instance the city of Austin, Texas.  If we simply told Graph Earth we wanted to graph data for Austin, the application wouldn't know if we meant Austin, Texas or Austin, Indiana.  Thus, we must provide the second identifier of state by selecting an 'Additional Field' which contains the state name, USPS code or FIPS code of the city.

2.  Select the appropriate columns for your graph.  Make sure to set the 'Additional Field' combo box to 'State'.

3.  Make any other desired changes to map elements including color ramp selection, number of classes, legend, title, logo, or embedded charts.

4.  When you are done, click 'Create Graph'.

5.  At times, your spreadsheet may contain anomalies or misspellings.  In this case we have some additional characters in our city names which are the result of footnoting in our source, Wikipedia.  When Graph Earth gets confused, it will ask you for clarification with the 'Invalid Region Detected' dialog.  Simply select the appropriate region from the dialog and click 'Replace'.  This will fix the misspelling in your spreadsheet and make sure the dialog does not appear again in the future.  When all of these anomalies have been resolved, your Google Earth document will be rendered in KML.  When rendering is complete, Google Earth will automatically launch so you can view your new map.

When complete, your Google Earth (KML) map should look similar to the image below.

Terms of Service

Please click on the link below to read our terms of service.  These terms apply any time you access this site or domain.  Please read them carefully...

Read

Privacy Policy

Privacy matters and your privacy is important to us!  Please click the link below to review our Privacy Policy.  This is a site-wide policy that applies to GraphEarth.Com

Read

GE Software License

GraphEarth is licensed pursuant to the GraphEarth End User License Agreement (EULA).  Please click the link below to learn more...

Read