16

I'm confused about how to use "datatool" to read variables from a row in my database, and pass them on to be formatted in my text. I don't want to make a table, but that's about all there seems to be examples of on the Internet.

I have a CSV file made up something like this:

name|points
John|172
Sue|120
Mike|64

After two days of staring at the datatool manual, this is the extent of my success:

\documentclass{memoir}
\usepackage{datatool}
\DTLsetseparator{|}
\begin{document}
\DTLloaddb{players}{database.csv}
\DTLforeach*{players}{\name=name,\points=points}
\subsection{\name}
\textit{\points}
\end{document}

That prints the chosen variables with the formatting I desire, but it only does so with the last entry in the database. My guess is that "\dtlgetrowforvalue{players}{1}{John}" could be used to find John's row in the database, but I don't understand how to integrate that with the commands that read and display the variables.

I am unfamiliar with much of the terminology used in the manual, and finding no examples that look like what I wish to achieve, my attempts at bullying my way through by trial and error have failed. I don't necessarily need a working example, but I would very much like for someone here to put me onto the appropriate commands, at least.

  • Welcome to TeX.SX! You can have a look at our starter guide to familiarize yourself further with our format. – yo' May 18 '14 at 16:29
  • Try to delete the star *. – Sigur May 18 '14 at 16:35
  • Sigur, the * is there to make the database access read-only -- unless I misunderstood the documentation. – user67287 May 18 '14 at 17:21
  • @user67287, you understand it correctly: "The database may be edited in the unstarred version, in the starred version the database is read only". Page 155 of piotrkosoft.net/pub/mirrors/CTAN/macros/latex/contrib/datatool/… – belford Jan 14 '17 at 19:23
16

\DTLforeach takes three compulsory arguments, not two as in your example. This fixes your example:

\documentclass{memoir}

\usepackage{datatool}
\DTLsetseparator{|}

\DTLloaddb{players}{database.csv}

\begin{document}

\DTLforeach*
{players}% database label
{\name=name,\points=points}% assignment
{% Stuff to do at each iteration:
  \subsection{\name}
  \textit{\points}
}

\end{document}

This produces:

Image of result

Edit:

\DTLforeach is a repetitive (loop) command designed to perform a particular action for each row of data. If you just want to lookup information from a single row of the data, there are a number of methods to do this. Here are some examples:

\documentclass{memoir}

\usepackage{datatool}
\DTLsetseparator{|}

\DTLloaddb{players}{database.csv}

\begin{document}

Sue's points: \DTLfetch{players}{name}{Sue}{points}.

Pull information from row 3.
\DTLassign{players}{3}{\name=name,\points=points}

Here's the information: Name: \name. Points: \points.

Pull information from the first row where the name column has the
value ``John''.
\DTLassignfirstmatch{players}{name}{John}{\name=name,\points=points}

Here's the information: Name (we already know this anyway): \name.
Points: \points.

\newcommand{\PlayerName}{John}
As above, but the required name (\PlayerName) is given by a command:
\xDTLassignfirstmatch{players}{name}{\PlayerName}{\name=name,\points=points}

Here's the information: Name (we already know this anyway): \name.
Points: \points.

\end{document}

This produces:

Image of result

