4.20.01 Create an Attribute Data in Excel and Save in dBase Format

Excel comes with the basic MS office package and seems to be a quite familiar tool throughout the municipalities for spreadsheet work, thus Excel will be used for the attribute data entry and maintenance. Compared to other procedures it might not be the quickest method to acquire data for the GIS, however it is relatively simple and easy to be operated by the LGU staff and in computers found in the various offices involved in the introduction of the GIS in the LGU.

Later on when the staff become more familiar with standards and procedures of how to keep the Geographic Information updated and well maintained, and the GIS software becomes available elsewhere other than in the Planning Office, more sophisticated methods and more advanced software can be practiced.

Capture the Data
The procedure how to get the data is described in Chapter… ‘Search for data’

Organize the Data in Digital Format
In a basic GIS, it is recommended to use Excel for building the attribute data for non-spatial datasets. Two methods will be needed for the CLUP:

The first one will be used in the GIS and will be joined with the spatial data that must have a very basic design, as it will be saved in the dBase format in ArcView or other GIS Software. Details are discussed in the latter part of this section, or for more information about the dBase format, refer to Chapter…. Tutorials.

In brief the following must be followed:

  1. Only 8 alphanumeric in the column heads;
  2. No merging or text wrapping of the cells

The second one will be used in the narrative/documentation part of the CLUP. It is recommended that these tables (also) have a uniform layout and should have an appealing design to attract interest. This version of the table will also be used when graphs are prepared for further planning analysis.

There are many ways to create an attribute database to be merged with spatial data in a Geographic Information System. It can be built within the GIS software (e.g. ArcView). Access or other (attribute) database software can also be used. At this point, in order to consider the awareness level among the trainees, the availability of computers and software and the distribution of responsibilities for the data gathering and data maintenance in the LGU, a very basic and practical approach is proposed to be used for the guidelines.

Work with a Template
As mentioned in Chapter 5.01.01 ‘Quick Look’, metadata table templates are made available for data gathering of different planning sectors needed in the formulation of the CLUP. These templates are made possible to provide comprehensible and easy-to-understand procedures for organizing data.

The table templates will provide working resources for handling of data used for planning for the efficient provision of adequate and equitable services. However, there may be other indicators and data needed for efficient management and planning strategy, these templates are just a few examples that can be used for CLUP preparation.

Basic instructions are discussed in the ‘ReadMeFirst’ section of the Quick Look, thus the following procedures can also be pursued to be able to work on the attribute tables using the templates:

  1. Open the Excel software by clicking the Excel icon in the computer desktop (or from the Start button, click Programs, then click Microsoft Excel)
  2. In the CLUP file directory navigate to the ‘Metadata Tables/ 02_Socio Economic/ 03_Education‘ and open the specific sector table needed. (Example: SE20_Education: Capacity & Condition of School, Year???)

    The basic metadata table looks like this:

  3. Copy the row for the coding of the field names with the field description of the table, open a new Excel table and paste in cell 1A, which should look like this:

Now you have the working table where the data needed for the CLUP can be inputted by the planning unit or other data custodians for a certain planning sector.

Saving the attribute data in Excel can be done in two ways; the first one is by saving it as a Microsoft Excel Workbook with the prescribed file name under the specified sector folder you are working on, if the data are not yet complete. This Excel file would serve as the working table for the continuation of the data entry procedure.

Before saving to dBase format, please take note of the following:

  1. The encoded data should be formatted according to how the user wants it to be seen in dBase format. A number with decimal values (e.g. 3.141) but displayed as a whole number (e.g. 3), will be saved whole numbers in dBase. Make sure to set the cell properties according to the data type and format required before saving.
  2. Fields with blank entries will be converted as text data types.
  3. The Unique ID field is important for linking. Make sure that it is of the same data type as that of the Unique ID field in the attribute table of the spatial data. Ideally, unique ID should be a Number Data type. If the user will assign the Unique ID, it should be as a number data type. But if a structured ID is assigned (like the PSGC or PIN), text will be required in order to maintain the structure.

Once the data is completed, it can now be converted or saved in dBase format following some simple procedures:

  1. Click on the first cell with the field name (code only), without releasing the mouse click, drag the cursor to the last field column then down to the last row containing the data;
  2. Then from the ‘File’ menu click on ‘Save As…’ navigate to the GIS folder of the specific planning sector you are working on and the same file name as recommended in the metadata ‘Instructions’ can be used;
  3. In the ‘Save as Type…’ search for the ‘DBF 4 (dBASE IV)’ format, then click Save.

Work Without a Template
For planners or data custodians who are not able to have the table templates, creating attribute tables in Excel is as easy as following some simple procedures:

  1. On a new Excel spreadsheet, click on cell A1 so that there will be a thick frame around the cell space. The cell is now active for data entry.
  2. Type ‘ID’, it is a specific and unique identification number given to each data or information. It will be the linking agent to the spatial information found in the table attached to the location (of a school for example), which has been organized in ArcView or any other GIS Software.
  3. Move to the next cell on the right (cell B1), and type ‘NAME’.
  4. Continue to insert the codes for the succeeding column heads needed for the table. The reason for the abbreviated coding is that the heading of the column has to be set to not more than 8 digits in order to be accepted by ArcView and other GIS Software.

The result of creating the field names for the columns where the data will be inserted should look like this:

Saving the table follows the same procedures mentioned above.

Remember the following when creating a table in Excel to be used in ArcView or any other GIS Software available:

  1. Always start in the upper corner of the Excel worksheet (cell A1);
  2. Use a maximum of 8 digits/letters for the field names or code;
  3. Use a uniform spelling for the data (ex: name of a school);
  4. Organize files in an easy-to-find way in the specified file directories for fast and easy retrieval;
  5. Exit/close Excel when working in ArcView or any other GIS Software, and exit/close ArcView or other GIS software when working with the attribute table in Excel.

For detailed and step-by-step procedures on saving attribute data in Excel and joining/linking it to ArcView or other GIS software tables, see Chapter 7.03.03.