2.2.2 ⁑Loading Data From a .dbtex File

2.2.2 ⁑Loading Data From a `.dbtex` File

A datatool (.dbtex) file is a LaTeX file with definitions and assignments of macros and registers used to represent an internal database. Although this is a plain text file, it's very difficult to read and edit. However it's the fastest way of loading data via datatool. The datatooltk application can read and write this file, but it can also import data from CSV file s, Excel .xls file s, Open Document .ods spreadsheet s or MySQL databases. (You can't export back to those formats.) It can be run either in batch mode from a command prompt (see Volume 1) or as a graphical user interface.

It's simple to load a .dbtex file into a document that uses the datatool package. Just use:

\input {⟨filename⟩}

where ⟨filename⟩ is the name of the file including the .dbtex extension. (This must come after \usepackage{datatool}.) If you can't remember the label you assigned to the data, you can reference it using:

\dtllastloadeddb

However, a more convenient approach is to use:

\DTLloaddbtex {⟨cs⟩}{⟨filename⟩}

This inputs ⟨filename⟩ and makes the control sequence ⟨cs⟩ have the same value as \dtllastloadeddb. For example

\DTLloaddbtex{\people}{people.dbtex}

works like:

\input{people.dbtex}
\let\people\dtllastloadeddb

but it checks for the existence of the file people.dbtex and checks that the new command (\people) isn't already defined. Using \DTLloaddbtex is therefore safer than just using \input and \let. When you use commands like \DTLdisplaydb (§2.6 Displaying Tabulated Data) and \DTLforeach (§2.7.1 Iterating Through a Database) you can now reference the data using your new command (\people in this example).

The column title can be changed using:

\DTLsetheader {⟨db-name⟩}{⟨col-label⟩}{⟨title⟩}

where ⟨db-name⟩ is the label identifying the data (\people in the above example), ⟨col-label⟩ is the label identifying the required column and ⟨title⟩ is the new column title.

Example 1. Convert a .csv File to a .dbtex File

⚠

The default CSV escape character is a backslash, which means that if your data contains any control sequences the backslash will need to be doubled. For example, \\pounds instead of \pounds. If this causes a problem, you can change the CSV separator to a different character using datatooltk's application settings. (This doesn't apply to any of the sample files provided with this book as none of them contain any LaTeX commands.)

In the previous section I mentioned that \DTLloaddb and \DTLloadrawdb can't load CSV file s where there is an EOL within a cell, but datatooltk can. Let's suppose the file books-multiline.csv contains the following:

Title,Price
"Duck and Goose's 
Adventures",10.00
"The Return of 
Duck and Goose",11.00

This can be converted into a datatool (.dbtex) file using one of the following methods:

Run datatooltk in batch mode from a command prompt:

$ datatooltk --csv books-multiline.csv --output books.dbtex

The database label by default is taken from the CSV filename. If you want to change it you can use the --name ⟨db-name⟩ option:

$ datatooltk --name products2014 --csv books-multiline.csv --output books.dbtex

If you want to convert special characters to commands (using the mapping given in Table 2.1) you need to use the --map-tex-specials option:

$ datatooltk --name products2014 --csv books-multiline.csv --output books.dbtex --map-tex-specials
Run datatooltk in graphical user interface (GUI) mode. If you have installed datatooltk on Windows there should be an entry in your Start menu that will do this. Otherwise you can run the command datatooltk-gui. This should display the window shown in Figure 2.1.

Figure 2.1: datatooltk in Graphical Mode

If your CSV file contains special characters that you want converted to LaTeX commands, you need to switch on the map TeX characters via the “TeX” tab in the “Preferences” dialog. (This can be opened using the menu item Edit→Edit Preferences.)
Next use the File→Import→Import CSV menu item. This will open the dialog box shown in Figure 2.2. Select the CSV file and click on “Import”. The data should now be visible in the main window as shown in Figure 2.3.

Figure 2.2: Import CSV File

Figure 2.3: Imported CSV Data Shown in Main Window

The tab above the data shows the database label (the same as the first mandatory argument of \DTLloaddb and \DTLloadrawdb). If you want to change the default, you can double-click on the tab which will open a dialog box where you can type in a new label. You can now save the data to a datatool (.dbtex) file using the File→Save As menu item.

If you want to import an Excel .xls file to a datatool (.dbtex) file , you can use the --xls and --sheet options to datatooltk. The argument of --xls is the name of the .xls file and the argument of --sheet is either an integer (starting from 0) indicating the sheet index or a string identifying the sheet label. Note that no formatting information is read from the Excel file. Any font changes or alignments should be made in your LaTeX document. Similarly, you can import an Open Document .ods spreadsheet using the --ods and --sheet options.

Example 2. Convert an .xls Sheet to a .dbtex File

Suppose you have an Excel file called, say, shop.xls and it contains two sheets: “products” and “customers”. You can convert, say, the “customers” sheet to a file called customers.dbtex using one of the following methods:

Run datatooltk in batch mode from a command prompt:

$ datatooltk --output customers.dbtex --xls shop.xls --sheet customers
Or:

