Part 1: Gather and Format Delimited Earthquake Data
Step 1 – Note: This chapter was retired in July 2011. A similar EET chapter that uses the same data and ArcExplorer Java Edition (AEJEE) GIS software is available. See: Investigating Earthquakes with ArcExplorer GIS
Select the Output File Type and Set the Search Parameters
Note: This chapter was retired in July 2011. A similar EET chapter that uses the same data and ArcExplorer Java Edition (AEJEE) GIS software is available. See: Investigating Earthquakes with ArcExplorer GIS
Select the Output File Type and Set the Search Parameters
![USGS eq hazards logo thumb](https://cdn.serc.carleton.edu/images/eet/earthquakes/USGS_EQ_haz_thumb.jpg)
The United States Geological Survey (USGS) monitors earthquake activity all over the world, as well as in the United States. Earthquake records can be retrieved from several online data bases. 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 Earthquake Search.
- Select Spreadsheet Format (comma-delimited) as the Output File Type.
- Choose the Significant Worldwide Earthquakes (2150 B.C. - 1994 A.D.) Data Base. This data base contains the records of earthquakes that have caused either 1 million or more dollars in damage, 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. To find out more about the data base visit the metadata at NOAA Catalog of Significant Earthquakes.
- Specify a Magnitude range from 7.0 to 10.0.
- Click Submit Search to display the data.
Step 2 – Copy the Data from the USGS Web Page
Edit>Select All, then Edit>Copy all the earthquake data displayed from the search.
Step 3 – Paste the Data into a Spreadsheet
![Excel spreadsheet thumbnail](https://cdn.serc.carleton.edu/images/eet/earthquakes/Excel_thumb.jpg)
- Launch Excel or another spreadsheet application and Edit>Paste the copied data into a new file.
- Make sure Column A is highlighted.
- SelectData >Text to Columns...
- Use the Covert Text to Columns Wizard to format the comma-delimited data into several columns.
- 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.
- Delete the title "NEIC: Earthquake Search Results" and the extra blank rows at the top of the page as well as the menus at the bottom, leaving only the header and the data.
- 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 Edit>Cut or use the Delete key to remove the graphic.
Notice how the data paste into Column A.
Step 4 – Clean Up the Data
Step 5 – Format the Data to Create a "GIS-ready" File
Choose File>Save As... Sig_eq.txt to save the table as a tab-delimited or comma-delimited text(.txt) file. Depending upon your version and platform, 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 2.
Be sure to choose the text file option. Do not save the file as a comma separated values(.csv) file.
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.
Repeat steps 2 through 5 for this data, saving the file as Big_eq.txt.
- Obtain the records by selecting Spreadsheet Format (comma-delimited) as the Output File Type.
- Choose the USGS/NEIC (PDE) 1973 - Present Data Base.
- Specify a Date range beginning 01/01/1995 and ending with today's date.
- Limit the magnitude from 7.0 to 10.0.
- Click Submit Search to display the data.
Step 7 – Gather the Records of Earthquakes with a Magnitude of 4.0 or Greater that have Occurred in the Past Year
Use the USGS Earthquake Search to find earthquakes with a magnitude of 4.0 or greater that have occurred in the past year.
Repeat steps 2 through 5 for this data, saving the file as LastYear_eq.txt. Instead of using the word, LastYear, in the file name, include the actual year so that earthquake data from the year 2003 would be saved as 2003_eq.txt.
- Obtain the records by selecting Spreadsheet Format (comma-delimited) as the Output File Type.
- Choose the USGS/NEIC (PDE) 1973 - Present Data Base.
- Specify a Date range beginning one year ago and ending today.
- Limit the Magnitude from 4.0 to 10.0.
- Click Submit Search to display the data.
Step 8 – Combine the Significant and Big Earthquake Files into One Data Set
Copy and paste the two Excel files, Sig_eq.txt and Big_eq.txt, to create a combined file. Save the resulting file, Sig_Big_eq.txt, as a tab-delimited or comma-delimited text file.
- Launch Excel and File>Open Sig_eq.txt.
- File>Open Big_eq.txt.
- Edit>Copy all the records in Big_eq.txt.
- Scroll to the bottom of the records in Sig_eq.txt, click cursor in the first empty row of cells below the data, and Edit>Paste all the Big_eq.txt records.
- Save the new combined data set by choosing File>Save As... Sig_Big_eq.txt. Be sure to save the data as either a tab-delimited or comma-delimited text file.