In ArcView there are a few ways to work with Microsoft Excel spreadsheet data:
Editing an existing ArcView ‘.dbf’ file in Microsoft Excel;
Creating a new table in Microsoft Excel and then importing it into ArcView;
Using ArcViews SQL connection feature to retrieve records from an existing Microsoft Excel spreadsheet.
Editing an existing ArcView ‘.dbf’ file in Microsoft Excel
A Theme consists of several files (Figure B.1), mostly files with the extensions ‘.sbn’, ‘.sbx’, ‘.shp’, ‘.shx’, and ‘.dbf’. If you want to put a Theme on a floppy disk, you have to copy all files to be able to display the Theme on another PC.
ArcView shapefiles are a simple, non-topological format for storing the geometric location and attribute information of geographic features. A shapefile is one of the spatial data formats that you can work with in ArcView.
The shapefile format defines the geometry and attributes of geographically-referenced features in as many as five files with specific file extensions that should be stored in the same project workspace. They are:
‘.shp’- the file that stores the feature geometry.
‘.shx’- the file that stores the index of the feature geometry.
‘.dbf’- the dBASE file that stores the attribute information of features. When a shapefile is added as a theme to a view, this file is displayed as a feature table.
‘.sbn’ and ‘.sbx’ - the files that store the spatial index of the features. These two files may not exist until you perform theme on theme selection, spatial join, or create an index on a theme’s Shape field. If you have write access to the source data directory, the index files will be persistent and remain after your ArcView session is complete. If you do not have write access to the source data directory, they will be removed when you close the project or exit ArcView.
‘.ain’ and ‘.aih’ - the files that store the attribute index of the active fields in a table or a theme’s attribute table. These two files may not exist until you perform Link on the tables. If you have write access to the source data directory, the index files will be persistent and remain after your ArcView session is complete. If you do not have write access to the source data directory, they will be removed when you close the project or exit ArcView.
FIGURE B.1
The different files making a shapefile in
ArcView
To edit the ArcView files with a ‘.dbf’ extension in Excel you first need to import the file into Excel by using (in Excel) File/Open... via the menu bar. At the bottom of the Open window (which will pop up) is a drop down list (Figure B.2) for Files of type: Select dBase Files (*.dbf) from the drop-down list, and open your file.
FIGURE B.2
The Open File dropdown list
While using Excel, you can make changes to the data, add fields, perform calculations, etc., in the spreadsheet. However, if you simply save the changed file as a ‘.dbf’ file (still using Excel) and then try to open it in ArcView, none of the changes you made will appear in ArcView.
To get around this problem is easy. When you have finished making changes to your file in Excel copy all columns and rows in your file (whether changed or unchanged) that have data in them and paste them into a new, empty spreadsheet in Excel.
When you copy these cells DO NOT copy them by selecting the entire worksheet for copying; but select only those cells that have data in them. If the spreadsheet contains columns with decimal data, then first format the columns and indicate the number of decimals (Figure B.3). This is of utmost importance if your spreadsheet contains X and Y-coordinates in longitude and latitude.
FIGURE B.3
Formatting your cells in
Excel
Before saving the new spreadsheet in Excel, be sure to click on one of the cells with data in it, so that it is highlighted. When ready to save the new spreadsheet in Excel, select File/Save As... via the menu bar. The Save As window will pop-up. Give the new spreadsheet a name (If you are editing a.dbf file of an existing ArcView Theme, then give it here the exact name of the original file). Make sure that it has a ‘.dbf’ extension by selecting from the dropdown list for ‘Save as type’ ‘DBF 4’(dBase IV) (*.dbf)’(Figure B.4).
FIGURE B.4
Saving a spreadsheet as a.dbf file in
Excel
When you click the Save button the following window will pop-up.
Click Yes. When you close the spreadsheet you will get the following message:
Click Yes. The following message will pop up:
Click Yes. The last message pops up:
Click Yes.
It seems complicated but it really works. Now you have saved the file and you can open and use it in ArcView.
Try to apply this method. On the CD in the folder ‘AN_Excel’ you can find the same world Theme you have used in the real world exercise world fisheries. Try to import the file ‘world.dbf’ into Excel, calculate population per hectare (take care that you need to multiply Pop_95 with 1 000 first!). and after that export this file as a.dbf file (as world.dbf). Start ArcView, Open a New Theme, and add the Theme ‘world.shp’. Look at the Attribute Table of world.shp, and you will see that the changes you made do not appear. Now use the method described above, taking care that if you have a calculation in a column, you need to copy this column and use Edit/Paste Special..., Values.
Creating a new table using Microsoft Excel and importing it into ArcView
Another method using Microsoft Excel, is to create an entirely new spreadsheet that can be incorporated into ArcView. The key here is to have one column in your Excel file that matches exactly a pre-existing column in the ArcView.dbf file that is associated with the Theme that you are working on so that you can join the two. Saving this spreadsheet in Excel in a.dbf format follows the procedures as given above.
Using the SQL connection in ArcView
The use of the SQL connection to import a Microsoft Excel spreadsheet directly into ArcView will be explained with the example of the Three districts in Pais Pesca as given in the chapter Joining a table with a polygon file on page 24. In this example you added the file with the data by importing the ‘.dbf’ file, which you joined then with the Theme. The data for the three districts are also available in an Excel spreadsheet. You will be shown how to import these data directly into ArcView.
1. Start ArcView, Open a New Project, and Open a New View. Add the Themes ‘Pais_pesca_country.shp’ and ‘Three_districts_pais_pesca.shp’ (from the ‘AN_Excel’ folder on your CD) to the View.
2. Open the Attribute Table of the ‘Three_districts_pais_pesca.shp’ Theme and you will see that it contains the ‘Shape’, ‘Subd_code’, and ‘Subd_name’ fields. You will have to join the data in the Excel file ‘Three_districts_pais_pesca.xls’ with this attribute table.
3. Close the View and go via the menu bar to Project/SQL Connect...
4. The SQL Connect window will appear and in the Connection drop down list you need to select ‘Excel Files’(Figure B.5).
FIGURE B.5
The SQL Connect window
5. Click Connect, after which the Select Workbook window will popup (Figure B.6). Go to the correct drive and the correct folder (‘AN_Excel’ on your CD), and select the file ‘Three_districts_Pais_Pesca.xls’, click OK.
FIGURE B.6
Selecting the correct Excel file for the SQL connection
6. You will come back in the SQL Connect window. Double-click on ‘Database’ in the Table box. In the Columns box all column headings of the selected spreadsheet will appear.
7. Select (by Double clicking the Column names) the names of the columns you want to import (For this exercise import the following Columns: Subd_code, Subd_name, Population, Total_pop, Rural_perc, and Urban_perc). The selected Columns will appear in the Select box. Give the table a name (in the Output Table: box) and click on Query (Figure B.7).
8. The generated table will open in ArcView. Join the generated table with the Theme using the same method as in the chapter Joining a table with a polygon file on page 24.
FIGURE B.7
Making a table from Excel data using SQL
