1

Hi I've been trying to use the datatool package to get data from csv files easily into documents as formatted tables. I seem to keep getting the lines on the first column shifted down by a row.

If I rewrite the code so that it mimics the datatool manual \DTLforeach command I can't add the horizontal lines. What should I do differently? Am I missing something really obvious?

A supplementary question: If I want to use the \DTLdecimaltolocale{item}{cmd} to add thousands separators to the item lists in this database how can I do that? Thanks

Two tables showing datatool misaligned csv

\documentclass{article}
\usepackage{array,datatool}

\begin{filecontents}{sample.csv}
account, opening balance, expected, revised, amends
fruit, 1000000,1500000,1750000,250000
berries, 400000,500000, 450000,-50000
nuts, 300000,375000,390000,15000
legumes, 1250000, 1000000, 950000, 50000
cereals, 800000, 80000, 90000, 10000
\end{filecontents}

\begin{document}
\begin{table}[c]
\DTLloaddb[keys={ac,ob,ep,rv,df}]{dtl1}{sample.csv}
\caption{A Table to show misaligned borders}

\begin{tabular}{|p{4cm}|>{\raggedleft}p{2cm}|>{\raggedleft}p{2cm}|
>{\raggedleft}p{2cm}|>{\raggedleft}p{2cm}|}
\hline 
 & \bfseries 2010 Actual (a) & \textbf{2011 Target} & \textbf{2011
   Updated Plan (b)} &
\textbf{Difference (a) - (b)} \tabularnewline \hline \hline
\DTLforeach{dtl1}{\what=ac,\current=ob,\budget=ep,\plan=rv,\diff=df}{%
\what & \current & \budget & \plan & \diff \tabularnewline}
\end{tabular}
\end{table}

\begin{table}
\caption{A Table to show misaligned borders working}
\begin{tabular}{|p{4cm}|>{\raggedleft}p{2cm}|>{\raggedleft}p{2cm}|
>{\raggedleft}p{2cm}|>{\raggedleft}p{2cm}|}
\hline
\bfseries account&\bfseries opening balance&\bfseries
expected&\bfseries revised&\bfseries amends\tabularnewline \hline \hline
fruit& 1000000&1500000&1750000&250000\tabularnewline
berries& 400000&500000& 450000&-50000\tabularnewline
nuts& 300000&375000&390000&15000\tabularnewline
legumes& 1250000& 1000000& 950000& 50000\tabularnewline
cereals& 800000& 80000& 90000& 10000\tabularnewline \hline
\end{tabular}
\end{table}


\end{document}
Tahnoon Pasha
  • 3,007

2 Answers2

2

Not an answer to your question but I also recommend pgfplotstable for such constructions. You can then customize your output very easily using the key-value system as below. 

\documentclass{article}
\usepackage{array,booktabs}
\usepackage{pgfplotstable}

\begin{filecontents}{sample.csv}
account, opening balance, expected, revised, amends
fruit,1000000,1500000,1750000,250000
berries,400000,500000, 450000,-50000
nuts,300000,375000,390000,15000
legumes,1250000,1000000, 950000,50000
cereals,800000,80000, 90000, 10000
\end{filecontents}

\pgfplotstableread[col sep=comma]{sample.csv}\mydata
\newcolumntype{d}{>{\raggedleft}b{2cm}}
\newcolumntype{e}{>{\raggedleft\arraybackslash}b{2cm}}
\begin{document}


\begin{table}
\caption{Fiscal Table of Breakfast Costs}
\pgfplotstabletypeset[
every head row/.style={before row={\toprule\hline},after row={\hline}},
every last row/.style={after row=\hline\bottomrule},
columns/account/.style         ={string type,column type={l},column name=Account},
columns/opening balance/.style ={column type={d},column name=Opening Balance},
columns/expected/.style        ={column type={d},column name=2011 Expected Target},
columns/revised/.style         ={column type={d},column name=2011 Updated Plan},
columns/amends/.style          ={column type={e},column name={Difference \textbf{(a) - (b)}}},
/pgf/number format/.cd,int detect,set thousands separator={,},
]\mydata
\label{tab:breakfast}
\end{table}
\end{document}

enter image description here

percusse
  • 157,807
  • Thanks @percusse 'pgfplotstable' was my next port of call to resolve this. Is there a way to add the column separator lines in this construction? What is the \arraybackslash in the column def doing by the way? – Tahnoon Pasha Sep 05 '12 at 13:12
  • @TahnoonPasha Yes, it's just the usual | character that goes into the column type key but note that vertical seperators are often considered to be bad style in terms of informatics as they introduce artifical obstacles for the reader. For \arraybackslash see this question: http://tex.stackexchange.com/questions/7757/i-dont-use-centering-but-why-must-i-use-arraybackslash-in-my-table – percusse Sep 05 '12 at 13:23
2

For the purposes of completeness I'm adding in a solution using datatool. It is more visually logical for me although I'm using tikz a lot so the tikz/pgfplotstable solution that @percusse presented is clearly more flexible. I noticed a lot of datatool questions regarding format and data but no formatting answers. In case this is of some use..

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

\begin{filecontents}{sample.csv}
account, opening balance, expected, revised, amends
fruit, 1000000,1500000,1750000,250000
berries, 400000,500000, 450000,-50000
nuts, 300000,375000,390000,15000
legumes, 1250000, 1000000, 950000, 50000
cereals, 800000, 80000, 90000, 10000
\end{filecontents}

\newcommand{\seps}[1]{\DTLdecimaltolocale{#1}{\tmp1}\tmp1}
\newcolumntype{d}{>{\raggedleft}b{2cm}}
\newcolumntype{e}{>{\raggedleft\arraybackslash}b{2cm}}
\DTLloaddb[keys={ac,ob,ep,rv,df}]{dtl1}{sample.csv}

\begin{document}
\begin{table}

\caption{A Table to show misaligned borders}
\begin{tabular}{|l|d|d|d|e|}
\toprule \hline 
 & \bfseries 2010 Actual (a) & \textbf{2011 Target} & \textbf{2011
   Updated Plan (b)} &
\textbf{Difference (a) - (b)}%
\DTLforeach{dtl1}{\what=ac,\current=ob,\budget=ep,\plan=rv,\diff=df}{%
\ifthenelse{\value{DTLrowi}=1}{\tabularnewline \hline}{\tabularnewline}
\what & \seps{\current} & \seps{\budget} & \seps{\plan} & \seps{\diff}}\\
\hline \bottomrule

\end{tabular}
\end{table}

\end{document}

enter image description here

David Carlisle
  • 757,742
Tahnoon Pasha
  • 3,007