2.6 ⁑Displaying Tabulated Data

Once you have loaded your data you can display it using:

\DTLdisplaydb [⟨omit list⟩]{⟨db-name⟩}

where ⟨db-name⟩ is the database label. The optional argument ⟨omit list⟩ is a comma-separated list of labels indicating which columns you want omitted from the tabular environment. Make sure you don't have any unwanted spaces in ⟨omit list⟩.

The \DTLdisplaydb command uses a tabular environment internally and should typically go inside a table environment. If the data is too big to fit on one page, you can instead use:

\DTLdisplaylongdb [⟨options⟩]{⟨db-name⟩}

This uses the longtable environment defined by the longtable package [11] instead of the tabular environment (see §4.3 Building Your Own Invoice using longtable and datatool). As with \DTLdisplaydb, ⟨db-name⟩ indicates the label identifying the data. Note that \DTLdisplaylongdb should not be put inside a table environment.

The optional argument ⟨options⟩ is a key=value list. The following keys are available:

caption: The caption for the longtable.
contcaption: The continuation caption for the longtable.
shortcaption: The caption to be used in the list of tables.
label: The label for this table. (To be used in \ref{⟨label⟩}, if required.)
omit: Comma-separated list of labels identifying columns to be omitted.
foot: The longtable's foot.
lastfoot: The foot for the last page of the longtable.

Remember to load the longtable package if you want to use \DTLdisplaylongdb.

Example 4. Display Product List

Suppose I want to display the data from my sample booklist.csv file in a table environment, but I don't want to include the id or author columns:

% Load "booklist.csv":
\DTLloaddb
 [headers={Id,Title,Author,Format,Price (\pounds)}]% column headers
 {books}{booklist.csv}

\begin{table}[htbp]
\caption{Products}
\label{tab:products}
\centering
\DTLdisplaydb[id,author]{books}
\end{table}

This produces Table 2.2.

Table 2.2: Products

Suppose instead I want to use the equivalent books SQL table. If I'm not interested in using the id or format columns at all in the document, I can exclude them when I'm importing the data. From the command line:

$ datatooltk --output books.dbtex --sqldb samples --sqluser sampleuser --sql "SELECT title, author, price FROM books"

or using an arara directive:

% arara: datatooltk: {
% arara: --> output: books.dbtex,
% arara: --> sqldb: samples, 
% arara: --> sqluser: sampleuser,
% arara: --> sql: "SELECT title, author, price FROM books" }

Once the file books.dbtex has been created I can load it into my document using:

\DTLloaddbtex{\books}{books.dbtex}

and set the headers using:

\DTLsetheader{\books}{title}{Title}
\DTLsetheader{\books}{author}{Author}
\DTLsetheader{\books}{format}{Format}
\DTLsetheader{\books}{price}{Price (\pounds)}

(Alternatively, if I import the SQL data using datatooltk-gui I can set the header text by double-clicking on the column header as described in §2.2.2 Loading Data From a .dbtex File.)

The table can now be created in the same way as before. Putting it all together:

% Load "books.dbtex":
\DTLloaddbtex{\books}{books.dbtex}

% Set the headers:
\DTLsetheader{\books}{title}{Title}
\DTLsetheader{\books}{author}{Author}
\DTLsetheader{\books}{format}{Format}
\DTLsetheader{\books}{price}{Price (\pounds)}

% Display the data:

\begin{table}[htbp]
\caption{Products}
\label{tab:products}
\centering
\DTLdisplaydb[id,author]{books}
\end{table}

This produces the same result shown in Table 2.2.

A longer database, such as the sample country-codes.csv file, would need \DTLdisplaylongdb, which requires the longtable package:

\usepackage{longtable}
\usepackage{datatool}

Now load the data:

\DTLloaddb{countries}{country-codes.csv}

Later in the document, display the data in a longtable environment:

\DTLdisplaylongdb
 [
   caption={A Sample Long Table},% main caption
   contcaption={A Sample Long Table (Continued)},% continuation
   label={tab:countries},% table label
   foot={\emph{Continued on next page}},% table foot
   lastfoot={}% final table foot
 ]
 {countries}

The table can then be referenced using:

\label{tab:countries}

You can download or view the full example document using CSV file s, or download or view the example using a SQL database.

The commands \DTLdisplaydb and \DTLdisplaylongdb are useful if you just want to list the data, but if you want to modify the displayed format (for example, if you want to swap columns or highlight a row) then you'll need to construct the contents of the tabular or longtable environments by iterating through the data using \DTLforeach, described in §2.7.1 Iterating Through a Database.

⚠

Take care if you have imported your data from an Excel .xls file as the formatting information isn't imported. This may cause an unexpected result when you use \DTLdisplaydb or \DTLdisplaylongdb. This is illustrated in the example below.

Example 5. Displaying Data Imported from a Spreadsheet

Recall the spreadsheet shown in Figure 2.8. Viewed in a spreadsheet application, the second and third columns are displayed as currency with two decimal places. Now suppose I import the data from the “products” sheet using datatooltk:

$ datatooltk --output shop-products.dbtex --xls shop.xls --sheet products

and load the resulting shop-products.dbtex file in my document:

\DTLloaddbtex{\xlsproducts}{shop-products.dbtex}

then

\begin{table}
  \caption{Data imported from \texttt{shop.xls}}
  \label{tab:xlsproducts}
  \centering
  \DTLdisplaydb{\xlsproducts}
\end{table}

produces Table 2.3. Note that the second and third columns are no longer displayed as currency nor are the numbers rounded to two decimal places. (See Example 6 for a different approach that rounds and formats the price columns.)

You can download or view this example document.

Exercise 1. Loading and Displaying Data

Try loading data from the sample booklist.csv file or from the books SQL table and displaying it in a table. Then try displaying the sample country-codes.csv file or countries SQL table data in a longtable.

For the More Adventurous:

The datatool package provides some hooks to allow you to make minor modifications to the default layout of \DTLdisplaydb and \DTLdisplaylongdb. These include the commands:

\dtldisplaystarttab

which is done at the start,

\dtldisplayendtab

which is done at the end and

\dtldisplayafterhead

which is done after the header row. In the case of \DTLdisplaylongdb, the hooks \dtldisplaystarttab and \dtldisplayafterhead are used before and after the header row on each page of the longtable, but \dtldisplayendtab is only used on the last page of the longtable. You will need to use the foot option if you want to specify code that should appear at the bottom of every page of the longtable.

These three commands default to nothing, but you can redefine them before you display the data. For example, recall from Volume 1 that the booktabs package [24] provides:

\toprule [⟨wd⟩]

for a top horizontal rule,

\bottomrule [⟨wd⟩]

for a bottom horizontal rule, and

\midrule [⟨wd⟩]

for a horizontal rule between rows. See if you can edit your example document to include these rules in the table displaying the list of products.

You can download or view the solution to this exercise.

⇦

⇧

⇨

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