# Join databases with datatool

I'm trying to get a function that can take a variable amount of csv files as an argument, and then join them into a single database for Latex's datatool. The databases are all in a uniform format (same columns) and only need to append the rows.

My attempt was to make a recursive function inspired by this blog post.

\documentclass{article}

\makeatletter
\newcommand{\recursive}[1]{#1 \@ifnextchar\bgroup{\recursive}{}}
\makeatother

\begin{document}

\recursive{lorem ipsum}\par
\recursive{A}{B}{C}{1}\par
\recursive{1}{2}{3}{4}{5}{6}{7}

\end{document}


But I can't name the databases uniquely so I get an error. Maybe it would work in a way by loading the db in the argument <dbX>, add the content to a current main db <dbY> and clear <dbX> so the process can be repeated?

\documentclass{article}
\usepackage{datatool}
\DTLsetseparator{;}

%dbA
\begin{filecontents*}{databaseA.csv}
first;  second
A;      B
C;      D
E;      F
\end{filecontents*}

%dbB
\begin{filecontents*}{databaseB.csv}
first;  second
G;      H
I;      J
K;      L
M;      N
\end{filecontents*}

%dbC
\begin{filecontents*}{databaseC.csv}
first;  second
O;      P
\end{filecontents*}

\makeatletter
\makeatother

\recursive{databaseA.csv}{databaseB.csv}{databaseC.csv}


The second part of the challenge is how to append the rows to a/the database. There are more complicated examples of how to join additional columns and rows. But I was hoping there is a less complex command to just loop over all rows and append them in one line, not value by value, or even better just merge the two databases in one command.

• – Werner
Sep 5, 2021 at 20:55

In the manual of the packagage datatool just read about \DTLnewdbonloadtrue and \DTLnewdbonloadfalse:

By default, \DTLloaddb creates a new database called ⟨db name⟩ before it loads the data given in the file ⟨filename⟩. If you want to append the data, use
\DTLnewdbonloadfalse
before you use \DTLloaddb. You can reverse this using
\DTLnewdbonloadtrue

\documentclass{article}
\usepackage{datatool}
\DTLsetseparator{;}

%dbA
\begin{filecontents*}{databaseA.csv}
first;  second
A;      B
C;      D
E;      F
\end{filecontents*}

%dbB
\begin{filecontents*}{databaseB.csv}
first;  second
G;      H
I;      J
K;      L
M;      N
\end{filecontents*}

%dbC
\begin{filecontents*}{databaseC.csv}
first;  second
O;      P
\end{filecontents*}

\makeatletter
% #1 tokens to execute after \DTLloadrawdb
% #2 name of database
% #3 name of csv-file or end-marker for the loop.
}%
\makeatother

% Syntax of the mechanism:
%

\begin{document}

\DTLdisplaydb{db}

\end{document}


The above does

\DTLnewdbonloadtrue
...
\DTLnewdbonloadtrue

You can easily use \clist_map_inline:nn of expl3's l3clist-package for creating a macro where you can pass a comma-list of filenames.

\documentclass{article}
\usepackage{xparse}
\usepackage{datatool}
\DTLsetseparator{;}

%dbA
\begin{filecontents*}{databaseA.csv}
first;  second
A;      B
C;      D
E;      F
\end{filecontents*}

%dbB
\begin{filecontents*}{databaseB.csv}
first;  second
G;      H
I;      J
K;      L
M;      N
\end{filecontents*}

%dbC
\begin{filecontents*}{databaseC.csv}
first;  second
O;      P
\end{filecontents*}

