Script -- a programming tool to develop and save code

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:\cvxe\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.




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


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

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

3 db1open 'c:\cvxe\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:\cvxe\1\cb_2015_us_county_500k_14.csv'

16 db1filter ''

17 db1count

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

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

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

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

22 db1exclusive true

23 db1appendfrom 'c:\cvxe\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:\cvxe\1\script_newfile2.dbf'

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

48 db1exclusive true

49 db1appendfrom 'c:\cvxe\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:\cvxe\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.


db1open 'c:\cvxe\1\cb_2015_us_county_500k.dbf'

db1open 'c:\cvxe\1\temp.dbf'  

db1open 'c:\cvxe\1\script_newfile1.dbf'

db1open 'c:\cvxe\1\script_newfile2.dbf'

db1appendfrom 'c:\cvxe\1\temp.csv'

File saved>c:\cvxe\1\temp.dbf<


----->db1open 'c:\cvxe\1\temp.dbf'   <-----

.. db1 file opened.






4 GEOID C 5 0

5 NAME C 100 0

6 LSAD C 2 0

7 ALAND N 14 0

8 AWATER N 14 0

9 STAB C 2 0

10 CBSA C 5 0

11 CEN2010 N 9 0

12 POP2010 N 9 0

13 POP2011 N 9 0

14 POP2012 N 9 0

15 POP2013 N 9 0

16 POP2014 N 9 0

17 POP2015 N 9 0

18 CHG1015 N 9 0

19 CHG1015P N 9 1


----->db1index1 statefp+countyfp<-----

db1 index 1 created.



db1 index 1 opened; now in use.


----->db1find 04019<-----

db1 find successful.



db1 profile below.

statefp      04

countyfp     019

countyns     00025446

geoid        04019

name         Pima                                                                                              

lsad         06

aland             237943254

awater              5273054

stab         AZ

cbsa         46060

cen2010         980263

pop2010         981870

pop2011         988024

pop2012         992973

pop2013         997418

pop2014        1004244

pop2015        1010025

chg1015          28155

chg1015p           2.9



Counting ... wait ...

Counted 3233 records


----->db1filter geoid='04013'<-----

.. db1 filter applied: >geoid='04013'<



Counting ... wait ...

Counted 1 records


----->db1filter substr(geoid,1,2)='04'<-----

.. db1 filter applied: >substr(geoid,1,2)='04'<



Counting ... wait ...

Counted 15 records


----->db1list geoid,pop2010,pop2015,chg1015p<-----

geoid pop2010 pop2015 chg1015p

 04001     71766     71474      -0.4

 04003    131809    126427      -4.1

 04005    134626    139097       3.3

 04007     53544     53159      -0.7

 04009     37137     37666       1.4

 04011      8339      9529      14.3

 04012     20446     20152      -1.4

 04013   3825597   4167947       8.9

 04015    200374    204737       2.2

 04017    107715    108277       0.5

 04019    981870   1010025       2.9

 04021    379384    406584       7.2

 04023     47410     46461        -2

 04025    211122    222255       5.3

 04027    197069    204275       3.7


----->db1saveas 'c:\cvxe\1\cb_2015_us_county_500k_14.csv'<-----

File saved>c:\cvxe\1\cb_2015_us_county_500k_14.csv<


----->db1filter ''<-----

.. db1 filter applied: ><



Counting ... wait ...

Counted 3233 records


----->db1saveas 'c:\cvxe\1\temp.csv'<-----

File saved>c:\cvxe\1\temp.csv<


----->db1copystruc 'c:\cvxe\1\script_newfile1.dbf'<-----

.. db1 structure copied to c:\cvxe\1\script_newfile1.dbf


----->db1open 'c:\cvxe\1\script_newfile1.dbf'<-----

.. db1 file opened.


----->db1appendfrom 'c:\cvxe\1\temp.csv'<-----

.. db1 .. no records appended .. first set exclusive to true


----->db1exclusive true<-----

.. db1 exclusive set to true.


----->db1appendfrom 'c:\cvxe\1\temp.csv'<-----

.. db1 appended from >c:\cvxe\1\temp.csv<


----->db1exclusive false<-----

.. db1 exclusive set to false.



Counting ... wait ...

Counted 3234 records


----->db1newfield statefp,c,2,0<-----

db1 newfield >statefp,c,2,0<


----->db1newfield countyfp,c,3,0<-----

db1 newfield >countyfp,c,3,0<


----->db1newfield countyns,c,8,0<-----

db1 newfield >countyns,c,8,0<


----->db1newfield geoid,c,5,0<-----

db1 newfield >geoid,c,5,0<


----->db1newfield name,c,50,0<-----

db1 newfield >name,c,50,0<


----->db1newfield lsad,c,2,0<-----

db1 newfield >lsad,c,2,0<


----->db1newfield aland,n,14,0<-----

db1 newfield >aland,n,14,0<


----->db1newfield awater,n,14,0<-----

db1 newfield >awater,n,14,0<


----->db1newfield stab,c,2,0<-----

db1 newfield >stab,c,2,0<


----->db1newfield cbsa,c,5,0<-----

db1 newfield >cbsa,c,5,0<


----->db1newfield cen2010,n,9,0<-----

db1 newfield >cen2010,n,9,0<


----->db1newfield pop2010,n,9,0<-----

db1 newfield >pop2010,n,9,0<


----->db1newfield pop2011,n,9,0<-----

db1 newfield >pop2011,n,9,0<


----->db1newfield pop2012,n,9,0<-----

db1 newfield >pop2012,n,9,0<


----->db1newfield pop2013,n,9,0<-----

db1 newfield >pop2013,n,9,0<


----->db1newfield pop2014,n,9,0<-----

db1 newfield >pop2014,n,9,0<


----->db1newfield pop2015,n,9,0<-----

db1 newfield >pop2015,n,9,0<


----->db1newfield chg1015,n,9,0<-----

db1 newfield >chg1015,n,9,0<


----->db1newfield chg1015p,n,9,1<-----

db1 newfield >chg1015p,n,9,1<


----->db1newfield pop1415,n,9,0<-----

db1 newfield >pop1415,n,9,0<


----->db1create 'c:\cvxe\1\script_newfile2.dbf'<-----

.. dbf file created >c:\cvxe\1\script_newfile2.dbf<


----->db1open 'c:\cvxe\1\script_newfile2.dbf'<-----

.. db1 file opened.


----->db1exclusive true<-----

.. db1 exclusive set to true.


----->db1appendfrom 'c:\cvxe\1\temp.csv'<-----

.. db1 appended from >c:\cvxe\1\temp.csv<


----->db1assign pop1415,pop2015 - pop2014<-----

.. db1 1 field replaced ... >pop1415<






4 GEOID C 5 0

5 NAME C 50 0

6 LSAD C 2 0

7 ALAND N 14 0

8 AWATER N 14 0

9 STAB C 2 0

10 CBSA C 5 0

11 CEN2010 N 9 0

12 POP2010 N 9 0

13 POP2011 N 9 0

14 POP2012 N 9 0

15 POP2013 N 9 0

16 POP2014 N 9 0

17 POP2015 N 9 0

18 CHG1015 N 9 0

19 CHG1015P N 9 1

20 POP1415 N 9 0



ProximityOne -- resources to create and apply insights © ProximityOne