Spring 2013ArcGIS for GeoscientistsGEOL 596

Lab 3a: Importing XY data

Making a point layer from Excel

One of the most common things you will want to do in ArcGIS is bring in point data that you’ve collected, like sample locations, or downloaded, like earthquake locations. The steps that you will walk through today are useful for bringing any point data for which you have latitude and longitude coordinates into ArcGIS as a point layer. This will require some basic spreadsheet work, which we’ll do in Excel.

As you’ve already seen, little mistakes can throw big wrenches into this process. Sometimes totally invisible mistakes throw big wrenches into the process. Try not to get frustrated – just be very systematic in your work.

This lab has several parts:

  • Formatting data you already have in Excel (p. 2)
  • Downloading data and formatting it in Excel (p. 4)
  • Adding XY data to ArcGIS(p. 8)
  • Adding symbology and display options(p. 11)

This lab should give you the skills you need to get XY data into ArcMap from anywhere – really useful for a variety of purposes.

Formatting data you already have in Excel

Most research in geology involves some sort of spatially distributed point data that you’ve collected. Most geologic maps have some sort of structural information (strikes and dips on bedding, etc.), and most geologists also have sample locations. I’m going to use these two types of data as examples here – while the importing process is exactly the same, the techniques for setting up the data in Excel and applying symbology are somewhat different for each one.

In order to import properly into ArcGIS, your spreadsheet must have:

  • A single column for latitude in decimal degrees (xx.xxxxx), where N latitudes are positive and S latitudes are negative, and
  • A single column for longitude in decimal degrees (xxx.xxxxx), where E longitudes are positive and W longitudes are negative.

You must also know which datum the latitude and longitude data are referenced to. If you used a handheld GPS to collect the data yourself, you should be able to find that information on the GPS. If you estimated the lat-long by hand from a map, the paper map should have the datum printed on it. If you got the data from someone else, it’s possible they were responsible enough to annotate the spreadsheet with the datum. If not, you need to find out. Most commonly, the default datum for a handheld GPS unit is WGS 1984. (Remember, this is NOT a projection. It is just the reference for the geographic coordinates.)

It is also possible to import point data that has projected coordinates, like meters from a UTM projection. The main idea is that you need to know about your own data – are they projected or not, what is the datum if not, what is the projection if so.

You can then have any number of columns in your spreadsheet for whatever you want. You can also add columns once you bring it in to ArcGIS, but it is definitely easier to do so in Excel. Think about adding columns for information that you might use to display or sort the data.

