Creating DBF FIles with Excel
[ Credit ]
Objectives- This module contains the procedure for creating a DBF file in Excel for use in Atlas GIS
Opening Excel Spreadsheets in Atlas GIS
When opening Microsoft Excel files in Atlas GIS there are a number of rules which must be followed for the process to work properly.
· Confirm that none of the columns in the spreadsheet are hidden
· All field names must adhere to the dbase convention:
No spaces
No punctuation
Names can’t begin with a number
No longer than 10 characters
· The formats for the cells must correspond to the field types to which they will be translated. For example, a field containing numbers will, by default, be converted into a numeric field. In order to “force” the field into a character field, you must modify the first row. In the case of converting numerics to strings, placing an apostrophe in front of the value is generally sufficient.
· Check to make sure there are no blanks in any of the cells.
· Check DEFINE NAMED RANGE to make sure it is the correct spreadsheet extent
The spreadsheet must contain a key column with a unique value for each row. If one does not already exist, you can create one in Excel or later in Atlas by adding an arbitrary or sequential row number to each row. One you are prompted for the key field in Atlas, you will need to select this field.
Adding a Key Column to a Table Already Converted to DBF
Step 1
Select TABLE - DEFINE COLUMNS from the main menu
Step 2
ADD a field called ID with a type of STRING
Step 4 Select TABLE - CALCULATE COLUMN from the main menu.
Select the table which you would like to add the ID to.
Step 5
Set Column to Fill to the ID field which you added earlier
Set the Expression to Str(RecNo)) -- This will place each record’s sequential number in the ID field. The STR() function acts to convert the numeric record number into a character string which can be placed into the ID field whose type has been defined as "character."
Please note, if your ID field has been defined as a numeric field as opposed to a string (character) field, you should use the expression: recno() -- as in the example below.
Opening Excel Spreadsheets in Atlas GIS
When opening Microsoft Excel files in Atlas GIS there are a number of rules which must be followed for the process to work properly.
· Confirm that none of the columns in the spreadsheet are hidden
· All field names must adhere to the dbase convention:
No spaces
No punctuation
Names can’t begin with a number
No longer than 10 characters
· The formats for the cells must correspond to the field types to which they will be translated. For example, a field containing numbers will, by default, be converted into a numeric field. In order to “force” the field into a character field, you must modify the first row. In the case of converting numerics to strings, placing an apostrophe in front of the value is generally sufficient.
· Check to make sure there are no blanks in any of the cells.
· Check DEFINE NAMED RANGE to make sure it is the correct spreadsheet extent
The spreadsheet must contain a key column with a unique value for each row. If one does not already exist, you can create one in Excel or later in Atlas by adding an arbitrary or sequential row number to each row. One you are prompted for the key field in Atlas, you will need to select this field.
Adding a Key Column to a Table Already Converted to DBF
Step 1
Select TABLE - DEFINE COLUMNS from the main menu
Step 2
ADD a field called ID with a type of STRING
|
Step 4
Select TABLE - CALCULATE COLUMN from the main menu.
Select the table which you would like to add the ID to.
Step 5
Set Column to Fill to the ID field which you added earlier
Set the Expression to Str(RecNo)) -- This will place each record’s sequential number in the ID field. The STR() function acts to convert the numeric record number into a character string which can be placed into the ID field whose type has been defined as "character."
Please note, if your ID field has been defined as a numeric field as opposed to a string (character) field, you should use the expression: recno() -- as in the example below.
|
| <- Back | Next -> |