2.7.1 ⁑Iterating Through a Database

The datatool package provides ways of iterating through a database and performing a task on each row of data. The two main commands are:

\DTLforeach [⟨condition⟩]{⟨db-name⟩}{⟨assign⟩}{⟨body⟩}

and its starred version:

\DTLforeach*[⟨condition⟩]{⟨db-name⟩}{⟨assign⟩}{⟨body⟩}

The unstarred version allows you to modify the data stored internally (that is, in TeX's registers used by datatool, not in the original loaded or imported source). As it's more efficient to do any modifications in your spreadsheet or via SQL these datatool commands aren't covered here. Instead, all the examples in this document will use the read-only starred version, which compiles faster. The parameters for both versions are as follows:

⟨db-name⟩

The label identifying the internal database.

⟨assign⟩

A comma-separated list of ⟨cs⟩=⟨col-label⟩ assignments where ⟨cs⟩ is a control sequence that can be used as a placeholder in ⟨body⟩ and ⟨col-label⟩ is the label identifying the required column. Spaces aren't ignored in this list (except after ⟨cs⟩ as per TeX's normal behaviour). There is no check for the existence of ⟨cs⟩ so be careful you don't accidentally overwrite an existing command. You only need to assign control sequences to the columns whose values you intend to use in ⟨body⟩. Assignments are performed with the \global prefix to ensure that \DTLforeach works correctly within a tabular (or similar) environment.

⟨body⟩

The code to do for each row of data where the condition given in the optional argument is true.

⟨condition⟩

This optional argument should be a conditional that follows the same syntax as the \ifthenelse command defined in the ifthen package [10]. For example, you can use:

\equal {⟨text 1⟩}{⟨text 2⟩}

to test if ⟨text 1⟩ is equal to ⟨text 2⟩.

The ⟨body⟩ is only applied to those rows where the condition is met. The default is \boolean{true}. If you're importing data from a SQL database, then it's better to apply any filtering in the SELECT statement.

You can prematurely terminate the list at the end of the current iteration by placing

\dtlbreak

anywhere within ⟨body⟩.

For example, to simply print each surname in the people data:

\DTLforeach*{people}{\Surname=surname}{\Surname. }

Using the sample people.csv file, this produces:

To just print the forenames of the people whose surname is “Canary”:

\DTLforeach*
 [\equal{\Surname}{Canary}]% condition
 {people}% database
 {\Surname=surname,\Forenames=forenames}% assignments
 {\Forenames. }% body

which produces:

Example 6. Iterating Through Data

Recall from Example 5 that data imported from an Excel .xls file doesn't include any of the formatting used by the spreadsheet, so Table 2.3 (produced using \DTLdisplaydb) didn't display the numerical data as currency. Instead of using \DTLdisplaydb we can use \DTLforeach* to display the table and use \dtlround (described in §2.1.3 Arithmetic) to round the values to two decimal places.

\begin{table}
 \caption{Formatted data imported from \texttt{shop.xls}}
 \label{tab:xlsproducts2}
 \centering
 \begin{tabular}{lrr}
 \multicolumn{1}{c}{\bfseries Product} &
 \multicolumn{1}{c}{\bfseries Price (ex VAT)} &
 \multicolumn{1}{c}{\bfseries Price (inc VAT)}%
 \DTLforeach*{xlsproducts}%
 {%
   \Product=Product,%
   \exPrice=Price (ex VAT),%
   \incPrice=Price (inc VAT)%
 }%
 {%
    \\\Product &
    \dtlround{\exPrice}{\exPrice}{2}\pounds\exPrice &
    \dtlround{\incPrice}{\incPrice}{2}\pounds\incPrice
 }%
 \end{tabular}
\end{table}

which produces Table 2.4. Note that the new row command \\ is put at the start of ⟨body⟩ to ensure a new line starts after the header entries. It's usually best to put \\ at the start of ⟨body⟩ as it may cause a problem if it's placed later in that argument. You can download or view the complete document.

Exercise 2. Iterating Through Data

Create a document that loads the sample people.csv file (or the people SQL table) and displays the three tables shown in Tables 2.5, 2.6 and 2.7 using \DTLforeach*. You can download or view the solution to this exercise.

Table 2.5: Hardback Books

Table 2.6: Paperback Books

Table 2.7: Ebooks

⇦

⇧

⇨

This book is also available as A4 PDF or 12.8cm x 9.6cm PDF or paperback (ISBN 978-1-909440-07-4).