Formatting complex table from CSV using datatool

Question

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

Answer 1

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

Answer 2

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}