|
<< Click to Display Table of Contents >> Navigation: Command Language > Preprocessor > Preprocessor databases |
Database commands use a spreadsheet file to control the CL script output.
The commands are *DBOPEN, *DBCLOSE, *DBLOOP, *DBEND, *DBCOLUMN, *DBFINDROWS, and *DBLINES.
This command opens a spreadsheet file, usually created with Excel.
Any sheet referred to must have a header row with unique names above each column.
The form of the command is:
[*DBOPEN dfindex = filename]
The database file index dfindex cannot not be used for anything else.
Use quotes around the file name if it contains spaces.
Files opened with *DBOPEN should be closed with *DBCLOSE when they are no longer needed.
Examples of *DBOPEN commands:
[*dbopen dfNameFile=’Names List.xls’]
[*dbopen dfMain=C:\MyDB\Main.xls]
This command closes an open spreadsheet file. The form of the command is:
[*DBCLOSE dfindex]
Where the database file index dfindex was used in the *DBOPEN command.
Any number of *DBLOOP commands can be used on an open file.
Examples of *DBCLOSE commands:
[*dbclose dfNameFile]
[*dbclose dfMain]
This command starts a loop which scans all the rows in a sheet. The form of the command is:
[*DBLOOP dlindex = dfindex;sheetname]
[*DBLOOP dlindex =(start)dfindex;sheetname]
Where the database loop index dlindex is used withing the loop to refer to the relevant row in the loop.
Where the database file index dfindex was used in the *DBOPEN command. The file must be opened and not yet closed.
Where start is the row to start from. Use *LAST if all rows are not needed.
IMPORTANT: the start number starts at 1, so will be numerically one smaller than the actual row in the spreadsheet because row 1 is used for the header row.
The sheetname is the name of the sheet in the file. If sheetname contains blanks you must surround it with quotes.
The loop must end with a *DBEND command.
Cell references with loopindex can only be used within the loop.
Any number of database loops can be used on an open file.
The *SLE cannot be used with database loops.
Examples of *DBLOOP commands:
[*dbopen dfNameFile=’File of Names.xls’]
[*dbloop dlList=dfNameFile;Names]
[dlList.Surname] ! puts out a line with the surname for every row in the Names sheet
[*dbend dlList]
[*dbloop dlThis=(5)dfFile;Sheet1]
[*dbloop dlThat=(FirstRowWanted-1)dfFile;Sheet1]
This command finishes a loop started with a *DBLOOP command. The form of the command is:
[*DBEND dlindex]
The database index name loopindex was used in the *DBLOOP command.
Example of *DBEND command:
[*dbend dlList]
Commands and substitutions are allowed in database cell references unless *NOPPINDB is used.
To access data the sheet is first opened with both the *DBOPEN and *DBLOOP commands.
A database cell reference takes the form:
[dlindex.columnname]
where the dlindex is the index set in the *DBLOOP command and columnname is the identifier of the column in the first row of the spreadsheet. The case of columnname is not significant so these are all the same:
[dlNames.TITLE] [dlNames.Title] [dlNames.title]
You can check whether a column exists in a spreadsheet with the *DBCOLUMN command.
Cells can only be accessed between the *DBLOOP and *DBEND commands. Each time through the loop a new row is accessed.
Cell references without the enclosing square brackets can only be used where stated in the *SET, *SKIP and *BLOCK command descriptions.
Where a cell reference is used by itself (as a substitution) the text from the cell is output.
IMPORTANT: if the cell contains a value, then the character representation may not be as expected. For example, a cell containing the number 23 will be output as 23.0. To avoid this problem, set an index to the cell and use the index instead.
Example of database cell references:
[*set WhichRow = 1]
[*dbopen dfMyFile=MyFile.xls]
[*dbloop dlNameList=dfMyFile;names]
dc $name[WhichRow]=[dlNameList.surname],
[*set Age=[dlNameList.age]
di $age[WhichRow]=[Age],
[+WhichRow]
[*dbend dlNameList]
[*dbclose dfMyFile]
The above code opens Myfile.xls and sets the name and age from columns headed "surname" and "age" in the sheet called "names".
[*dbopen dfMyFile=MyFile.xls]
[*dbloop dlNameList=dfMyFile;names]
[*skip 99 empty dlNameList.surname]
! Name=[dlNameList.surname],[*block 99 string dlNameList.title.eq.'manager']Boss,[*99]
[*dbend dlNameList]
[*dbclose dfMyFile]
This code opens Myfile.xls and lists all rows in the sheet called "names" skipping any rows where the column headed "surname" is empty. "Boss" is output if the column headed "title" contains the word "manager".
This command checks whether a column exists in a sheet. The form of the *DBCOLUMN command is:
[*DBCOLUMN index = dfindex;sheetname;columnname]
The index name dfindex was used in the *DBOPEN command. The file must be open when *DBCOLUMN is used.
The sheetname is the name of the sheet in the file. If sheetname contains blanks, you must surround it with quotes.
The index is set to 1 if the column exists in the named worksheet or 0 if the column is not found.
Any number of *DBCOLUMN commands can be used on an open file.
Examples of *DBCOLUMN commands:
[*dbopen dfNameFile=FileOfNames.xls]
[*dbcolumn HasAddress=dfNameFile;Names;Address]
[*dbcolumn HasPostcode=dfNameFile;Names;Postcode]
[*set HasEither=HasAddress+HasPostcode]
[*dbloop dlList=dfNameFile;Names]
[*block 99 on HasEither.gt.0]
[dlList.Name] has address or postcode
[*99]
[*dbend dlList]
[*dbclose dfNameFile]
This searches for a particular text or value in a named worksheet column and returns the first row number that contains the target.
IMPORTANT: the row number returned starts at 1, so will be numerically one smaller than the actual row in the spreadsheet because row 1 is used for the header row that identifies the columns.
The command is one of the following:
[*DBFINDROW index = fileindex;sheetname;columnname='text’]
[*DBFINDROW index = fileindex;sheetname;columnname=index]
[*DBFINDROW index = fileindex;sheetname;columnname=value]
The first two above compare text, the last looks for the value.
Examples of *DBFINDROW:
[*dbfindrow RejectRow = dfFile;Sheet1;Value=’Reject’]
[*set ThisName=’[dsNames.lpName]’][*dbfindrow NameRow = dfFile;’Details’;’Full Name’=ThisName]
[*dbfindrow RowGot = dfFile;’Question details’;’Response’=13]
This sets an index to the number of rows in a sheet not including the header row.
The command is as follows:
[*DBLINES index= dfindex;sheetname]
See notes above for *DBFINDROW.