[Tex/LaTex] Formatting complex table from CSV using datatool

databasedatatooltables

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).

enter image description here

Best Answer

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.)

Related Question