Script

The Script feature enables use of programming-like code to automate various operations.

Start the Script feature using the Tools>Script.

The Script feature support three types of operations:

• dBase file processing

• GIS project file processing

• Modeler processing

This section reviews only the dBase file processing scripting.  This section illustrates use of the Script feature using a U.S. by county population estimates file.  However, the dBase scripting feature works with any dBase file.  The script feature is important for many reasons.

• It can automate operations that can be used by clicking buttons using the Tools>dBrowse operation.

• The script operations can also perform many tasks using dBase files that are not possible using the dBrowse feature.

• Unlike the dBrowse operation, a set of reusable commands can be stored in a file, called up and reused.

• By placing the operations in a file, a long sequence of operations can be performed in an unattended manner.

• dBrowse operations are sometimes tedious and require remembering certain steps, tasks or details.

• Scripting codes these details in a file that can be edited and re-run.

How it Works

Start the Scripting feature by clicking the main menu Tools>Script. At start-up, the user interface displays as shown below.

The Script form includes:

• top panel used for operation controls

• upper script section/panel -- includes a start-up set of code further discussed below.

• lower message section/panel -- displays the results of processing a script file.

• right DBF fields section/panel -- displays field attributes for the dbf open.

User may have a GIS project open that includes many layers.  When Script starts, the File combobox (above the Script section) is populated with the names of shapefile dBase files corresponding to layers in the active GIS project.  To start a new Script using one of these files, select the file from the dropdown and then dbl-click the file name.  The Script section will refresh with a db1open command and the filename selected.  A second command is automatically added db1showfilds.  Click the Run DScript button to view the fields in that file -- an easy way to start a script code section using a file of interest.

The Script user interface includes a Script list section and a Messaging list section.  The Script list section includes a set of commands that are operated on when the Run Database Script button is clicked.  This is a batch operation; all commands in the Script list section are processed to completion (or an error).

• Script statements/command lines are not case sensitive.

• Command line text is optionally terminated by "//" (no quotes)

-- text starting with // is not used and may be used as comments

As the Script commands are processed, the results are shown in the Messaging list section.  The content of the Messaging list section is also written to the text file c:\cvgis\1\scriptfile.txt.  The scriptfile.txt is overwritten each time the script processing is started.

The script makes use of commands.  The commands and brief description of each are summarized below.

the Script feature enables use of multiple dBase files.  The default version uses only one dBase file and is referred to as db1.  Note that all commands start with the dBase file being used (e.g., db1).

A db1open command must be the first command entered.  No other commands can be used unless a dBase file has been opened.  The syntax of Script commands is similar.  the command name starts the command line.  There is only one command per command line.

This version of Script has very limited syntax and related error checking. Review scripts carefully to avoid unexpected results.

Review of code used in the example

In the example shown below, a dBase file is opened and immediately saved to a temporary location.  The temporary file is then opened and used for the duration of the sample script.  These are command lines 1-4 in the example shown below.

The db1showfields command (line 5) lists the fields and their attributes (see output in "Messaging output" below).

The db1index1 command (line 6) creates an index file.  The index file determined the ordering of the records within the dBase file. The db1index1 command requires one or more fields on which the index is based.  The example used here uses the two fields statefp and countyfp.

Note that there are two steps to using and index file.  The db1index1 index command creates the index.  The db1useindex1 illustrates using the index file.  See command line 7 in the messaging output.

An index file useful for many reasons.  A dBase file must be indexed to use very fast Find commands -- find a record based on a criteria.

In the messaging output, see the db1find command (line 8) following the db1useindex1 command.  This command says "find the record with key value 04019 (in this example, this is the record for Pima County, AZ.  04 is the field statefp value for Arizona.  019 is the countyfp value for Pima County.

Following the db1find command is the db1profile command.  The db1profile command displays the field name and the field value for all fields for the currently active records.  Since the previous command was "db1find 04019", the profile will be for Pima County.  See in the Messaging output how all fields and their values are displayed.

Use of the db1count command is used several times in this sample script.  The db1count command has no parameters.  This command counts all records in the dBase file subject to whatever filters might be active.  In the first use of db1count in the example (line 10) the total number of records in the file as shown in the Messaging output is 3,233.  This is the number of records without any filters.  What if the analyses or processing wanted to focus on counties in only one state?

