# Formatting complex table from CSV using datatool

I have this csv file, formatted like this one:

Store,Product 1,Product 2,Product 3,Product 4,Product 5,Sub Total
Store 1,15,99,299,75,292,780
Store 2,33,353,429,283,561,1659
Store 3,248,381,403,306,454,1792
Store 4,3,14,42,2,32,93
Store 5,129,37,22,89,39,316
Store 6,147,396,404,221,441,1609
Store 7,1228,998,797,1008,1369,5400


I want to it to be formatted like the one in the figure.

Because the csv file is provided as-is and will be loaded dynamically. Is it possible to do that kind of work, just using the datatool without re-format the csv? (e.g. re-format using Excel, then export to new csv file).

• There is nothing complex about the CSV file: can you give more detail on what in particular you are finding difficult? Commented May 6, 2011 at 20:58
• @Joseph Wright How do I get the count of percentage and the Total as shown in the last row? or I missed something in the datatool manual. Commented May 6, 2011 at 21:07
• @user5372: See my answer Commented May 6, 2011 at 21:35

## 2 Answers

Taking Alan's example and modifying it, you can use the various calculation functions in datatool to do something like

\documentclass{article}
\usepackage{booktabs,datatool}
\usepackage[margin=1in]{geometry}
\DTLloaddb{stores}{stores.csv}
\newcommand*\calcpercent[1]{%
\DTLdiv{\tmp}{#1}{\subtotal}%
\DTLmul{\tmp}{\tmp}{100}%
\DTLround{\tmp}{\tmp}{1}%
\tmp\,\%
}
\def\total{0}
\DTLforeach{stores}{\subtotal=Sub Total}{\DTLadd{\total}{\total}{\subtotal}}
\begin{document}
\begin{tabular}{llllllll}
Header row
\DTLforeach{stores}{%
\store=Store,%
\one=Product 1,%
\two=Product 2,%
\three=Product 3,%
\four=Product 4,%
\five=Product 5,%
\subtotal=Sub Total%
}{%
\\
\store & \one & \two & \three & \four & \five & \subtotal
&
\DTLdiv{\tmp}{\subtotal}{\total}%
\DTLmul{\tmp}{\tmp}{100}%
\DTLround{\tmp}{\tmp}{1}%
\tmp\,\%
\\
& \calcpercent{\one}
& \calcpercent{\two}
& \calcpercent{\three}
& \calcpercent{\four}
& \calcpercent{\five}
}\\
\end{tabular}
\end{document}


I've not done any formatting here, but the general idea should be clear. (I'd also note that LaTeX is a typesetting system: if you need to do lots of processing, consider a script tool such as Perl, Python or Lua to pre-process the input .csv into a modified one containing the results.)

• You beat me to it. :-) Commented May 6, 2011 at 21:44
• Thank you very much, I got it now. I think I'll try to get the pre-formatted csv or maybe direct to .tex using SQL query [simple LaTeX but maybe complex query :-)] Commented May 6, 2011 at 22:53

Yes, this is standard form for the CSV file. (Assuming your example CSV file is called stores.csv)

\documentclass{article}

\usepackage{datatool}
\usepackage[margin=1in]{geometry}
\begin{document}
\DTLloaddb{stores}{stores.csv}
\DTLdisplaydb{stores}
\end{document}


• Yes, that is the default that I get. But I want the table formatted like this one link, (the second table) Commented May 6, 2011 at 21:17