# Merging two datatool databases from different files but with the same columns

Consider the following example code. This is not quite an MWE, because it contains elements that are strictly unnecessary.

I'd like to merge the two datatool databases, corrDB and docDB vertically (by row) and then sort them by the key column RowID. That last part, I think I know how to do, approximately. Though I am not sure if \dtlletterindexcompare is the right comparison method to use to sort keys of the form xxxx.xx.xx.yy where x are integers and the y are numbers. I don't know if I should ask a separate question about this, but in any case, feel free to comment.

\documentclass{article}
\usepackage{datatool}
\usepackage{datagidx}
\usepackage{array}
\usepackage{filecontents}
\usepackage{longtable}

\newcounter{tabenum}\setcounter{tabenum}{0}
\newcommand{\nextnuml}[1]{\refstepcounter{tabenum}\thetabenum.\label{#1}}
\newcommand*{\checkmissing}[1]{\DTLifnull{#1}{}{#1}}

\newcommand{\PrintDocTable}[3][]{%
% #1 = list of rowIDs
% #2 = database to search
% #3 =caption
\begin{longtable}{r l p{1in} c c p{3.5in}}
\caption{#3}\\
\DTLforeach
[%
\ifblank{#1}{\boolean{true}}{\DTLisSubString{#1}{\RowID}}
]
{#2}{%
\RowID=RowID,%
\Date=Date,%
\From=From,%
\To=To,%
\Subject=Subject%
}{%
\nextnuml{\RowID} & \Date & \checkmissing{\From} & \checkmissing{\To} & \Subject \\
}%
\end{longtable}
}%

\newcommand{\PrintCorrTable}[3][]{%
% #1 = list of rowIDs
% #2 = database to searchc
% #3 =caption
\begin{longtable}{r l p{1.5in} l l p{2.5in}}
\caption{#3}\\
\DTLforeach
[%
\ifblank{#1}{\boolean{true}}{\DTLisSubString{#1}{\RowID}}
]
{#2}{%
\RowID=RowID,%
\Date=Date,%
\From=From,%
\To=To,%
\Subject=Subject%
}{%
\nextnuml{\RowID} & \Date & \From & \To & \Subject \\
}%
\end{longtable}
}%

\begin{document}

\begin{filecontents*}{corr.csv}
2011.09.09.ab,   Sep 09 2011, AB,  CD , more stuff
2011.09.26.xy,   Sep 26 2011, XY,  WZ , some more stuff
2011.09.26.ab,   Sep 26 2011, AB,  CD , yet more stuff
2011.09.09,      Sep 09 2011, CD,  AB , stuff
\end{filecontents*}

\begin{filecontents*}{doc.csv}
2011.09.26.cd,   Sep 26 2011,  Doc 1
2011.09.26.ce,   Sep 26 2011,  Doc 2
2011.09.26.zz,   Sep 26 2011,  Doc 3
\end{filecontents*}

\PrintDocTable{docDB}{Documents}

\dtlsort{RowID}{corrDB}{\dtlletterindexcompare}
\PrintCorrTable{corrDB}{Correspondence}

\end{document}


Merging of tables with datatool is not natively supported. You'll either have to process the tables externally, or create a new table that represents the union of the tables to be merged. This latter approach is used below:

\documentclass{article}
\usepackage{datatool}
\usepackage{datagidx}
\usepackage{array}
\usepackage{filecontents}
\usepackage{longtable}

\newcounter{tabenum}\setcounter{tabenum}{0}
\newcommand{\nextnuml}[1]{\refstepcounter{tabenum}\thetabenum.}
\newcommand*{\checkmissing}[1]{\DTLifnull{#1}{}{#1}}

\newcommand{\PrintDocTable}[3][]{%
% #1 = list of rowIDs
% #2 = database to search
% #3 = caption
\begin{longtable}{r l p{1in} c c p{3.5in}}
\caption{#3} \\
\hline
\DTLforeach
[%
\ifblank{#1}{\boolean{true}}{\DTLisSubString{#1}{\RowID}}
]
{#2}{%
\RowID=RowID,%
\Date=Date,%
\From=From,%
\To=To,%
\Subject=Subject%
}{%
\nextnuml{\RowID} & \Date & \checkmissing{\From} & \checkmissing{\To} & \Subject \\
}%
\end{longtable}
}%

\newcommand{\PrintCorrTable}[3][]{%
% #1 = list of rowIDs
% #2 = database to searchc
% #3 = caption
\begin{longtable}{r l p{1.5in} l l p{2.5in}}
\caption{#3} \\
\hline
\DTLforeach
[%
\ifblank{#1}{\boolean{true}}{\DTLisSubString{#1}{\RowID}}
]
{#2}{%
\RowID=RowID,%
\Date=Date,%
\From=From,%
\To=To,%
\Subject=Subject%
}{%
\nextnuml{\RowID} & \Date & \From & \To & \Subject \\
}%
\end{longtable}
}%

\begin{document}

\begin{filecontents*}{corr.csv}
2011.09.09.ab,   Sep 09 2011, AB,  CD , more stuff
2011.09.26.xy,   Sep 26 2011, XY,  WZ , some more stuff
2011.09.26.ab,   Sep 26 2011, AB,  CD , yet more stuff
2011.09.09,      Sep 09 2011, CD,  AB , stuff
\end{filecontents*}

\begin{filecontents*}{doc.csv}
2011.09.26.cd,   Sep 26 2011,  Doc 1
2011.09.26.ce,   Sep 26 2011,  Doc 2
2011.09.26.zz,   Sep 26 2011,  Doc 3
\end{filecontents*}

\PrintDocTable{docDB}{Documents}

\dtlsort{RowID}{corrDB}{\dtlletterindexcompare}
\PrintCorrTable{corrDB}{Correspondence}

\makeatletter
% Merge two tables
\DTLnewdb{docDBcorrDB}% Create new merged table
% Read all entries from docDB and insert into docDBcorrDB
\DTLforeach*{docDB}{\docDBRowID=RowID,\docDBDate=Date,\docDBSubject=Subject}{
\DTLnewrow{docDBcorrDB}
{\let\DTLnewdbentry\relax% Avoid expansion of \DTLnewdbentry when using \protected@xdef below
\protected@xdef\insertnewdbentry{%
\DTLnewdbentry{docDBcorrDB}{RowID}{\docDBRowID}%
\DTLnewdbentry{docDBcorrDB}{Date}{\docDBDate}%
\DTLnewdbentry{docDBcorrDB}{Subject}{\docDBSubject}%
}}\insertnewdbentry
}
% Read all entries from corrDB and insert into docDBcorrDB
\DTLforeach*{corrDB}{\corrDBRowID=RowID,\corrDBDate=Date,\corrDBFrom=From,\corrDBTo=To,\corrDBSubject=Subject}{
\DTLnewrow{docDBcorrDB}
{\let\DTLnewdbentry\relax% Avoid expansion of \DTLnewdbentry when using \protected@xdef below
\protected@xdef\insertnewdbentry{%
\DTLnewdbentry{docDBcorrDB}{RowID}{\corrDBRowID}%
\DTLnewdbentry{docDBcorrDB}{Date}{\corrDBDate}%
\DTLnewdbentry{docDBcorrDB}{From}{\corrDBFrom}%
\DTLnewdbentry{docDBcorrDB}{To}{\corrDBTo}%
\DTLnewdbentry{docDBcorrDB}{Subject}{\corrDBSubject}%
}}\insertnewdbentry
}
\makeatother

% Sort database
\dtlsort{RowID}{docDBcorrDB}{\dtlletterindexcompare}
% Print new merged table
\begin{longtable}{r l p{1.5in} l l p{2.5in}}
\caption{Merge table (Documents + Correspondence)} \\
\hline
\DTLforeach
{docDBcorrDB}{%
\RowID=RowID,%
\Date=Date,%
\From=From,%
\To=To,%
\Subject=Subject%
}{%
\nextnuml{\RowID} & \Date & \checkmissing{\From} & \checkmissing{\To} & \Subject \\
}%
\end{longtable}

\end{document}


Of course, the construction of the docDBcorrDB database uses some knowledge of docDB and corrDB, since docDB doesn't have the From and/or To field. However, that's typical for query constructions and require some accommodation anyway. Either way, you'll have to process each table separately to get the information, whether they have the same structure or not.

The main principle behind the merge is to read entries one-by-one from the first database and constructing a "new entry" (in the form of \insertnewdbentry). Let's look at how this is performed. Here's the main construction for the first table (with line numbers):

1: \DTLforeach*{docDB}{\docDBRowID=RowID,\docDBDate=Date,\docDBSubject=Subject}{
2:   \DTLnewrow{docDBcorrDB}
3:   {\let\DTLnewdbentry\relax% Avoid expansion of \DTLnewdbentry when using \protected@xdef below
4:    \protected@xdef\insertnewdbentry{%
5:      \DTLnewdbentry{docDBcorrDB}{RowID}{\docDBRowID}%
6:      \DTLnewdbentry{docDBcorrDB}{Date}{\docDBDate}%
7:      \DTLnewdbentry{docDBcorrDB}{Subject}{\docDBSubject}%
8:   }}\insertnewdbentry
9: }


We cycle through each entry (line 1) using \DTLforeach. The starred version is used as the database will only be read, not written to. Each of the fields are assigned to specific macros (field RowID will be assigned to \docDBRowID, ...). For each record that is read, we create a new record in the merged database (line 2). The next block (lines 3-8) is meant to add a single record (in this case containing 3 different fields) to the new merged table. It creates \insertnewdbentry inside a group. However, since it's created using an \...xdef, the actual definition will be global. The reason for using an expanded definition is because the values for each field has to be expanded before placing them into the database. That is, if you just used

\DTLnewdbentry{docDBcorrDB}{RowID}{\docDBRowID}


the first record's field RowID would have the value \docDBRowID and not 2011.09.26.cd, as the macro \docDBRowID is not expanded before being stored; \...xdef ensures expansion. Using \protected@xdef makes sure that certain macros that require (or already use) \protection is not expanded. However, this does require the construction to be surrounded by a \makeatletter ... \makeatother pair. Now the tricky part is to expand \docDBRowID but not expand \DTLnewdbentry... The easy way around this is to set it to \relax (line 3). That way a \protected@xdef will not expand it (since \relax is not expandable) but it will expand \docDBRowID. When the group is closed (line 8), the value of \DTLnewdbentry is restored to what it is supposed to originally. As such, \insertnewdbentry will contain expanded versions of \docDBRowID, \docDBDate and \docDBSubject while keeping \DTLnewdbentry intact. Finally, after \insertnewdbentry is constructed, it's executed (still line 8) to now insert the correct values into the new database.

This process is repeated for each record in the first database, as well as the second database.

With the final database docDBcorrDB constructed, it's handled like any other database.

The datatool user guide mentions something about this expansion issue on p 38, but also provides a work-around (not included above). Setting the boolean switch \dtlexpandnewvalue will ensure that values are expanded before they are inserted into the database. Using this approach, the construction may seem simpler if you use

\dtlexpandnewvalue% Values to be inserted into database should be expanded first
% Merge two tables
\DTLnewdb{docDBcorrDB}% Create new merged table
% Read all entries from docDB and insert into docDBcorrDB
\DTLforeach*{docDB}{\docDBRowID=RowID,\docDBDate=Date,\docDBSubject=Subject}{
\DTLnewrow{docDBcorrDB}
\DTLnewdbentry{docDBcorrDB}{RowID}{\docDBRowID}%
\DTLnewdbentry{docDBcorrDB}{Date}{\docDBDate}%
\DTLnewdbentry{docDBcorrDB}{Subject}{\docDBSubject}%
}
% Read all entries from corrDB and insert into docDBcorrDB
\DTLforeach*{corrDB}{\corrDBRowID=RowID,\corrDBDate=Date,\corrDBFrom=From,\corrDBTo=To,\corrDBSubject=Subject}{
\DTLnewrow{docDBcorrDB}
\DTLnewdbentry{docDBcorrDB}{RowID}{\corrDBRowID}%
\DTLnewdbentry{docDBcorrDB}{Date}{\corrDBDate}%
\DTLnewdbentry{docDBcorrDB}{From}{\corrDBFrom}%
\DTLnewdbentry{docDBcorrDB}{To}{\corrDBTo}%
\DTLnewdbentry{docDBcorrDB}{Subject}{\corrDBSubject}%
}

• Hi Werner. Thanks, for the very nice answer. I suspected that datatool did not support this directly. Can you elaborate a bit on the TeX programming you are doing, for the benefit of non-experts? Also, I've added NULL values for From and To in docDB, so it doesn't require special handling... – Faheem Mitha Jan 5 '15 at 1:13