|
<< Click to Display Table of Contents >> Navigation: Reference > Pre-processor > ODBC cell references (Excel and Access only) |
VERY IMPORTANT: All cells in a spreadsheet for use with CL must be formatted as text. The only safe way to do this is to format all cells in the sheet as text before entering any contents. Formatting as text after entering the contents is not always successful.
VERY IMPORTANT: PP commands and substitutions are allowed in ODBC cell references unless [*NOPPINDB] is used.
The Pre-processor allows you to access cells in a spreadsheet. Each cell may consist of up to 1000 characters of text. Later sections refer to a cell reference as cellref.
The sheet must have column names in row 1 to identify the columns being referred to.
To access ODBC data the sheet is first opened with both the *DBOPEN and *DBLOOP commands. Index names set in these commands are reserved for this purpose and cannot be used as ordinary indices elsewhere in the CL script. For this reason we recommend using DB as the first two characters of the index name in these commands.
A cell reference (cellref) takes the form "loopindex.columnname", where the loopindex 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:
[dlNames.TITLE]
[dlNames.Title]
[dlNames.title]
will all work regardless of the case used in the spreadsheet itself.
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 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 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 ODBC references:
[*dbopen dfMyFile=MyFile.xls]
[*dbloop dlNameList=dfMyFile;names]
dc $name=[dlNameList.surname],
[*set Age=[dlNameList.age]
di $age=[Age],
[*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 list all rows in the sheet called "names" skipping any rows where the column headed "surname" is empty. A second line (boss) is output if the column headed "title" contains the word "manager".