Friday, January 20, 2012

Mail-Merge Batch Generating Documents with datatool Package

If you aren’t aware of the possibility before this, you read the title right, it’s possible to do mail-merging in LaTeX with e.g. the versatile datatool package. This makes LaTeX quite a handy Swiss knife in a pinch.

The example scenario: you need to produce a batch of letters, name cards, or certificates really quickly. Let’s say certificates of appreciation for some long-serving employees. The recipients list has most probably been compiled by someone, as a spreadsheet like this:


Now all spreadsheet applications should be able to export the worksheet as a comma-separated values (CSV) plain text file, so we have namelist.csv with the following contents:

Name,ID,Gender,Years in Service
Abdul Ali,382473856,M,15
Francesca Joestar,461276432,F,10
Chan Ker Mei,463724631,F,5
Hikaru Yagami,154954739,M,10

The datatool package can then load namelist.csv as a simple database, which each line being a record, consisting of fields delimited by commas. By default, the field names are given by the first line of the .csv file. After assigning macros to the field names, we can then use the macros to insert ‘mail merge’ fields into a LaTeX document.

Here’s a quick example, using the wallpaper package and an external image (image courtesy of fromoldbooks.org) for the decorative frame:

% Use scrartcl to allow larger base font size
\documentclass[16pt]{scrartcl}
\usepackage[left=3cm,right=3cm,top=6cm,bottom=5cm]{geometry}

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{tgschola}
\usepackage{fourier-orns}
\usepackage{graphicx}
\usepackage{wallpaper} % For background image frame

\usepackage{datatool}
% Load database 'names' from file 'namelist.csv'
\DTLloaddb{names}{namelist.csv}

\begin{document}
\linespread{2}\selectfont
\pagestyle{empty}
% Set old-border.jpg as background image
\TileWallPaper{\paperwidth}{\paperheight}{old-border}

% For each record (line) in database 'names'
% Assign field values by name to macros
\DTLforeach{names}
{\name=Name, \ID=ID, \gender=Gender, \yr={Years in Service}}{

  {\centering
    {\LARGE\bfseries Certificate of Appreciation}\par
    {\LARGE\decofourleft\quad\decoone\quad\decofourright}\par
    \vskip2em
    We thank\par
    % Insert '\name' field
    {\large\bfseries\MakeUppercase{\name}}\par
    % Insert '\ID' field
    {\large (ID: \ID)}\par
    for having been with\par
    {\large XYZ Company}\par
    % Insert '\yr' field
    {for \emph{\LARGE\yr} glorious years}\par
    \vskip1em
    % Test '\gender' field and insert him/her, his/her
    We commend \DTLifeq{\gender}{M}{him}{her} on
    \DTLifeq{\gender}{M}{his}{her} excellent service.\par
  }
  \vfill
  \hfill
  \begin{minipage}{.4\textwidth}
    Managing Director\par
    \vskip1cm
    \rule{8em}{.5pt}
    \vskip-1em
    (MD's Name)\par
  \end{minipage}

  \pagebreak
}
\end{document}

And voilĂ , the output document has 4 pages, each containing the certificate for a recipient listed in namelist.csv.



Just for fun, here’re the same certifates using ornaments from the adforn font package. For more flamboyant ornaments, see the webomints fonts (installable via getnonfreescripts) or the psvectorian package.



The .tex source codes can be downloaded here.

datatool has many more macros for testing field values and even arithmetics. Other accompanying packages in the bundle, such as datapie, dataplot and databar, even lets you draw charts from .csv files via TikZ/PGF, so do check out the documentation.

Now I’m off to my holidays! Happy Chinese New Year to everyone, and may the Year of the Dragon brings only good tidings for you!

4 comments:

  1. Dear Lian Tze Lim, I find that your sit is very useful with new applications of LaTeX. Hats off to you people for the work. I would like to have a request. Why don't you post things like "mail merging" quite often so that people learn new things. Please spare your valuable time to post more and more blogs (I mean more frequently) like this. Good work and thank you a lot.

    ReplyDelete
  2. @Harish Thank you! Trust me, we do try to post as frequently as possible. :) Comments like yours add to the motivation. But then you know, work, family, ...stuff...

    ReplyDelete
  3. @LTLim True. That why I said you people are doing great job amid your tight work schedule. Keep it up.

    ReplyDelete