Sue’s points: 120.
Pull information from row 3.
Here’s the information: Name: Mike. Points: 64.
Pull information from the first row where the name column has the value “John”.
Here’s the information: Name (we already know this anyway): John. Points: 172.
As above, but the required name (John) is given by a command:
Here’s the information: Name (we already know this anyway): John. Points: 172.

  • I don't know how I missed that! I'll have to try that method again. (However, the subsection and textit parts weren't intended to be used at each iteration, but rather in the document generally, in order to display the variables found by datatool.) – user67287 Jun 1 '14 at 5:46
  • I struggled a while with that, so: \DTLassign{players}{3}{\name=name,\points=points} does not take row 3 of the file but rather the third row after the header – normally this is row 4 in the file! – dessert Nov 4 '17 at 19:22
  • @dessert The row number always refers to the row index in the internal data set. Not all databases are created by reading information from a csv file. Some are created using commands like \DTLnewdb or by loading a .dbtex file created by datatooltk or a csv file may have no header row or omitlines may be used to skip initial non-data rows, so file line numbers are an unreliable reference and sometimes non-existent. – Nicola Talbot Nov 4 '17 at 22:10
  • @NicolaTalbot That's understandable and somewhat trivial, but nevertheless I would have expected the User Manual to explain what's meant with row index. I don't think I'm the only one counting at least the header in – common spreadsheet software teaches it this way. – dessert Nov 4 '17 at 22:50
6

NOVEMBER 2016 SOLUTION (does not use datatool):

This solution uses my recently updated readarray package that now uses the powerful listofitems package for parsing and accessing the data.

\documentclass{article}
\usepackage{filecontents}
\begin{filecontents*}{database.csv}
name,points,d3,d4,d5,d6,d7,d8,d9,d10,d11
John,172,yes,black,23,0.000,89,big,32,A,Z
Sue,120,no,red,26,0.002,97,medium,36,B,W
Mike,64,maybe,green,89,0.567,154,small,42,C,X 
\end{filecontents*}
\usepackage{readarray}[2016-11-07]
\usepackage{ifthen}
\newcounter{recordindex}
\newcommand\findrecord[1]{%
  \def\matchrecord{0}
  \setcounter{recordindex}{0}%
  \whiledo{\therecordindex<\datacellROWS}{%
    \stepcounter{recordindex}%
    \ifthenelse{\equal{#1}%
      {\datacell[\therecordindex,1]}}
    {\xdef\matchrecord{\therecordindex}\setcounter{recordindex}{\datacellROWS}}%
    {}%
  }%
}
\begin{document}
\readarraysepchar{,}
\readdef{database.csv}{\mydata}
\readarray\mydata\datacell[-,\ncols]

The whole array is\arraydump\datacell
\datacellROWS\ records total\par
\datacell[3,6] is the 6th cell of the 3rd row\par
\datacell[4,11] is the 11th cell of the 4th row\par
\findrecord{Sue}
\datacell[\matchrecord,4] is Sue's 4th data cell
\end{document}

enter image description here

I am removing my earlier solutions because they relied on use of internal code from my tabstackengine and readarray packages, which has changed in recent package updates.

  • Steven, I will look into the method you describe. If my example of "\subsection{\name}\textit{\points}" wasn't sufficient explanation of what I want, let me clarify: I wish to be able to select a CSV row by the "name" it starts with, put the row's data ("John", "172") into commands ("\name", "\points"), and then use those commands in some unique text describing John. Then I wish to do the same for some other name in the database, reading that row's data into the commands, and applying the commands in some more unique text. – user67287 May 18 '14 at 17:35
  • Further clarification: Each CSV line has over a dozen more variables beyond "name" and "points". I only mentioned those two in order to keep things simple. My goal is to be able to extract any or all of those variables and put each into its own command, which can in turn be used to enter the data into text I write about the person named in the database entry. I've already written a rather large "\DTLforeach", creating a command for each variable I want to use. I just can't figure out how to tell it to specify which row I want to extract the variables from. – user67287 May 18 '14 at 17:40
  • @user67287 Please see revision. – Steven B. Segletes May 18 '14 at 21:37
1

Mostly by accident, I found a solution. The way I got it to work seems a bit long-winded, though, so I think there's probably a better way of doing it that I simply haven't figured out.

database.csv...

name,points,other
John,172,yes
Sue,120,no
Mike,64,maybe

document.tex...

\documentclass{memoir}
\usepackage{datatool}
\DTLloaddb{players}{database.csv}
\newcommand{\player}[1]{%
    \dtlgetrowforvalue{players}{\dtlcolumnindex{players}{name}}{#1}%
    \input{./template.tex}%
}
\begin{document}
\player{Sue}
\end{document}

template.tex...

% read variables into commands
\dtlgetentryfromcurrentrow{\name}{1}
\dtlgetentryfromcurrentrow{\points}{2}
\dtlgetentryfromcurrentrow{\other}{3}

% use commands in text
\subsubsection{\name}
\textit{\points}
\other
  • It seems that this method makes it impossible to use commands like "\par" and "\footnote" in the variables. If anyone can explain why this is, or perhaps offer a better solution, that would be great. – user67287 May 20 '14 at 19:37
  • Using "\DTLpar" in place of "\par" in the database solves one problem. Using "\protect" before "\footnote" in the database solves the other. At least it seems to, so far. – user67287 Jun 1 '14 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.