\ExplSyntaxOn
\DTLnewdb{#1} % <- create the new empty database
\DTLnewdbonloadfalse % <- let's append to that database
\clist_map_inline:nn {#2} {\DTLloadrawdb{#1}{##1}} % <- have a sequence of calls \DTLloadrawdb
\DTLnewdbonloadtrue % <- switch back to \DTLload(raw)db not appending to an existing database but to creating databases anew.
}
\ExplSyntaxOff

\begin{document}

\DTLdisplaydb{db}

\end{document}


Explanation of the code:

Release 2021-08-27 of The LaTeX3 Interfaces says in section 22.5 Mapping over comma lists:

\clist_map_inline:Nn
\clist_map_inline:cn
\clist_map_inline:nn
Updated: 2012-06-29

\clist_map_inline:Nn ⟨comma list⟩ {⟨inline function⟩}

Applies ⟨inline function⟩ to every ⟨item⟩ stored within the ⟨comma list⟩. The ⟨inline function⟩ should consist of code which receives the ⟨item⟩ as #1. The ⟨items⟩ are returned from left to right.

The last sentence of this explanation seems a bit inaccurate to me, because what is returned is not only the items. What is returned are as many sequences of tokens as there are non-blank items in the ⟨comma list⟩. Each sequence of tokens consists of that assortment of tokens that is called ⟨inline function⟩, whereby in that assortment the sequence #1 is replaced by those tokens, of which the respective item consists.

After defining

\NewDocumentCommand\LoadDatabases{mm}{
\DTLnewdb{#1}
}


the sequence \LoadDatabase{db}{databaseA.csv, databaseB.csv, databaseC.csv}

yields:

  \DTLnewdb{db}


(During the expansion of \LoadDatabases the two consecutive hashes ## of ##1 collapse into a single hash#.)

\clist_map_inline:nn's ⟨comma list⟩ is: databaseA.csv, databaseB.csv, databaseC.csv \clist_map_inline:nn's ⟨inline function⟩ is: \DTLloadrawdb{db}{#1}
Within the ⟨inline function⟩ #1 denotes an item of the ⟨comma list⟩, i.e., the name of a .csv-file.

So \clist_map_inline:nn yields something like

\DTLloadrawdb{db}{databaseA.csv}%

• Thank you Ulrich, I was almost convinced I can count on you providing a great answer again. I am grateful for your support. Sep 8, 2021 at 18:22
• Can you explain what '##1' in the \clist_map_inline:nn version is? Is this the first argument that \clist_map_inline:nn receives? I didn't find any explanation for this. Sep 8, 2021 at 19:16
• @SharkyBamboozle I just edited my answer and added some explanation. ##1 is reduced to #1 during expansion of \LoadDatabases and denotes the set of tokens belonging to an item of the comma-list, i.e., denotes the name of a .csv-file. Sep 9, 2021 at 14:01

Here is how it can be done with the readarray package (v3.0). I read each of the files into it's own array, and then start merging the arrays in a way which eliminates the header line from all but the first array.

The final result is in \arrayjoined of size [9,2]. Individual elements may be accessed, for example, as \arrayjoined[5,2] to get row-5, column-2 data entry.

%dbA
\begin{filecontents*}{databaseA.csv}
first;  second
A;      B
C;      D
E;      F
\end{filecontents*}

%dbB
\begin{filecontents*}{databaseB.csv}
first;  second
G;      H
I;      J
K;      L
M;      N
\end{filecontents*}

%dbC
\begin{filecontents*}{databaseC.csv}
first;  second
O;      P
\end{filecontents*}

\documentclass{article}
\begin{document}

\initarray\arrayjoined[\the\numexpr
\arrayAROWS+\arrayBROWS+\arrayCROWS-2\relax,2]

\mergearray\arrayC\arrayjoined[\the\numexpr
\arrayAROWS+\arrayBROWS-1\relax,1]
\mergearray\arrayB\arrayjoined[\arrayAROWS,1]
\mergearray\arrayA\arrayjoined[1,1]

\typesetarray\arrayjoined
\end{document}


Perhaps the way below is even simpler, if one is allowed to fully expand the file contents...here I use a macro to join the three sets of file data while stripping unnecessary headers.

%dbA
\begin{filecontents*}{databaseA.csv}
first;  second
A;      B
C;      D
E;      F
\end{filecontents*}

%dbB
\begin{filecontents*}{databaseB.csv}
first;  second
G;      H
I;      J
K;      L
M;      N
\end{filecontents*}

%dbC
\begin{filecontents*}{databaseC.csv}
first;  second
O;      P
\end{filecontents*}

\documentclass{article}
\begin{document}

\typesetarray\arrayjoined
\end{document}


Note: if one wishes to "condition" the output of \typesetarray for use in tabular format, then one merely needs to condition it so, as

\renewcommand\typesetcolsepchar{&}
\renewcommand\typesetrowsepchar{\\}
\begin{tabular}{ll}
\typesetarray\arrayjoined
\end{tabular}


• Thank you for your solution, though I think Ulrich Diez's solution is the more elegant one. Sep 8, 2021 at 18:20
• @TheMindWithin You are welcome. I have been working recently to improve readarray's capability and convenience. I will keep working on it. Sep 8, 2021 at 18:23

I managed to get something to work by myself, so this is my best attempt at it. Maybe someone else will find a more elegant solution.

\documentclass{article}

\usepackage{datatool}
\DTLsetseparator{;}
\DTLnewdb{db}

\makeatletter
\DTLforeach*{tmpDB}{\DBfirst=first,\DBsecond=second}{
\DTLnewrow{db}
{\let\DTLnewdbentry\relax
\protected@xdef\insertnewdbentry{%
\DTLnewdbentry{db}{first}{\DBfirst}%
\DTLnewdbentry{db}{second}{\DBsecond}%
}}\insertnewdbentry
}
\DTLdeletedb{tmpDB}
`