Depending on the state of your data, you may or may not need to go through all of these steps.

  1. Open your spreadsheet in Excel.
  2. Save a new copy by selecting “Save as…” and make a new folder within the folder that contains your geodatabase and the DEMs folder. Call the new folder XY_data, and save your spreadsheet as fieldarea_datatype.xlsx (where fieldarea and datatype are replaced by the appropriate words).
  3. Look at your latitude and longitude columns. Are they in decimal degrees? There should be no symbols (like °) or letters (like N or S) in the column with the number. Do your data look different? Do not despair! There are lots of things you can do in Excel with formulas and text-to-columns to make this work.
  4. Is there a letter in the column with the number, separated by a space?Insert a column to the right. Select the column with the data in it. Go to Data  Text to Columns… Select Delimited and use the space to delimit the column; the letter will now be separated into the new column and you can delete it.
  5. Is there a symbol in the column with the number without a space?Insert a column to the right. Select the column with the data in it. Go to Data  Text to Columns… Select Delimited and use the symbol(s) to delimit the columns; the number might now be distributed among many columns (see below).
  6. Is the number in multiple columns? (e.g. degrees, minutes, seconds)Insert a column to the right of all three. Use a formula to calculate the decimal degrees from the degrees, minutes, seconds format. The formula is ((S/60) + M)/60) + D
  7. For your spreadsheet to import correctly, the first row must be your column headers, with no other information above it, and no other information in rows below all of the data. Make sure that this is the case with your spreadsheet.
  8. In addition, all of the column headers must be formatted according to strict rules:
  9. No spaces (these are often hiding at the end of words in Excel, and you should just check each header to make sure there are no hidden spaces). Underscores are allowed.
  10. No symbols, including ) ( < > , . : ; & % $ # * ^ etc. This rule means that you cannot have a header that is something like Elevation (m), which has both a space and two forbidden symbols. Elevation_m is OK.
  11. Column headers must start with a letter.
  12. Column headers must contain only letters, numbers, and underscores.
  13. Column headers must not exceed 64 characters.

Check all of your headers to see that they follow these rules. In particular, make sure that the columns that have your latitude and longitude data are clearly labeled as such.

  1. You also want to make sure that the data itself is formatted correctly, beyond the lat-long coordinates. In general, you want to keep a single value per column. For example, if you have a spreadsheet with geochronology data, you might have age and error data. Rather than having this in a single column, you want to put it into two columns.

Bad! / Good!
Age_error / Age_Ma / Error_Ma
12.31 +/- 1.21 / 12.31 / 1.21

Spend some time looking through all of your data and making sure you have it how you want it. You don’t have to have all numeric columns.

  1. Your Excel spreadsheet can have multiple worksheets. Even if it only has a single worksheet, go ahead and give it a name – you will see why when we add the file to ArcGIS. Do this by double-clicking on the tab at the bottom of the spreadsheet, which has a default of Sheet1. The name will become highlighted, and you can type what you want. If you have multiple sheets, name them all.

Downloading XY data and formatting it in Excel

Here are some places where you might find point data that you want to download:

  • The Global Earthquake Search ( at the USGS is a great place to download earthquake locations, depths, and magnitudes. Note that this is in beta form right now.
  • NAVDAT ( the Western North American Volcanic and Intrusive Rock Database, where you can download geochemical and geochronological data that has been submitted by others.
  • Something paleo
  • Something geothermal
  • Paleo mag
  • More examples

Each of these has slightly different processes for downloading the data, but the process once you have the data is exactly the same.

  1. Launch a browser and go to

  1. Click on Earthquakes in the upper left navigation.
  2. Click on in the right-hand navigation.
  3. Read the top paragraph on this page carefully. Imagine that you want to download all of the earthquakes in your field area, as far back in time as you can go. Fill in the search parameters accordingly.

  1. Once you are successful, you will get a return on the page that looks something like this:

Click on CSV. This does not actually prompt a file download, but gives you something like this instead:

Looks like a complete mess, right? Don’t worry.

  1. Select everything on the page, and copy it (right-click and select Copy, or use the menu or keyboard shortcut).
  2. Go to Excel and open a new workbook.
  3. Right-click in the upper-left-most cell (A1) and select Paste. This pastes all of the data into different rows, but all in the same column.
  4. With all of the data still selected (just the first column appears selected), go to Data  Text to Columns… and you should see this window:

If you are not already familiar with this process, you will be. It will become your friend.

  1. The data you have copied and pasted is in CSV, or comma-separated values, format. That means it is “delimited” (data in different columns is separated by commas) rather than “fixed width” (which has an equal number of spaces per column). So keep the default selection of delimited and click Next.
  2. Select the appropriate delimiter in the next screen (comma) and deselect the others. You should see the data separate into columns. Scroll through to see if it all looks OK.

  1. The final screen in the wizard offers you the opportunity to determine the format of each column. I tend to leave these all as “General” and just click right to Finish, here, because otherwise it can sometimes introduce errors into perfectly good data.

So you can just click Finish.

  1. Hooray! This is starting to look better, but there is still a lot of clean-up to do in order to bring it in to ArcGIS. Save your work in your XY_data folder, with a name something like fieldarea_EQs.xlsx
  2. Name the worksheet Fieldarea_EQs_all_data, and add a second worksheet (you can either click on the plus sign in the tab next to the first worksheet or select Insert  Sheet  Blank Worksheet). Name the second worksheet Fieldarea_EQs_edited.
  3. Take a look at all of the data. What all do you really want to have at your fingertips in ArcGIS? You know you want Latitude, Longitude, Depth, Magnitude. Do you care about anything else? Probably not. Copy and paste just the columns you want from EQs_all_data to EQs_edited. (Note: if you want to use the date and time information, you’ll need to clean it up to make it useful. You can do this by being clever with the text-to-columns process described above, but I won’t go through it now.)
  4. Save your work.
  5. Now go through the process described above for formatting your own data correctly. Check the headers and scroll through to make sure things look OK.

Now we can bring in both your data and the data you downloaded into ArcGIS.

Adding XY data to ArcGIS

There are a few different ways to bring your spreadsheet into ArcGIS. We’ll go through one of them here, and you can then find the other ways in the Help document if you need them. The principles are the same in all cases, so you should have no trouble.

  1. Launch ArcMap. If you saved the map you made with the DEM and hillshade, open that. If not, add them and make the colors and shading look the way you want them. Go ahead and save this if you haven’t already.
  2. The process for bringing in new data from an Excel file is a little different from adding a raster image or a feature class. Go to File  Add Data  Add XY data…
  1. This will open a new dialogue box for you to fill in.

Click on the file folder and navigate to your Excel spreadsheet. You will see that the spreadsheet has a symbol like this: . This is NOT what you want to add. You want to double-click on this, and you will be able to see the individual worksheets within the workbook, which will have symbols like this: . This is why you want to rename your worksheets with meaningful names. Select the sheet you want to add, and click Add.

  1. Most likely, some of the drop-down boxes will auto-populate, perhaps even with the right information! But you might need to modify them.

The fields correspond with the column headers you cleaned up. When you click on the drop-down boxes, you should see all of your column headers. NOTE:If you don’t see all of your headers, there is probably something wrong with your formatting, and you should go back and edit the Excel file, checking for extra spaces and random symbols.

In the X field, you should select your Longitude column; in the Y field, select Latitude. If you have elevation data in a column, you can select that in the Z field, but it’s not necessary.

  1. You also need to check the coordinate system. The program is probably just guessing, if there is anything at all showing up in the description box. Read what is in the box, and then click the Edit… button to find the right one.
  2. This brings you to the familiar spatial reference window, where you select the correct coordinate system. IMPORTANT:Here you are telling ArcGIS the coordinate system that the data are currently in, NOT the one you want them to be in. So if your GPS data are geographic, lat-long coordinates referenced to the WGS 84 datum, that is what you would select here.
  3. Once you’ve selected the coordinate system, it will show up in the description window, and you can click OK.
  4. Most likely, the following warning will appear:

Read it. It’s important. Because we are going to export to a feature class so that you can do the things you want to do, and because it makes your data more transferable. But for now, you can note that you have been warned, and click OK.

  1. Once you click OK, your points should show up on your map.
  • If they are not where you expected them to be, you probably chose the wrong coordinate system.
  • If you can’t even find them, you can right click on the layer and select Zoom to layer to see them – this is probably another indication that your coordinate system is off.
  1. In the Table of Contents, you will notice that your spreadsheet has the word “Events” attached to it, so it looks like this:

This is related to that warning you got. Before you do any messing around with the symbology and displays, you want to export this to a feature class so that the data live within your geodatabase and can be manipulated and edited the same as all of the other feature classes. So right-click on the layer and select Data  Export Data…

  1. You want to export your data to the feature dataset where all of the rest of your data lives:

The radio button may already be selected as the default – that’s fine. Then click on the file folder to navigate to the feature dataset. You should get to a window where you can see the continents file that we imported, along with the feature class you created. Change the default name (Export_Output) to something more meaningful (e.g., fieldarea_samples). Click Save.

  1. Click OK. You will be asked:

The answer is yes! You do.

  1. The new layer will show up in the Table of contents. You can now delete the Events layer, and work entirely with the new feature class you’ve created.
  2. Save your work.

Congratulations! You’ve now successfully brought point data in to both your feature dataset and your map.

Adding symbology and display options

There are infinite ways to display and label point data using symbology options in ArcMap. This will go through a few of the most common things you would do in a geologic map or figure.

  1. To get started, add your earthquake XY data to the map you’ve created, following the same steps as above.
  2. Right-click on the layer and select Properties.
  3. Click on the Symbology tab. The default symbology when you add a layer is typically a simple symbol for every feature, so you are likely to see something like this for your symbology window:

You can, of course, edit that symbol however you’d like by clicking on it, but the result is that every feature in that feature class will be displayed using the same symbol. That’s not terribly informative in the case of earthquake data, where you have not only location, but depth and magnitude, which you can display. For example, let’s say you want to color the symbols by depth and scale them by magnitude. You can do that! Let’s start with coloring the earthquakes by depth.

  1. Select Quantities in the left menu of the symbology tab.