# datatool: How to load a table from file, rotate or not, then impose predefined headers

This is a follow-up question to my previous one.

In this one, I would like to tackle two cases:

First, load the table as it is while just imposing my own column and row headers.

Second, load the table and rotate it, then impose my own column and row headers.

For both cases mentioned above, two different types of databases are to be handled as shown below

\documentclass{article}
\usepackage{datatool,filecontents,booktabs}

% 1st dataset
\begin{filecontents*}{data1.csv}
,   col1  , col2  , col3
row1    ,   11    , 12    , 13
row2    ,   21    , 22    , 23
\end{filecontents*}

% 2nd dataset
\begin{filecontents*}{data2.csv}
11  , 12 , 13
21  , 22 , 23
\end{filecontents*}

\begin{document}

\section{First case: without rotation}

The desired output for both datasets should be

\begin{table}[h]
\centering
\caption{without rotation}
\begin{tabular}{@{}lccc@{}}
\toprule
& First Column  & Second Column & Third Column\\
\midrule
First Row   &   11  &   12  & 13 \\
\midrule
Second Row  &   21  &   22  & 23 \\
\bottomrule
\end{tabular}
\end{table}

\section{Second case: with rotation}

The desired output for both datasets should be

\begin{table}[h]
\centering
\caption{with rotation}
\begin{tabular}{@{}lcc@{}}
\toprule
& First Column  & Second Column\\
\midrule
First Row   &   21  &   11 \\
\midrule
Second Row  &   22  &   12 \\
\midrule
Third Row   &   23  &   13 \\
\bottomrule
\end{tabular}
\end{table}
\end{document}


It's best to ensure that both databases are assigned the same keys (through the keys option in \DTLloaddb). This means that the keys can be used to reference the same column in both databases, even though they have different column indexes:

\DTLloaddb[keys={rowheader,col1,col2,col3}]{database1}{database1.csv}


The column headers can be set for both databases using these keys:

\newcommand{\setdatabaseheaders}[1]{%
}



However, if the column headers are being shared across all the tables, then it's simpler to just store them in an easy to access manner with the help of etoolbox's \csdef command:

\csdef{columnheader1}{First Column}


There's no concept of row headers with datatool but it's easy enough to provide some based on the row index in a similar way:

\csdef{rowheader1}{First Row}


The column identified by rowheader needs to be ignored when displaying the data. The existence of this column can be checked with \DTLifhaskey. The custom commands for displaying the data can insert the row headers by referencing the command given by \csname rowheaderidx\endcsname (or \csuse{rowheaderidx}) at the start of each row. Similarly for the column headers.

Complete MWE:

\documentclass{article}

\usepackage{booktabs}
\usepackage{datatool}

% Case 1
\begin{filecontents*}{database1.csv}
,col1   , col2  , col3
row1    , 11    , 12    , 13
row2    , 21    , 22    , 23
\end{filecontents*}

% Case 2
\begin{filecontents*}{database2.csv}
11  , 12 , 13
21  , 22 , 23
\end{filecontents*}

\newcount\columnidx
\newcount\rowidx
\newcount\columncount

