Import data from a spreadsheet into latex and create multiple pdf files for each row in LaTeX

I have a form which I assign to people to fill them out as a google form, and get the responses in a spreadsheet (xlsx or ods file formats). I need to file a report based on their answers, which means I have to generate a pdf file for each row of the spreadsheet. Here is an example. Imagine this is the first three rows of the soreadsheet:

Name Question1 Question2 Question3
Name1 Answer1.1 Answer2.1 Answer 3.1
Name2 Answer1.2 Answer2.2 Answer 3.2


What I need to get is two pdf files named Name1.pdf and Name2.pdf which first one contains something like this:

Name: Name1
Question1: Answer1.1
Question2: Answer2.1
Question3: Answer3.1


And so on. I was thinking that one might write a script in python and read each row of the file and give it as an input to pdflatex command on a template file, but I don't have much ideas on how to really start on this.

-
What I would do is export the files to .csv format, transform them with a script to replace field separators with newlines, and use textmerg to process the data file. For example, I export with ; as field separator and nothing around text to .csv. Then gawk 'BEGIN { RS=";"; ORS="\n" } { print }' input.csv > output.dat prepares the file for merge. This will standardly give you a single pdf but either you could adjust this (I think) or you can 'burst' the pdf using something like pdftk. I don't know this is the best way, though. It is probably a function of what I already know how to do! –  cfr Feb 1 at 21:21
Something like (in bash) split -l 1 myfile.csv name && sed -i 's/;/\n/g' name* will do: (1) split each line of your .csv file into a new file with the prefix namea (thus, your three lines would become: nameaa nameab and nameac); (2) sed takes each instance of ; and replaces it with a newline (the \n). This gives you the files broken down into discrete pieces which you can latex in which ever way you choose. Note that the ; delimiter needs to be chosen in your export to .csv –  jon Feb 1 at 22:25

1 Answer

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.

-
Interesting. I think I'd split the .csv first: it should simplify the process. (No need to use pdftk, e.g.) –  jon Feb 1 at 22:27
@jon True. The merging stage is more complex in that case, though, but it would definitely simplify things later. This is actually something I use when I don't want separate pdfs so, as I said above, it isn't necessarily the best solution here. More a function of what I already know how to do! –  cfr Feb 1 at 22:31
Hmm, true. I've never used textmerg so I didn't think about that. I suppose there must be a way to \input a file as a variable... –  jon Feb 1 at 22:42
@jon Probably. I'm just not sure how to do that. At least, I am not sure how to do it at all tidily. (I can think of ways to do it with a shell script, for example, but it is getting very messy.) textmerg is great. Whether it should be used here I'm not sure, but it is a fantastic tool. For example, I'll write or export student feedback to a data file, merge it into a template and then print the resulting file. In that case, I don't want 40 or 60 pdfs. I just need to split them once printed. textmerg is perfect for that. –  cfr Feb 1 at 23:15
Thank you very much @cfr It worked like a charm. The only thing that I needed to take care of manually was the "new line"s that people had in their responses to a question. I think there should be a way to do that also automatically, before converting it to CSV. Thanks again. –  Keivan Apr 5 at 20:05