Part 1—Gather and Format Delimited Earthquake Data

Step 1 –
Select the Output File Type and Set the Search Parameters

The United States Geological Survey (USGS) monitors earthquake activity in the United States as well as all over the world. Earthquake records can be retrieved from several online databases.

You will obtain the records of significant historical worldwide earthquakes with a magnitude greater than 7.0 by performing a global search for earthquakes at the USGS site. To begin, click this link to go to the USGS websiteEarthquake Search.


  1. Once on the new web page, click the Global (Worldwide) Search link.
  2. On the next page, select Spreadsheet Format (comma-delimited) as the Output File Type.
  3. Choose the Significant Worldwide Earthquakes (2150 B.C. - 1994 A.D.) Database. This database contains the records of significant earthquakes. This includes ones with the following criteria: have caused either 1 million or more dollars in damage; have resulted in 10 or more deaths; have a magnitude of 7.5 on the Richter scale; or have an intensity of X or higher on the Modified Mercalli Intensity scale.
  4. Specify a Magnitude range from 7.0 to 10.0.
  5. Click Submit Search to display the data.

Optional: To find out more about the database, visit the metadata at the NOAA websiteNatural Hazards Data, Images and Education page.

Step 2 –
Copy the Data from the USGS Web Page

To copy the data, choose Edit > Select All, then Edit > Copy all the earthquake data displayed from the search.

Step 3 –
Paste the Data into a Spreadsheet and Save it

  1. Launch Microsoft Excel, or another spreadsheet application, and then paste the copied data into a new file. Depending on your software,all of the data may paste into Column A. If this happens, make sure Column A is highlighted then choose Data > Text to Columns... and use the Convert Text to Columns Wizard to format the comma-delimited data into multiple columns.
  2. In the spreadsheet file, tab across the data to make sure the data are spread across the columns. The number of columns should match the number of fields in the header, so that each field fills a single column.
    The header is the first row of the data file and often contains brief field names such as "M" for magnitude. Each of the remaining rows contains an individual earthquake record.
  3. When you are finished with the import and conversion to columns, choose File > Save, then select the Excel workbook format, and save the data into a file called Sig_eq.xls.
  4. Create a folder called Earthquake Data to store the Sig_eq.xls file and the others you create.

Step 4 –
Clean Up the Imported Data

  1. Delete the title "NEIC: Earthquake Search Results" and the extra blank rows at the top of the page as well as the menus and graphics at the bottom, leaving only the header and the data.
  2. Depending upon browser settings, graphic elements such as the USGS logo may have also been copied when the earthquake data was copied from the web page. Right-click or ctrl-click to select any graphics. Then, either choose Edit > Cut or use the Delete key to remove the graphic.
  3. Edit the data as needed. AEJEE is very sensitive to empty cells, and special characters, and will not import datasets that contain even one of these types of cells. Careful editing at this stage will save you frustration at the next stage.
    • Use Find/Replace to replace empty cells with the number -999.
    • Delete cells of data that have special characters such as %, $, or @.
    • Inspect the file for extra or misaligned data, adjust the data.

Step 5 –
Format the Data to Create a "GIS-ready" File

Choose File > Save As... Sig_eq.csv to save the data as a Comma Separated Values (CSV) file. Depending upon your version and platform, Microsoft Excel may prompt you twice about saving the file in this format. Click yes to any additional messages asking if you are sure you want to save the file in this format. Save this file into the Earthquake Data folder you created in Step 3.
Be sure to choose the Comma Separated Values (.csv) option. DO NOT save the file as a Text (.txt) file or AEJEE will not be able to read it.

Step 6 –
Gather the Records of "Big" Earthquakes from 1994 to Present

Use the USGS Earthquake Search to find "Big" earthquakes that have occurred since 1994.

  1. Click the Global (Worldwide) Search link.
  2. Obtain the records by selecting Spreadsheet Format (comma-delimited) as the Output File Type.
  3. Choose the USGS/NEIC (PDE) 1973 - Present Data Base.
  4. Specify a Date range beginning 01/01/1995 and ending with today's date.
  5. Limit the Magnitude from 7.0 to 10.0.
  6. Click Submit Search at the bottom of the page, to display the data.
  7. Repeat steps 2 through 5 for this data, saving the file as Big_eq.csv and saving it into the Earthquake Data folder.

  8. If you were unable to complete this step, here is a cleaned-up file to use. Right-click (PC) or control-click (Mac) to download and save the file to your computer. Big Earthquakes in CSV (Comma Separated Values 11kB Aug6 11)

Step 7 –
Gather the Records of Earthquakes with a Magnitude of 5.0 or Greater that have Occurred in the Past Year

Use the USGS Earthquake Search to find earthquakes with a magnitude of 5.0 or greater that have occurred in the past year.
  1. Click the Global (Worldwide) Search link.
  2. Obtain the records by selecting Spreadsheet Format (comma-delimited) as the Output File Type.
  3. Choose the USGS/NEIC (PDE) 1973 - Present Data Base.
  4. Specify a Date range beginning one year ago and ending today.
  5. Limit the Magnitude from 5.0 to 10.0.
  6. Click Submit Search to display the data.
  7. Repeat steps 2 through 5 for this data, saving the file as LastYear_eq.csv and saving it into the Earthquake Data folder. Instead of using the word, LastYear, in the file name, include the actual year so that earthquake data from the year 2011 would be saved as 2011_eq.csv.
  8. Note: you will want to check to see how many records you have in your completed file. AEJEE GIS software will have difficulties with large files, ones with over 10,000 records. You may need to limit your search to greater magnitudes or smaller date ranges to enable AEJEE to function correctly.

If you were unable to complete this step, here is a cleaned-up CSV file for 2010-2011.
Right-click (PC) or control-click (Mac) to download and save the file to your computer.
2010-2011 Earthquakes with a Magnitude between 5.0 and 10.0 in CSV (Comma Separated Values 119kB Aug6 11)

Step 8 –
Combine the Significant and Big Earthquake Files into One Data Set

Copy and paste the two Excel files, Sig_eq.csv and Big_eq.csv, to create a combined file. Save the resulting file, Sig_Big_eq.csv, as a Comma Separated Values (CSV) file.
  1. Launch Excel and choose File > Open Sig_eq.csv
  2. File > Open Big_eq.csv.
  3. Edit > Copy all the records in Big_eq.csv.
  4. Scroll to the bottom of the records in Sig_eq.csv, click your cursor in the first empty row of cells below the data, and Edit > Paste all the Big_eq.csv records.
  5. Save the new combined data set by choosing File > Save As... Sig_Big_eq.csv. Be sure to save the data as a Comma Separated Values (CSV) file and save it into the Earthquake data folder.
If you were unable to complete this step, here is a cleaned up file for your use.
Right-click (PC) or control-click (Mac) to download and save the file to your computer.
Significant Plus Big Earthquakes in CSV (Comma Separated Values 50kB Aug6 11)