About
Shop
LaTeX
Software
Books
Gallery
News
Contact
Blog
Settings
Account
Latest news 2021-06-05: New blog post: Dickimaw Books Site Account.

Display Customer List (Null Values)

This example needs people.csv and people.dbtex, which is created using:

datatooltk --output people.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM people" 
Alternatively you can use datatooltk-gui to import the data and save it to a file called people.dbtex. Landscape is used since the tables are quite wide.
\documentclass[captions=tableheading,pagesize,paper=landscape]{scrartcl}

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}

\usepackage{pifont}
\usepackage{datatool}

\DTLloaddb{peoplecsv}{people.csv}

\DTLloaddbtex{\peoplesql}{people.dbtex} 

\newcommand*{\ifcsbool}[3]{%
  \ifboolexpr
  {
    test{\ifdefstring{#1}{true}} or
    test{\ifdefstring{#1}{1}}
  }
  {#2}{#3}%
}

\begin{document}

Table~\ref{tab:peoplecsv} uses the CSV data, which has some empty
values.
Table~\ref{tab:peoplesql} uses the SQL data, which has some null
values.
Table~\ref{tab:peoplenullcheck} checks for null\slash empty
values and 0\slash false \& 1\slash true boolean values.

\begin{table}
  \caption{Customers (CSV)}
  \label{tab:peoplecsv}
  \centering
  \DTLdisplaydb
  [id,forenames,title,country,postcode,gender,dob]% omit these columns
  {peoplecsv}
\end{table}

\begin{table}
  \caption{Customers (SQL)}
  \label{tab:peoplesql}
  \centering
  \DTLdisplaydb
  [id,forenames,title,country,postcode,gender,dob]% omit these columns
  {\peoplesql}
\end{table}

\begin{table}
  \caption{Customers (Check for Null and Boolean)}
  \label{tab:peoplenullcheck}
  \centering
   \begin{tabular}{lllllc}
    \multicolumn{1}{c}{\bfseries Surname} &
    \multicolumn{1}{c}{\bfseries Address 1} &
    \multicolumn{1}{c}{\bfseries Address 2} &
    \multicolumn{1}{c}{\bfseries Town} &
    \multicolumn{1}{c}{\bfseries County} &
    \multicolumn{1}{c}{\bfseries Subscribed}%
    \DTLforeach*{\peoplesql}{\Surname=surname,\AddressI=address1,%
    \AddressII=address2,\Town=town,\County=county,\Subscribed=subscribed}%
    {%
       \\\Surname & \AddressI &
       \DTLifnullorempty{\AddressII}{\multicolumn{1}{c}{---}}{\AddressII}
&
       \Town &
       \DTLifnullorempty{\County}{\multicolumn{1}{c}{---}}{\County}&
       \ifcsbool{\Subscribed}{\ding{52}}{\ding{56}}%
    }%
  \end{tabular}
\end{table}

\end{document}

Download sample-null.tex or sample-null.pdf.

© 2015 Dickimaw Books. "Dickimaw", "Dickimaw Books" and the Dickimaw parrot logo are trademarks. The Dickimaw parrot was painted by Magdalene Pritchett.

Terms of Use Privacy Policy Cookies Site Map FAQs