About
Shop
LaTeX
Software
Books
Gallery
News
Contact
Blog
Settings
Latest news 2019-11-04: The giveaway of two signed copies of “Quack, quack, quack. Give my hat back!” has closed and the winning entrants have been selected. Thank you to everyone who took part.


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
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:

Parrot. Canary. Zebra. Arara. Duck. Canary.

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:

Mabel. Fred.

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.


Table 2.4: Formatted Data Imported From shop.xls
Product Price (ex VAT) Price (inc VAT)
Ink cartridge £25.00 £30.00
Mouse mat £12.00 £14.40
USB stick £15.00 £18.00
Pen £2.50 £3.00
End of Image.



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
Id Author Title
5 Sir Quackalot The Return of Duck and Goose
6 Sir Quackalot The Adventures of Duck and Goose
10 Bor Ing ‘Duck and Goose’: an allegory for modern times?
End of Image.




Table 2.6: Paperback Books
Id Author Title
1 Sir Quackalot The Adventures of Duck and Goose
2 Sir Quackalot The Return of Duck and Goose
3 Sir Quackalot More Fun with Duck and Goose
4 Sir Quackalot Duck and Goose on Holiday
7 A. Parrot My Friend is a Duck
End of Image.




Table 2.7: Ebooks
Id Author Title
8 Prof Macaw Annotated Notes on the ‘Duck and Goose’ chronicles
9 Polly Parrot ‘Duck and Goose’ Cheat Sheet for Students
End of Image.




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

© 2015 Dickimaw Books. "Dickimaw", "Dickimaw Books" and the Dickimaw parrot logo are trademarks. The Dickimaw parrot was painted by Magdalene Pritchett.

Terms of Use Privacy Policy Cookies Site Map FAQs