Join databases with datatool

Question

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
\newcommand{\recursive}[1]{\DTLloadrawdb{dbA}{#1}@ifnextchar\bgroup{\recursive}{}}
\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.

Answer 1

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}
\usepackage{readarray}[2021-08-08]
\begin{document}
\readarraysepchar{;}
\readdef{databaseA.csv}\dbA
\readarray\dbA\arrayA[-,2]
\readdef{databaseB.csv}\dbB
\readarray\dbB\arrayB[-,2]
\readdef{databaseC.csv}\dbC
\readarray\dbC\arrayC[-,2]
\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}
\newcommand\stripheader[1]{\expandafter\stripheaderaux#1\relax}
\def\stripheaderaux#1;#2;#3\relax{#3}
\documentclass{article}
\usepackage{readarray}[2021-08-08]
\begin{document}
\readarraysepchar{;}
\readdef{databaseA.csv}\dbA
\readdef{databaseB.csv}\dbB
\readdef{databaseC.csv}\dbC
\edef\dbjoined{\dbA\stripheader\dbB\stripheader\dbC}
\readarray\dbjoined\arrayjoined[-,2]
\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}

Answer 2

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
\newcommand\LoadNoMoreDatabases{\LoadNoMoreDatabases}%
\newcommand\LoadDatabases[1]{\DTLnewdbonloadtrue\LoadDatabasesLoop{\DTLnewdbonloadfalse}{#1}}
\newcommand\LoadDatabasesLoop[3]{%
  % #1 tokens to execute after \DTLloadrawdb
  % #2 name of database
  % #3 name of csv-file or end-marker for the loop.
  \ifx\LoadNoMoreDatabases#3\expandafter@secondoftwo\else\expandafter@firstoftwo\fi
  {\DTLloadrawdb{#2}{#3}#1\LoadDatabasesLoop{}{#2}}%
  {\DTLnewdbonloadtrue}%
}%
\makeatother
% Syntax of the mechanism:
%
% \LoadDatabases{<database to create>}{<csv-file 1>}{<csv-file 2>}...{<csv-file k>}\LoadNoMoreDatabases
\LoadDatabases{db}{databaseA.csv}{databaseB.csv}{databaseC.csv}\LoadNoMoreDatabases
\begin{document}
\DTLdisplaydb{db}
\end{document}

The above does

\DTLnewdbonloadtrue
\DTLloadrawdb{⟨database to create⟩}{⟨csv-file 1⟩}%
\DTLnewdbonloadfalse
\DTLloadrawdb{⟨database to create⟩}{⟨csv-file 2⟩}%
\DTLloadrawdb{⟨database to create⟩}{⟨csv-file 3⟩}%
...
\DTLloadrawdb{⟨database to create⟩}{⟨csv-file k-1⟩}%
\DTLloadrawdb{⟨database to create⟩}{⟨csv-file k⟩}%
\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
\NewDocumentCommand\LoadDatabases{mm}{
  \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
\LoadDatabases{db}{databaseA.csv, databaseB.csv, databaseC.csv}
\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}
  \DTLnewdbonloadfalse
  \clist_map_inline:nn {#2} {\DTLloadrawdb{#1}{##1}}
  \DTLnewdbonloadtrue
}

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

yields:

  \DTLnewdb{db}
  \DTLnewdbonloadfalse
  \clist_map_inline:nn {databaseA.csv, databaseB.csv, databaseC.csv} {\DTLloadrawdb{db}{#1}}
  \DTLnewdbonloadtrue

(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}%
\DTLloadrawdb{db}{databaseB.csv}%
\DTLloadrawdb{db}{databaseC.csv}%

Answer 3

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
\newcommand{\AddAllDBs}[1]{
    \DTLloadrawdb{tmpDB}{#1}
        \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}
    @ifnextchar\bgroup{\AddAllDBs}{}
}
\makeatother
\begin{document}
\AddAllDBs{databaseA.csv}{databaseB.csv}{databaseC.csv}
\DTLdisplaydb{db}
\end{document}