$ datatooltk --output customers.dbtex --xls shop.xls --sheet 1
(The customer data is in the second sheet, but indices start from 0 so it's sheet 1.)
Run datatooltk in GUI mode using datatooltk-gui, as described above, and set the map TeX characters option and header row settings if required.
Next use the File→Import→ Import Spreadsheet menu item. This will open a file selector dialog box. Select the .xls file (shop.xls in this example) and click on the “Import” button. Another dialog box will appear in which you need to select the required sheet name (“customers” in this example).
Click on “Okay” and the data will be displayed in the main window. You can then save the data to a .dbtex file using the File→Save As menu item.

Note that formatting information isn't fetched from the spreadsheet. You will have to add any necessary font changing commands when you display the data. This helps to provide a consistent style throughout the document.

You can also use datatooltk to import data from a MySQL database. This is slightly more complicated as you need to tell datatooltk the database, the SQL SELECT statement⁵, the user name and password. (You may also need to change the SQL port and host, if different from the defaults.)

Example 3. Importing SQL Data to a .dbtex File

Suppose you have a database called samples and in that database you have a table called books and let's suppose the user name for the samples database is “sampleuser”. Then you can import the data into a datatool (.dbtex) file using one of the following methods:

Run datatooltk in batch mode from a command prompt:

$ datatooltk --output books.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM books"
This will prompt you for a password from the console. As before, you can use the --map-tex-specials option if required.
Run datatooltk in GUI mode using datatooltk-gui, as described above, and set the map TeX characters option if required.
Next use the File→Import→Import SQL menu item. This will open the “Import SQL” dialog box. Edit the SELECT statement as required and enter the database and user name in the appropriate fields. For example, as shown in Figure 2.4.

Figure 2.4: Import SQL Data

Then click on “Okay” and enter the password when prompted.
The data should now be visible in the main window as illustrated in Figure 2.5.

Figure 2.5: Imported SQL Data Shown in Main Window
If you want to change the column header details, double-click on the button at the top of the required column. This will display the dialog box shown in Figure 2.6.

Figure 2.6: Changing the Column Header Text
Once the data has been fetched, you can save it using File→Save As.

Remember that you can use the SELECT statement to filter unwanted rows, sort data or join the data with data from other tables.

If you use arara to build your document, you can use the datatooltk directive, but you must make sure you have at least v4.0 of arara installed.

Examples Using arara

Fetch the data from a CSV file called booklist.csv:

% arara: datatooltk: {
% arara: --> output: books.dbtex,
% arara: --> csv: booklist.csv }
% arara: pdflatex
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\books}{books.dbtex}

\begin{document}
% Do stuff with data.
\end{document}

Remember that you can also use conditionals to prevent unnecessary application calls. For example:

% arara: datatooltk: {
% arara: --> output: books.dbtex,
% arara: --> csv: booklist.csv }
% arara: --> if changed(toFile("booklist.csv"))
% arara: --> || missing(toFile("books.dbtex"))
% arara: pdflatex while changed("tex") 
% arara: --> || changed(toFile("books.dbtex"))
% arara: --> || missing("pdf")
% arara: --> || found("log", "Rerun")
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\books}{books.dbtex}

\begin{document}
% Do stuff with data.
\end{document}

Fetch the data from the sheet called “products” in an Excel .xls file called shop.xls:

% arara: datatooltk: {
% arara: --> output: products.dbtex,
% arara: --> xls: shop.xls,
% arara: --> sheet: products }
% arara: pdflatex
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\products}{products.dbtex}

\begin{document}
% Do stuff with data.
\end{document}

Fetch all the data from the table called books in a MySQL database called samples:

% arara: datatooltk: {
% arara: --> output: books.dbtex,
% arara: --> sqldb: samples,
% arara: --> sqluser: sampleuser,
% arara: --> sql: "SELECT * FROM books" }
% arara: pdflatex
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\books}{books.dbtex}

\begin{document}
% Do stuff with data.
\end{document}

In this case, datatooltk will prompt you for the MySQL password associated with the specified user name. There's a slight difference between running datatooltk directly from a terminal and running it via arara. The terminal usually provides a console for datatooltk to use to prompt for a password, but with arara there's no console so you'll get a dialog box instead (even if you haven't specified the --verbose option when you call arara). See Figure 2.7. This will also happen if you invoke datatooltk from another application, such as TeXworks.

⚠

If you're thinking of using a conditional in the arara directive, there's no platform-independent way of determining if the database has been modified. The way the data is stored on the hard disk varies not only according to the operating system but also with the way the table was created (MyISAM, InnoDB, etc). For example, on my Linux computer I could test if the file /var/lib/mysql/samples/books.MYD has changed, but I would need to run arara with sudo in order to access the file, which is unwise.

**Figure 2.7:** Running `datatooltk` via `arara` uses a dialog box to prompt for a MySQL password instead of using a console.

Footnotes

... statement ⁵: SQL statements are beyond the scope of this guide, but for further details I recommend you read “Managing & Using MySQL” [71].

⇦

⇧

⇨

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