Take the 2-minute tour ×
TeX - LaTeX Stack Exchange is a question and answer site for users of TeX, LaTeX, ConTeXt, and related typesetting systems. It's 100% free, no registration required.

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.

share|improve this question
    
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 '12 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. –  Raphael Halimi Jul 13 '12 at 21:06
add comment

1 Answer

up vote 10 down vote accepted

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}

output of code

share|improve this answer
    
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. –  Raphael Halimi Jul 16 '12 at 15:20
    
Readers shall find interesting materials in the formatting best practices detailed by Alan. –  Raphael Halimi Jul 16 '12 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 '12 at 23:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.