ArcGIS Pro

Work with Microsoft Excel and CSV files in ArcGIS Pro

In the age of databases and cloud based data storage, many organizations still maintain valuable data in Microsoft Excel and comma-separated value (CSV) files. In this blog post, you will explore a workflow for importing geological data maintained by a mining company in Microsoft Excel and CSV files for use in an ArcGIS Pro project.

The data used in this blog was collected and generated by Mike Price, Entrada/San Juan, Inc.  It consists of fictitious sample data, that is true to the underlying geology. Landownership information is imaginary,but reflects exploration trends around Battle Mountain, Nevada, in the early 1990s.  Additional source data was derived from the Nevada Bureau of Mines and Geology County mapping series and the US Department of Energy National Uranium Resource Evaluation (NURE) program.

Download and review rock and soil sample data

1. Click Import_Excel_to_Pro and choose Download.

2. Unzip the Import_Excel_to_Pro.zip in a project area on your local machine.

3. Browse to your project area, expand the Battle_Mountain folder, then expand the XLSFiles folder.

Locate Excel files

4. Locate and open Sample_Locations.xlsx  in Microsoft Excel.

The Sample_Locations workbook contains two worksheets named Rock and Soil.

5. If necessary, click the Rock worksheet, to review the table content.

Review Rock worksheet
Review Rock worksheet

The Rock worksheet contains sample numbers, coordinates, and field information that will allow this data to be displayed on a map.

6. Click and review the Soil worksheet.

Review Soil worksheet
Review Soil worksheet

Similar to the Rock worksheet, the Soil worksheet also contains sample numbers, coordinates, and field information.

7. Open and review the Rock_Data.csv file in Microsoft Excel.

Review Rock_Data file
Review Rock_Data file

Notice that the SAMPLENO field in the CSV files will support a one-to-one tabular join with the same field in the Soil and Rock worksheets.

8. Open and review the Soil_Data.csv  file.

Review Soil_Data file
Review Soil_Data file

Both CSV files were obtained from an assay laboratory and contain results of chemical analysis of rock and soil samples taken at locations in the Battle Mountain area. Values in the tables represent trace elements used in gold exploration and are indicator minerals used to assess the potential for finding viable gold deposits.

9. In the Soil_Data.csv  file, inspect the alignment of data in table columns.

Review data column alignment
Review data column alignment

Notice that SAMPLENO, AU_PPB and SB_PPM are aligned on the left side of the column. Left alignment generally denotes that a column contain text strings and not numbers that may be used to determine values greater than a specific range.

Review right aligned columns
Review right aligned columns

The AG_PPM, AS_PPM, and HG_PPB columns are aligned on the right. Right alignment identifies a column as containing numeric values.

Update and prepare data in Microsoft Excel

When ArcGIS Pro reads an Excel workbook with worksheets, the first data record  in the workbook or file defines the field type. Consequently, for many worksheets, some data preparation is necessary to ensure the data is correctly identified in ArcGIS Pro. The next step will be to prepare the workbook containing rock and soil worksheets and convert and update  the CSV files containing assay data  before using them in ArcGIS Pro.

Some best practices are as follows:

Before making updates to the worksheet, lets make a copy of the workbook so you can retain the original data as an archive.

1. In Excel, save the workbook to a new workbook named Sample_Locations_ Import.xlsx.

2. In Sample_Locations_Import workbook open the Soil worksheet and review the first row of data containing the column names.

The table contains 20,096 records, representing soil sample points collected in UTM North American Datum 1983 (NAD83) Zone 11 Meters. Coordinates are maintained in the Easting and Northing fields and are displayed using a precision of 0.01 meters. In addition, many samples are coded by Claim Group.

Review Soil worksheet column names
Review Soil worksheet column names

Notice that several column names contain spaces. Recall that field names must contain only letters, numbers, and underscores.

3. In the Soil worksheet, for column names, change the spaces to underscores.

Update Soil column names
Update Soil column names