\newcommand{\displaydatabase}[1]{%
{%
\columncount=\numexpr\DTLcolumncount{#1}-1\relax
\def\columnoffset{1}%
}%
{%
\columncount=\DTLcolumncount{#1}%
\def\columnoffset{0}%
}%
% construct \begin{tabular} arguments
\def\columnargs{}%
\columnidx=0
\loop
\appto\columnargs{c}%
\ifnum\columnidx<\columncount
\repeat
\edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
\appto\tabularcontents{\toprule}%
{%
{}%
{%
\eappto\tabularcontents{\noexpand&
}%
}%
% iterate over all rows
\rowidx=0
\loop
\eappto\tabularcontents{\noexpand\\\noexpand\midrule
% columns
{%
{}%
{%
\DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
\eappto\tabularcontents{\noexpand& \expandonce\thisvalue}%
}%
}%
\ifnum\rowidx<\DTLrowcount{#1}
\repeat
\appto\tabularcontents{\\\bottomrule\end{tabular}}%
\tabularcontents
}

\newcommand{\displayrotateddatabase}[1]{%
{%
\def\columnoffset{1}%
}%
{%
\def\columnoffset{0}%
}%
\columncount=\DTLrowcount{#1}%
% construct \begin{tabular} arguments
\def\columnargs{}%
\columnidx=0
\loop
\appto\columnargs{c}%
\ifnum\columnidx<\columncount
\repeat
\edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
\appto\tabularcontents{\toprule}%
\columnidx=0
\loop
\eappto\tabularcontents{\noexpand&
\ifnum\columnidx<\columncount
\repeat
% iterate through all columns omitting rowheader
{%
{}%
{%
\eappto\tabularcontents{%
\noexpand\\\noexpand\midrule
% row loop in reverse order
\rowidx=\DTLrowcount{#1}\relax
\loop
\DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
\eappto\tabularcontents{\noexpand&\expandonce\thisvalue}%
\ifnum\rowidx>0
\repeat
}%
}%
\appto\tabularcontents{\\\bottomrule\end{tabular}}%
\tabularcontents
}

\newcommand{\displaytransposedatabase}[1]{%
{%
\def\columnoffset{1}%
}%
{%
\def\columnoffset{0}%
}%
\columncount=\DTLrowcount{#1}%
% construct \begin{tabular} arguments
\def\columnargs{}%
\columnidx=0
\loop
\appto\columnargs{c}%
\ifnum\columnidx<\columncount
\repeat
\edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
\appto\tabularcontents{\toprule}%
\columnidx=0
\loop
\eappto\tabularcontents{\noexpand&
\ifnum\columnidx<\columncount
\repeat
% iterate through all columns omitting rowheader
{%
{}%
{%
\eappto\tabularcontents{%
\noexpand\\\noexpand\midrule
% row loop
\rowidx=0\relax
\loop
\DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
\eappto\tabularcontents{\noexpand&\expandonce\thisvalue}%
\ifnum\rowidx<\DTLrowcount{#1}
\repeat
}%
}%
\appto\tabularcontents{\\\bottomrule\end{tabular}}%
\tabularcontents
}

\begin{document}
\section{No rotation}
\subsection{database1}

\displaydatabase{database1}

\subsection{database2}

\displaydatabase{database2}

\subsection{Desired Output}

\begin{table}[h]
\centering
\caption{without rotation}
\begin{tabular}{@{}lccc@{}}
\toprule
& First Column  & Second Column & Third Column\\
\midrule
First Row   &   11  &   12  & 13 \\
\midrule
Second Row  &   21  &   22  & 23 \\
\bottomrule
\end{tabular}
\end{table}

\newpage
\section{Rotation}
\subsection{database1}

\displayrotateddatabase{database1}

\subsection{database2}
\displayrotateddatabase{database2}

\subsection{Desired Output}
\begin{table}[h]
\centering
\caption{with rotation}
\begin{tabular}{@{}lcc@{}}
\toprule
& First Column  & Second Column\\
\midrule
First Row   &   21  &   11 \\
\midrule
Second Row  &   22  &   12 \\
\midrule
Third Row   &   23  &   13 \\
\bottomrule
\end{tabular}
\end{table}

\newpage
\section{Transpose}
\subsection{database1}

\displaytransposedatabase{database1}

\subsection{database2}

\displaytransposedatabase{database2}

\end{document}


Non-rotated tables:

Rotated tables:

Transposed tables:

Notes:

• \DTLloaddb doesn't trim trailing spaces, so if I hadn't used keys={rowheader,col1,col2,col3}, the keys for the second, third and fourth columns would include a trailing space. The following MWE doesn't work:

\documentclass{article}

\usepackage{datatool}

\begin{filecontents*}{database1.csv}
,col1   , col2  , col3
row1    , 11    , 12    , 13
row2    , 21    , 22    , 23
\end{filecontents*}

\begin{document}

\end{document}


This produces the error

! Package datatool Error: Database database1' doesn't contain key col1'.


The column label includes a trailing space, so the assignment needs to be:

\DTLsetheader{database1}{col1 }{First Column}

• Leading spaces are ignored, so

\DTLsetheader{database1}{ col2 }{Second Column}


produces an error, even though there's a leading space in the CSV file. It needs to be:

\DTLsetheader{database1}{col2 }{Second Column}


Similarly for each entry. For example:

\documentclass{article}

\usepackage{datatool}

\begin{filecontents*}{database1.csv}
,col1   , col2  , col3
row1    , 11    , 12    , 13
row2    , 21    , 22    , 23
\end{filecontents*}

\begin{document}

\DTLgetvalue{\thisvalue}{database1}{1}{2}\thisvalue'.

\end{document}


This produces:

The leading space is ignored but the trailing space is retained.

In this example, it doesn't make much difference as the keys are assigned using keys which doesn't contain any spaces. The entries are in a tabular environment so the spurious space isn't noticeable, but it's something to be careful about.

• For the second time, your help is highly appreciated. However, I am afraid I couldn't find how to rotate (not transpose) the data in your answer as I showed in my second desired table. – Diaa Feb 19 '18 at 19:45
• @DiaaAbidou Sorry I should've checked the output more closely with yours. By rotation, do you mean that the second row becomes the first column and the first row becomes the second column? Do you always have two rows of data? – Nicola Talbot Feb 19 '18 at 20:30
• Thanks for consideration. By rotation, I mean that the first row becomes the last column, and the last row becomes the first column, and so on for the rest of rows. Usually, I have several m rows and n columns. – Diaa Feb 19 '18 at 21:02
• @DiaaAbidou Okay. Hopefully I've got it right now :-) – Nicola Talbot Feb 19 '18 at 21:30
• @DiaaAbidou \begin{tabular}{@{}l\columnargs @{}} should work. (Make sure there's a space after \columnargs if @` is a letter.) – Nicola Talbot Feb 21 '18 at 11:27