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} 

Displaying the table:\\
\DTLdisplaydb{myDB} \\
Operating the table:\\
\def\totalPercent{0} %initializing totalAmount and totalPercent to 0

label & Amount1 &  Amount2 & Percent
\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\%


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

  • So your test.csv contains comma-delimited fields and some random text at the bottom? Is there some way of identifying when the text begins/data ends?
    – Werner
    Jul 13, 2012 at 17:09
  • Yes, my true data are between lines 1 and N where I know N (I let these lines because I wasn't sure they were responsible for some issues). Option [\value{DTLrowi}<3] deals with this, thanks. Jul 13, 2012 at 21:06

1 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:

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.


Displaying the table:


Operating the table:

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

label & Amount1 &  Amount2 & Percent\\% table newline added here
\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)
Total & \totalAmount & &\totalPercent\%


output of code

  • Alan, very insightful, thanks. Following your code, I tried to address 2 issues: (1) rounding the amount in USD (2) pre-process the test.csv file to remove trailing spaces. (1) Rounding issue: I replace the \bb by \$\DTLconverttodecimal{\bb}{\eee}\DTLround{\eee}{\eee}{0}\eee (2) Remove trailing spaces: I failed by trying to use the DTLsubstituteall. Jul 16, 2012 at 15:20
  • Readers shall find interesting materials in the formatting best practices detailed by Alan. Jul 16, 2012 at 16:40
  • 1
    @RaphaelHalimi I think if you were to preprocess the file it would be easier to use non TeX tools. On Linux or MacOS, assuming your data doesn't have other fields in which spaces matter, you can just use sed -e "s/[ ]*//g" -i "" test.csv This will remove all the spaces in the file.
    – Alan Munn
    Jul 16, 2012 at 23:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .