databaseexcelluatex
I have an Excel file that I use as a database. Of course, there are a lot of entries (more than 4000) and I don't want to use all the data to create my PDF.
So I was thinking of using LuaLaTeX to create a script to use only the data I need.
But I don't know how to access to my Excel file with my script. Is there a proper way or not?
I agree with the comments that you'd need a scripting language to extract the information from your existing documents, but once you have the information in a database you can use the datatool package to generate a report for a particular patient. For the type of information you want to save, I wouldn't recommend using csv as I would find that tricky to edit (but that depends on what you use to edit it). Here's how it might look in csv form (using | as a separator as you requested):
Patient Number|Name|Surname|DoB|Sex|Address|Phone|History|Past History|Personal History|Family History|Examination|Diagnosis|Treatment
0001|Joe|Bloggs|1974-12-06|Male|"1 The Street, The Town"|0123456|Joe's patient history here|Joe's past history here|Joe's personal history|Joe's family history|Joe's examination|\begin{enumerate}\item Joe's diagnosis\end{enumerate}|\begin{enumerate}\item Joe's treatment\end{enumerate}
0002|Jane|Doe|1970-05-18|Female|"2 The Street, The Town"|0123457|Mary's patient history here|Mary's past history here|Mary's personal history|Mary's family history|Mary's examination|\begin{enumerate}\item Mary's diagnosis\end{enumerate}|\begin{enumerate}\item Mary's treatment\end{enumerate}
0003|John|Smith|1969-01-20|Male|"3 The Street, The Town"|01234568|John's patient history here|John's past history here|John's personal history|John's family history|John's examination|\begin{enumerate}\item John's diagnosis\end{enumerate}|\begin{enumerate}\item John's treatment\end{enumerate}
Alternatively, the same information can be store in a .tex file using the datatool format:
\DTLnewdb{patients}
% Patient Joe Bloggs (patient number 0001)
\DTLnewrow*{patients}
\DTLnewdbentry*{patients}{Patient Number}{0001}
\DTLnewdbentry*{patients}{Name}{Joe}
\DTLnewdbentry*{patients}{Surname}{Bloggs}
\DTLnewdbentry*{patients}{DoB}{1974-12-06}
\DTLnewdbentry*{patients}{Sex}{Male}
\DTLnewdbentry*{patients}{Address}{1 The Street, The Town}
\DTLnewdbentry*{patients}{Phone}{0123456}
\DTLnewdbentry*{patients}{History}{Joe's patient history here}
\DTLnewdbentry*{patients}{Past History}{Joe's past history here}
\DTLnewdbentry*{patients}{Personal History}{Joe's personal history}
\DTLnewdbentry*{patients}{Family History}{Joe's family history}
\DTLnewdbentry*{patients}{Examination}{Joe's examination}
\DTLnewdbentry*{patients}{Diagnosis}
{%
\begin{enumerate}
\item Joe's diagnosis
\end{enumerate}
}
\DTLnewdbentry*{patients}{Treatment}
{%
\begin{enumerate}
\item Joe's treatment
\end{enumerate}
}
% Patient Jane Doe (patient number 0002)
\DTLnewrow*{patients}
\DTLnewdbentry*{patients}{Patient Number}{0002}
\DTLnewdbentry*{patients}{Name}{Jane}
\DTLnewdbentry*{patients}{Surname}{Doe}
\DTLnewdbentry*{patients}{DoB}{1970-05-18}
\DTLnewdbentry*{patients}{Sex}{Female}
\DTLnewdbentry*{patients}{Address}{2 The Street, The Town}
\DTLnewdbentry*{patients}{Phone}{0123457}
\DTLnewdbentry*{patients}{History}{Mary's patient history here}
\DTLnewdbentry*{patients}{Past History}{Mary's past history here}
\DTLnewdbentry*{patients}{Personal History}{Mary's personal history}
\DTLnewdbentry*{patients}{Family History}{Mary's family history}
\DTLnewdbentry*{patients}{Examination}{Mary's examination}
\DTLnewdbentry*{patients}{Diagnosis}
{%
\begin{enumerate}
\item Mary's diagnosis
\end{enumerate}
}
\DTLnewdbentry*{patients}{Treatment}
{%
\begin{enumerate}
\item Mary's treatment
\end{enumerate}
}
% Patient John Smith (patient number 0003)
\DTLnewrow*{patients}
\DTLnewdbentry*{patients}{Patient Number}{0003}
\DTLnewdbentry*{patients}{Name}{John}
\DTLnewdbentry*{patients}{Surname}{Smith}
\DTLnewdbentry*{patients}{DoB}{1969-01-20}
\DTLnewdbentry*{patients}{Sex}{Male}
\DTLnewdbentry*{patients}{Address}{3 The Street, The Town}
\DTLnewdbentry*{patients}{Phone}{0123458}
\DTLnewdbentry*{patients}{History}{John's patient history here}
\DTLnewdbentry*{patients}{Past History}{John's past history here}
\DTLnewdbentry*{patients}{Personal History}{John's personal history}
\DTLnewdbentry*{patients}{Family History}{John's family history}
\DTLnewdbentry*{patients}{Examination}{John's examination}
\DTLnewdbentry*{patients}{Diagnosis}
{%
\begin{enumerate}
\item John's diagnosis
\end{enumerate}
}
\DTLnewdbentry*{patients}{Treatment}
{%
\begin{enumerate}
\item John's treatment
\end{enumerate}
}
You can access a particular patient like this:
\documentclass{scrartcl}
\usepackage{datatool}
% load from csv file:
%\DTLsetseparator{|}
%\DTLloaddb{patients}{patients.csv}
% or load from .tex file:
\input{patients}
\title{Consultation Report}
\author{}
\newcommand*{\getdetails}[1]{%
\dtlgetentryfromcurrentrow{\patientdetails}{\dtlcolumnindex{patients}{#1}}%
\patientdetails
}
\begin{document}
\maketitle
% fetch patient's details (patient number 0002)
\dtlgetrowforvalue{patients}{\dtlcolumnindex{patients}{Patient Number}}{0002}%
\begin{tabular}{ll}
Name: & \getdetails{Name} \getdetails{Surname}\\
DoB: & \getdetails{DoB}\\
Sex: & \getdetails{Sex}\\
Address: & \getdetails{Address}\\
Phone: & \getdetails{Phone}
\end{tabular}
\section*{Clinical details}
\subsection*{Present history}
\getdetails{History}
\subsection*{Past History}
\getdetails{Past History}
\subsection*{Personal history}
\getdetails{Personal History}
\subsection*{Family history }
\getdetails{Family History}
\subsection*{Examination}
\getdetails{Examination}
\section*{Diagnosis}
\getdetails{Diagnosis}
\section*{Rx}
\getdetails{Treatment}
\begin{flushright}
My Name \\
{\footnotesize Qualification
}\\
{\footnotesize designation}\\
{\footnotesize Ph: }
\par\end{flushright}{\footnotesize \par}
\end{document}
A little more detail...
If I start with a spreadsheet which looks like yours, I then save as, pick .csv and choose ; as field separator and nothing to surround text. (This is in calc but I assume other software offers similar functionality.)
This produces the following .csv file which I saved as question.csv:
Name;Question1; Question2; Question3
Name1;Answer1.1;Answer2.1;Answer 3.1
Name2;Answer1.2;Answer2.2;Answer 3.2
I then run
gawk 'BEGIN { RS=";"; ORS="\n" } { print }' question.csv > question.dat
which produces question.dat:
Name
Question1
Question2
Question3
Name1
Answer1.1
Answer2.1
Answer 3.1
Name2
Answer1.2
Answer2.2
Answer 3.2
We don't especially want a pdf with the headers in it but I think it can be useful to have a 'dummy' page just to make sure everything ends up in the right places. However, you can easily exclude this if you prefer. It would be good to tidy up the stray spaces at the start of some lines, though:
sed -i 's/^ *//' question.dat
gets me question.dat:
Name
Question1
Question2
Question3
Name1
Answer1.1
Answer2.1
Answer 3.1
Name2
Answer1.2
Answer2.2
Answer 3.2
You can now use the data in a template .tex file, formatting it as you wish. Just for example, I've used the description environment as I don't know how long the answers might be so tabular seemed potentially problematic:
\documentclass{article}
\usepackage{textmerg}
\begin{document}
\Fields{\subjectname\questionone\questiontwo\questionthree}
\Merge{question.dat}{%
\begin{description}
\item[Name:] \subjectname
\item[Question1:] \questionone
\item[Question2:] \questiontwo
\item[Question3:] \questionthree
\end{description}
\cleardoublepage
}
\end{document}
This produces a 3 page pdf file. To separate the pages into separate pdfs, I used pdftk as follows:
pdftk question.pdf burst
This gives me pg_0001.pdf, pg_0002.pdf and pg_0003.pdf. The remaining problem is therefore to rename them using the names from the original file. This might be problematic if you have names with accented characters etc. Assuming nothing deviates too far from what your system will accept:
ls pg_000* > pdf.list
sed 's/\;.*$//' question.csv > name.list
If you need to clean up the name list, do it now. For example, you might need to remove spaces:
sed -i 's/ //g' name.list
Then create a file of mv commands. I'm doing it this way because if you have a lot of data, storing all the names as arguments is likely to exceed the capacity of your shell. This way, each data entry gets its own command.
paste -d ' ' pdf.list name.list | sed -e 's/^/mv /' -e 's/$/.pdf/' > cmds.list
Now you can run the commands with e.g. sh cmds.list.
This gives me three pdfs named Name.pdf, Name1.pdf and Name2.pdf.
Name.pdf is the dummy run:
Name1.pdf corresponds to the first data row:
and Name2.pdf to the second:
Obviously, this process can be tweaked in various ways and you can combine things in scripts etc. It can also be made more efficient, especially, I think, for the renaming. But the best way to do that probably depends on the details and hopefully this would give you a starting point if you end up using something like this workflow.
Best Answer
When you follow the advice of Joseph Wright and Andrew you could also use the
pgfplotstablepackage to print your CSV file. Because you didn't provide an example, I just copied the code from this answer. To see the result, see there.Regarding the filtering of the Excel table: Why not just filter the Excel table directly, for example using the
AutoFilterfeature, copy the result to a new table and just save that as CSV file?To copy just the "visible cells" have a look at this resource.