7.03.03 Create Attribute Table in Excel and Join in the GIS Software

Fri, 11/23/2007 - 11:16

In this exercise we will build up an attribute table for a planning subject and make necessary arrangements so the data can be linked to the features of the subject on the map in the GIS software.

ARRANGING THE DATA

Keywords: Excel, dBASE

Software: Excel, ArcGIS 8/9.x

Preconditions: Folder directories for the CLUP has been created previously; see 3.04.02 Data Preparation for more information. The data to be encoded has been gathered from the field. Spatial layers needed have been prepared beforehand.

The building up of the attribute data will be done in Excel.

The ‘Instructions’ sheet in Quick Look gives you information what to consider at data gathering and where to find the templates you need for the specific table.

From these instructions this table IS01 is defined as a Key table meaning that all LGU’s should prepare it for analysis purposes in the CLUP. However, some of the columns (“A”) might have a light yellow colored background (meaning the data is “Optional”) as opposed to the light green colored columns (it is a “Required” data). In the example below which describes segments in a road network, the data for ‘width’ (of the road) column may not be required if it is very hard to get. Each column has corresponding codes (“B”) for the Field Names.

The data to be encoded for the respective road segment is (hopefully) prepared by the caretaker of the municipal road management (the Engineering Department). He/she is the custodian of the attribute data and in this case he/she has managed to get the data. On the other hand, if in case the road has no individual name, that column is blank. So the populated dataset to start from will look something like this:

The data to be encoded for the respective road segment is (hopefully) prepared by the caretaker of the municipal road management (the Engineering Department). He/she is the custodian of the attribute data and in this case he/she has managed to get the data. On the other hand, if in case the road has no individual name, that column is blank. So the populated dataset to start from will look something like this:

For example, you can see that the road segment with ID 101 is a municipal road, has a gravel surface and is in a poor condition.

The next step is to convert the file to dBase format. Unfortunately dBase is an old MS Dos format which means that the dataset must be very ‘clean and appropriate’ to be used.

In the example below the data has been encoded already digitally so there is an opportunity to start from that. If the data is still in a paper format, it is recommended to use the GIS Table to encode the data and save it in dBase format. (How to will be explained later).

Highlight and copy the cells that should be found in the dBase file:

Open a new Excel file and paste in cell A1. Save the file with a filename in the specific CLUP GIS folder as recommended in the Quick Look.

Before saving the file in dBase format, some pointers are needed to be remembered:

The field name of each column must be in a single cell dBase format. Highlight the column head and do procedures A, B and C to format the field name:

Only one sheet is allowed in dBase. Remove sheet 2 by right clicking on the flip and choosing delete. Do the same procedure with sheet 3:

Highlight the cells with the information, then from the File menu select Save As…, navigate to the CLUP directory, (1) locate the specific sector you are working on and into the GIS folder. (2) In the Save as type select dBase4 format and (3) type the name of the file preferably the same as the Excel file:

Click Save:

A Dialogue box will appear, informing you to save only the active sheet.

Click OK:

Another Dialogue box will appear asking if you want to keep the workbook in its existing format.

Click exit (1) and Yes (2)

A Dialog box will appear asking if you want to save changes you made to the file.
Click YES.

Again the Dialog box informing you that the only one to be saved is the active sheet will appear.

Click OK again.

Then a another Dialog box informing you that the file may contain features that are not compatible with DBF 4 (dBASE IV) and asking if you want to keep the workbook in that format.

Just click YES.

Please remember to have Excel closed when you work with ArcView.
If you want to revise a .dbf file you should close ArcView before you open Excel.
Now it is time to join the attribute data with the shape file you have made for the road:
Open ArcView and open the project you were working on before. Right click on the road layer and select ‘Join…’:

  1. Id’ was the column head or field name where you encoded the unique IDs for the road segments.

  2. IS01_RdYr’ is the name of the dBase table you have prepared that contains the attribute information of the road segments.

  3. RD_ID’ is the field name of the column where the unique ID has been encoded.

  4. Click OK

Let’s see how the table looks like by opening the Attribute Table.

The columns from the dbf table have been integrated into the road segment table:

To make it easier to read we need to hide some columns and give proper alias. Right click on the file name and select ‘Properties…’:

Select Field. Select name by name (A), un tic if it should not be visible (B), if it will be kept visible type an alias in the box (C) and click OK (D):

Ver 1.0


AttachmentSize
7.03.03 Create Attribute Table in Excel and Join in the GIS Software1.09 MB
Back to HLURB Website