2.8 ⁑Fetching Data From a Given Row

It may be that you don't want to iterate through the entire data but just want to fetch information from a particular row. The datatool package provides a number of ways to do this, but this book is just going to cover three commands:

\DTLassign {⟨db-name⟩}{⟨row-idx⟩}{⟨assign list⟩}

This applies the assignment list to the row given by ⟨row-idx⟩. (Indices start from 1.)

\DTLassignfirstmatch {⟨db-name⟩}{⟨col-label⟩}{⟨value⟩}{⟨assign list⟩}

This applies the assignment list to the first row where the entry in the column identified by ⟨col-label⟩ exactly matches the given value. Note that no expansion is performed on ⟨value⟩.

\xDTLassignfirstmatch {⟨db-name⟩}{⟨col-label⟩}{⟨value⟩}{⟨assign list⟩}

This applies the assignment list to the first row where the entry in the column identified by ⟨col-label⟩ exactly matches a one-level expansion of ⟨value⟩.

In each case, ⟨db-name⟩ is the label identifying the data and ⟨assign list⟩ is the comma-separated list of assignments, as used by \DTLforeach and \DTLforeach*.

Example 8. Fetching the Data From Row 1

Suppose I just want information from the first row of data in my sample people.csv file. Then I can use \DTLassign, like this:

\DTLassign{people}{1}{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode%
}

Remember to make sure you comment out the unwanted EOL characters, as shown above, or you'll get an error caused by spurious spaces (recall the note about spaces earlier). Now that the data has been fetched, it can be used. For example, to just display the details in a tabular environment:

\begin{tabular}{l}
\Title\␣\Surname\\
\AddressI\\
\AddressII\\
\Town\\
\County\\
\Postcode
\end{tabular}

This produces:

(You can download or view a complete document.)

Note that there is a blank entry caused by missing data in the address2 column. If this example was changed to use the people SQL table instead, the result would appear as:

See §2.9 Null and Boolean Values on how to deal with null or empty entries. (You can also download or view a complete document for the SQL version.)

Remember that if you're importing your data from a SQL database, there's no need to import all the data from the table if you don't require parts of it. Instead you can filter out all the unwanted rows in your SELECT statement. For example, if you wanted to fetch the data for just the customer whose surname is “Parrot”, you can do:

$ datatooltk --output customer.dbtex --sqldb samples --sqluser sampleuser --sql "SELECT * FROM people WHERE surname='Parrot'"

If you're not using SQL then you can fetch the relevant row using the afore mentioned \DTLassignfirstmatch, but it's less efficient.

Example 9. Fetching a Customer's Details

Suppose you only want the details from the customer whose surname matches “Parrot” in the sample people.csv file. This can be fetched using:

\DTLassignfirstmatch{people}{surname}{Parrot}{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode%
}

Now the details have been fetched, it can be used as in the previous example:

\begin{tabular}{l}
\Title\␣\Surname\\
\AddressI\\
\AddressII\\
\Town\\
\County\\
\Postcode
\end{tabular}

The result is the same as for the previous example. (You can download or view a complete document.)

Remember that \DTLassignfirstmatch performs an exact match without expansion. This means that if you do something like:

\newcommand{\Name}{Parrot}
✘\DTLassignfirstmatch{people}{surname}{\Name}%
{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode%
}

Then you'll get an error that no match was found. This is because you're effectively asking TeX to find an entry that contains “\Name”, but that control sequence doesn't appear in any of the entries, so there's no match. Instead, you need to use \xDTLassignfirstmatch which will internally replace \Name with its definition (“Parrot”).

\newcommand{\Name}{Parrot}
✔\xDTLassignfirstmatch{people}{surname}{\Name}%
{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \AddressII=address2,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode%
}

Example 10. Fetching a Customer's Details (With Expansion)

In Example 8, I didn't access the country from the data. Let's modify that example so that it fetches the complete address for “Polly Parrot”:

\DTLassignfirstmatch{people}{surname}{Parrot}{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryCode=country%
}

Now let's try displaying the information:

\begin{tabular}{l}
\Title\␣\Surname\\
\AddressI\\
\Town\\
\County\\
\Postcode\\
\CountyCode
\end{tabular}

This produces:

If this is intended for, say, a letter to a customer, then the country code really needs to be converted to the country's name. That information is stored in the sample country-codes.csv file, so that also needs to be loaded. Therefore the document should have:

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

Once the \CountryCode has been assigned via \DTLassign, as shown above, the code can be converted to a name:

\xDTLassignfirstmatch{countries}{code}%
{\CountryCode}{\CountryName=name}

Now the address can be displayed including the country name:

You can download or view this example.

Remember that if you're using SQL, it's much simpler to combine and filter using the SELECT statement:

$ datatooltk --output customer.dbtex --sqldb samples --sqluser sampleuser --sql "SELECT title, surname, address1, address2, town, county, countries.name AS country, postcode FROM people, countries WHERE surname='Parrot' AND people.country = countries.code"

This creates a datatool (.dbtex) file called customer.dbtex that only contains the one row of data. The country name is now stored in the column labelled country. So you can just do:

\DTLloaddbtex{customer}{customer.dbtex}
\DTLassign{customer}{1}{%
  \Surname=surname,%
  \Title=title,%
  \AddressI=address1,%
  \Town=town,%
  \County=county,%
  \Postcode=postcode,%
  \CountryName=country%
}
\begin{tabular}{l}
\Title\␣\Surname\\
\AddressI\\
\Town\\
\County\\
\Postcode\\
\CountyName
\end{tabular}

This produces the sample result as above. You can download or view this SQL version.

Exercise 6. Fetching a Row of Data

Modify the code from Example 10 so that it fetches the address for Fred Canary from sample people.csv file.

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).