4. In the Soil worksheet, change the Easting column name to UTM83Z11_E.

5. In the Soil worksheet, change the Northing column name to UTM83Z11_N.

Update Easting and Northing column names
Update Easting and Northing column names

6. In Sample_Locations_Import workbook open the Rock worksheet and inspect the data.

7. In the Rock worksheet, review the first row of data containing column names, and then update names by replacing spaces with underscores.

8. In the Rock worksheet, change the Easting column name to UTM83Z11_E and the Northing column name to UTM83Z11_N.

Update Rock column names
Update Rock column names

9. Save and close the Sample_Locations_ Import.xlsx

Next, you will edit and format the companion laboratory assay data for the Rock and Soil worksheets.

10. If necessary, open the Rock_Data.csv file in Excel.

11. Save the file as Rock_Data_Import.xlsx  so you have the original CSV file and a new copy in Excel.

12. Review the table for improper field names and inappropriate data formats.

Review Rock_Data table
Review Rock_Data table

The field names appear to be correctly formatted, but a significant number of records are coded with a less than (<) symbol. These records each contain less than the minimum detectable amount of a specific element. Sometimes, a sample contains more than a maximum detectable amount and is coded with a greater than (>) symbol. The over-limit samples in this dataset have already been resolved, so only the less than values need fixing.

13. In Excel, use Find and Replace to find < 0.5 and replace all with 0.1.

Find and Replace
Find and Replace

14. Use Find and Replace to change all additional values below the detection limit for arsenic, gold, mercury, silver and antimony with values shown in table below.

Element Abbr. Detection Limit Change From Change To
Silver Ag 0.5 ppm <0.2 0.1
Silver Ag 0.5 ppm <0.5 0.1
Antimony Sb 5.0 ppm <5 1
Arsenic As 5.0 ppm <5 1
Gold Au 5.0 ppm <5 1
Mercury Hg 10.0 ppm <10 2

When done, review the table and search for the < and > characters to confirm that all the above and below limit values have been located and updated.

15. In the table, right click the AU_PPB field and select Format Cells.

Format columns
Format columns

The Format Cells pane displays. Notice that the field is currently categorized as General.

General categorization
General categorization

16. In the Format Cells pane, click Number to change the field category.

17. Change Decimal places to 0, and then click OK.

Change field category
Change field category

18. In the same manner, change the field category from General to Number for the AG_PPM, AS_PPM, SB_PPM, and HG_PPB

19. For the SAMPLENO field, change the field category from General to Text.

20. Save and close the Rock_Data_Import.xlsx table.

21. On your own Update Soil Data.

22. Save and close the Soil_Data_Import.xlsx  table.

You have completed data preparation of the battle mountain data currently stored and maintained in Excel and CSV files. Next, you will import and use the data in ArcGIS Pro.

Use Excel files in ArcGIS Pro

Microsoft Office Excel tables may be used directly in ArcGIS Pro and you can work with them in the same way as other tabular data sources. For example, you can add them to the map, open them in the fields view, and use them as inputs to geoprocessing tools. However, to work with Excel files in the current release of ArcGIS Pro, you must download and install the ‘Microsoft Access Database Engine 2016 Redistributable’ from the Microsoft Download Center.

Please verify the Microsoft Access Database Engine 2016 is successfully installed and operational before continuing.

Review the help topic Install the Microsoft Access Database Engine driver for updated instructions.

1. Launch ArcGIS Pro.

2. For New, select Map.

3. In the Create a New Project pane, for Name, type BattleMountain.

4. For Location, browse and select your Battle_Mountain folder.

5. Click OK.

Create a new project
Create a new project

6. In the Catalog pane, click and expand Folders.

7. Right-click Folders and select Add Folder Connection.

Add folder connection
Add folder connection

8. In the Add Folder Connection pane, browse and add a connection to the Battle_Mountain\XLSFiles folder.

9. Click OK.

Select folder location
Select folder location

10. In the Catalog pane, under Folders, expand XLSFiles.

