Datatool and currency-type data

Question

The package datatool gives me some headaches with the currency-type fields.
My data is a test.csv file exported from Excel, without header:

a,"$100,090.06 ","$107,578.03 ",0.129565278  
b,"$267,697.13 ","$72,463.54 ",0.087442318  
c,"$761,314.81 ","$192,210.83 ",0.231820008  
some text  
some text2

I'd like to (1) add totals for some columns; and (2) round the figures:

Label     Amount1     Amount2  Percent  

----------------------------------------

a        $100,090    $107,578      13%  
b        $267,697     $72,464       9%  
c        $761,315    $192,211      23%  

----------------------------------------

Total: $1,129,102                  45%  

Here is my code:

\documentclass[a4paper, 11pt]{article} 
\usepackage{datatool}
\begin{document}

\DTLloadrawdb[noheader,headers={label,Amount1,Amount2,\%},keys={A,B,C,D}]{myDB}{test.csv}
Displaying the table:\\
\DTLdisplaydb{myDB} \\
Operating the table:\\
\def\totalAmount{0}
\def\totalPercent{0} %initializing totalAmount and totalPercent to 0

\begin{tabular}{r|ll|r}
label & Amount1 &  Amount2 & Percent
\DTLforeach[\value{DTLrowi}<3]{myDB}{\aa=A,\bb=B,\cc=C,\dd=D}{
\\ 
\aa & \bb & \cc & 
\DTLmul{\dd}{\dd}{100}\DTLround{\dd}{\dd}{0}\dd\%  %formatting the percent column

\DTLgadd{\totalPercent}{\dd}{\totalPercent} %cumulating percentages
\DTLgadd{\totalAmount}{\bb}{\totalAmount} %cumulating Amount <- THIS LINE DOES NOT WORK (when removing it, the code does partially what I want), probably because \bb is not seen as real
} \\ \hline
Total & \totalAmount &  & \totalPercent\%
\end{tabular}

\end{document}

I guess my failure comes the fact that \bb and \cc are currencies and not real.

Answer 1

The problem lies with the trailing spaces in your CSV currency values. Because of them, the numbers are treated as strings and the internal conversion from currency to decimal that takes place fails. So with the following CSV:

a,"$100,090.06","$107,578.03",0.129565278
b,"$267,697.13","$72,463.54",0.087442318
c,"$761,314.81","$192,210.83",0.231820008
some text  
some text2

your document works. Note that in order to treat the values as currency you need to set you total to a currency first, when you define it.

I've also made a few changes to the way you've set up the table. Instead of using \\ at the beginning of each row, I've added the \\ to the header row where it should be, and then used \DTLiffirstrow to eliminate it in the first row. Although this doesn't appear to make a difference in your particular example, it's a good habit to get into, since in more complex examples, it can lead to problems. I've also removed some extra spaces in the table body which were affecting the spacing. When you perform calculations in the table cells it's a good idea to end the macro with a % to make sure there are no extra spaces.

\documentclass{article}

\usepackage{datatool}
\begin{document}
\DTLloadrawdb[noheader,headers={label,Amount1,Amount2,\%},keys={A,B,C,D}]{myDB}{test.csv}
Displaying the table:

\DTLdisplaydb{myDB}

Operating the table:

\def\totalAmount{\$0} % make this a currency
\def\totalPercent{0} %initializing totalAmount and totalPercent to 0
\def\tmp{}


\begin{tabular}{r|rr|r@{}}
label & Amount1 &  Amount2 & Percent\\% table newline added here
\DTLforeach[\value{DTLrowi}<3]{myDB}{\aa=A,\bb=B,\cc=C,\dd=D}{%
\DTLiffirstrow{}{\\}
\aa & \bb & \cc &
\DTLmul{\dd}{\dd}{100}\DTLround{\dd}{\dd}{0}\dd\%%formatting the percent column (space removed here)
\DTLgadd{\totalPercent}{\dd}{\totalPercent}%cumulating percentages (space removed here)
\DTLgadd{\totalAmount}{\bb}{\totalAmount}% adding column B (space removed here)
}\\\hline
Total & \totalAmount & &\totalPercent\%
\end{tabular}

\end{document}