the db1filter command is illustrated in line 11.  The command includes the filter condition geoid='04013' that says to select only records where the geoid field name has the value 04013 -- this is one record and one county -- Maricopa County, AZ.  Note in the Messaging output that the count is 1 when the db1count command is used after the db1filter condition has been set.

A second db1filter sets the filter to substr(geoid,1,2)='04' -- this filter says to make the active records only those with the first two charactes in the geoid of 04 -- the FIPS state code for Arizona.  This command also illustrates use of dBase operators, such as substr (substring) in this example.  Note that the Messaging output shows that there are 15 records in the active dBase file -- 15 county records in Arizona.

The db1list command (line 19) illustrates an operation not available in dBrowse -- displaying values for a set of selected fields.  The values for these fields -- geoid,pop2010,pop2015,chg1015p -- specified by the db1list command are displayed in the Messaging output.

The db1saveas command (line 16) saves the active records to an output file.  The output file name is provided in the command line.  The file extension determines the format/structure of the output file:

• csv extension -- the file is written as a CSV structured file

• sdf or txt -- the file is written as a fixed-length field ACSII text file

• dbf -- the file is written as a dBase structured file

Using the db1filter command (line 17) with a null valued filter removes all filter conditions from the dBase file.  Note when the db1count command is used following that db1filter command, the record count shows all records in the files as available.  See in Messaging output.

Example

The numbers at the left have been added for reference.  There are no numbers permitted in actual command lines.

1 db1open 'c:\cvgis\1\cb_2015_us_county_500k.dbf' //generally first command line

2 db1saveas 'c:\cvgis\1\temp.dbf'  // command lines are truncated at // when // encountered

3 db1open 'c:\cvgis\1\temp.dbf'    //text to right of // works as comment

4 db1showfields

5 db1index1 statefp+countyfp

6 db1useindex1

7 db1find 04019

8 db1profile

9 db1count

10 db1filter geoid='04013'

11 db1count

12 db1filter substr(geoid,1,2)='04'

13 db1count

14 db1list geoid,pop2010,pop2015,chg1015p

15 db1saveas 'c:\cvgis\1\cb_2015_us_county_500k_14.csv'

16 db1filter ''

17 db1count

18 db1saveas 'c:\cvgis\1\temp.csv'

19 db1copystruc 'c:\cvgis\1\script_newfile1.dbf'

20 db1open 'c:\cvgis\1\script_newfile1.dbf'

21 db1appendfrom 'c:\cvgis\1\temp.csv'

22 db1exclusive true

23 db1appendfrom 'c:\cvgis\1\temp.csv'

24 3db1exclusive false

25 db1count

26 db1newfield STATEFP,C,2,0

27 db1newfield COUNTYFP,C,3,0

28 db1newfield COUNTYNS,C,8,0

29 db1newfield GEOID,C,5,0

30 db1newfield NAME,C,50,0

31 db1newfield LSAD,C,2,0

32 db1newfield ALAND,N,14,0

33 db1newfield AWATER,N,14,0

34 db1newfield STAB,C,2,0

35 db1newfield CBSA,C,5,0

36 db1newfield CEN2010,N,9,0

37 db1newfield POP2010,N,9,0

38 db1newfield POP2011,N,9,0

39 db1newfield POP2012,N,9,0

40 db1newfield POP2013,N,9,0

41 db1newfield POP2014,N,9,0

42 db1newfield POP2015,N,9,0

43 db1newfield CHG1015,N,9,0

44 db1newfield CHG1015P,N,9,1

45 db1newfield pop1415,n,9,0

46 db1create 'c:\cvgis\1\script_newfile2.dbf'

47 db1open 'c:\cvgis\1\script_newfile2.dbf'

48 db1exclusive true

49 db1appendfrom 'c:\cvgis\1\temp.csv'

50 db1assign pop1415,pop2015 - pop2014

51 db1showfields

Messaging output

Input command lines are repeated in the messaging output in the following format:

----->db1saveas 'c:\cvgis\1\temp.dbf'<-----

where the command line is shown between the >< brackets.

The following text appears in the Message section when the above commands are processed.