11. Review the folder contents.

Review folder contents
Review folder contents

12. Click and expand Sample_Locations_Import.xlsx.

Expand workbook
Expand workbook

In the expanded multi-sheet workbook, worksheets display with a dollar sign ($) at the end of the name.

13. Right-click Rock$ and select Add To Current Map.

Add worksheet to map
Add worksheet to map

14. In the Contents pane, under Standalone Tables, right-click Rock$ and select Display XY Data.

Display XY Data
Display XY Data

15. In the XY Table To Point geoprocessing tool, update the following parameters:

XY Table To Point tool
XY Table To Point tool

16. Click Run.

The tool creates a new feature class named RockSampleLocations in the project geodatabase and adds the feature class as a layer to the map displaying rock sample location points in the Battle Mountain Nevada region.

Rock sample points
Rock sample points

17. In the Contents pane, right-click RockSampleLocations and select Attribute Table.

Display layer attributes
Display layer attributes

The layer attribute table displays. Note the layer field names derived from the original column names in the excel table. You will use the Sample_Number field to join the laboratory assay data from the Rock_Data table.

Layer attribute table
Layer attribute table

18. Close the RockSampleLocations table.

19. In the Catalog pane, under Folders, expand Rock_Data_Import.xlsx.

20. Right-click Rock_Data$ and select Add To Current Map.

Add rock assay data to map
Add rock assay data to map

21. In the Contents pane, under Standalone Tables, right-click Rock_Data$ and select Open.

Review rock data table
Review rock data table

22. In the Rock_Data$ table, right-click the SAMPLENO field and select Fields to display a fields view of the table.

Display fields view
Display fields view

23. Review the Fields: Rock_Data$ view.

Notice the field data types, these types are the same as the categories you assigned to columns in Excel.

Fields view
Fields view

24. Close the Rock_Data$ table and the Fields view.

Next you will join the Rock_Data table records to the Rock Sample locations for analysis and display.

Join assay data and symbolize gold values

1. In the Contents pane, right-click RockSampleLocations and select Joins and Relates, and then choose Add Join.

Create table join
Create table join

2. In the Add Join geoprocessing tool, update the following parameters:

Add Join tool
Add Join tool

3. Click Run.

4. In the Contents pane, right-click RockSampleLocations and select Attribute Table.

5. Review the RockSampleLocations attribute table. Scroll to the right to display the joined fields.

Joined table
Joined table

6. Close the RockSampleLocations table when done.

7. In the Contents pane, right-click RockSampleLocations and select Symbology.

8. In the Symbology pane, for Primary symbology, choose Graduated Colors.

Change layer symbology
Change layer symbology

9. In the Symbology pane, update the following:

Graduated symbology parameters
Graduated symbology parameters

The map updates to display rock sample locations with graduated colors representing parts per billion of gold detected in samples.

High gold values
High gold values

10. Review the map and note the location of several samples displaying a high value for gold. You can confirm these locations by selecting the points and reviewing the AU_PPB field values in the layer table.

Selected high gold samples
Selected high gold samples

11. On your own, Display soil samples with assay information.

12. When you have completed all steps, save the project

Congratulations, you have now successfully imported several Excel tables to generate new geodatabase feature classes and joined assay data to the points for visualization.

About the author

Colin Childs is a product engineer for Learn ArcGIS team at Esri who has been doing GIS for 30 years. Before coming to Esri in 2001, he was an Esri software instructor for the Esri distributor in South Africa. A CompTIA Certified Technical Trainer (CTT+), Childs holds Esri Certified ArcGIS Desktop Professional, Esri Certified Enterprise Geodatabase Management Professional, and Esri Certified Enterprise Geodatabase Management Associate and was an instructor at Esri’s corporate offices in Redlands for 15 years. He holds bachelor and honors degrees in geography from Rand Afrikaans University (Now UJ -University Of Johannesburg).

Next Article

Pop-ups: the essentials

Read this article