# Rendering CSV data in tables which do not fit in paper width

I have to generate a number of tables from CSV data which span a couple of hundred pages or more. So, I need some kind of automation for this.

Please note the following salient points:

1. The data has two main parts in each row, some kind of name in the first column followed by data in the rest of the columns.
2. The number of columns in the CSV data varies from one table to another. But the number remains fixed in a single table.
3. The data is generated automatically in a software application. The number of rows in a table is variable and may span multiple pages. Hence, I need to use longtable.
4. A variable number of columns are grouped under the main categories. That is the number of sub-categories under each category is not fixed.
5. Since the application generating this data is also local (own-built), the data format can be customized (up to a certain extent).
6. The number of rows in a table will vary from table to table and may (vertically) exceed one page.
7. A number of leftmost columns will have to be repeated when the table is split horizontally.

The CSV file will look something like this.

Name,4:Category 1,3:Category 2,6:Category 3,8:Category 4,5:Category 5,7:Category 6
Sub-category,Sc11,Sc12,Sc13,Sc14,Sc21,Sc22,Sc23,Sc31,Sc32,Sc33,Sc34,Sc35,Sc36,Sc41,Sc42,Sc43,Sc44,Sc45,Sc46,Sc47,Sc48,Sc51,Sc52,Sc53,Sc54,Sc55,Sc61,Sc62,Sc63,Sc64,Sc65,Sc66,Sc67
Name 1,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67
Name 2,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67


Each number, m preceding the Category n indicates the number of sub-categories under that category. As such, 4:Category 1 indicates that there are 4 sub-categories under Category 1.

Though the above one is more or less a pure CSV input, putting some kind of predefined macro command around each row, and an environment around the whole table is also possible. So, the input for one table could be like:

\begin{csvwidetable}{1}
\csvwidetableheader{Name,4:Category 1,3:Category 2,6:Category 3,8:Category 4,5:Category 5,7:Category 6}
\csvwidetablerow{Name 1,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67}
\csvwidetablerow{Name 2,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67}
\end{csvwidetable}


Here 1 as environment argument indicates that 1 left most column will have to be repeated.

Please see the code below which I believe should be able to explain the effect I am trying to achieve.

\begin{document}

\newlength{\ncw}
\setlength{\ncw}{10.00mm}

If we resort to putting each row of CSV in a single row of the table,
it will not fit. Moreover, we will need to find a way to put those
headers like Category 1 over multiple columns.

\begin{longtable}[l]{|l|*{33}{C{\ncw}|}}
\hline
Name&\multicolumn{4}{|c|}{Category 1}&\multicolumn{3}{c|}{Category 2}&\multicolumn{6}{c|}{Category 3}&\multicolumn{8}{|c|}{Category 4}&\multicolumn{5}{c|}{Category 5}&\multicolumn{7}{c|}{Category 6}\\\hline
Sub-category&Sc11&Sc12&Sc13&Sc14&Sc21&Sc22&Sc23&Sc31&Sc32&Sc33&Sc34&Sc35&Sc36&Sc41&Sc42&Sc43&Sc44&Sc45&Sc46&Sc47&Sc48&Sc51&Sc52&Sc53&Sc54&Sc55&Sc61&Sc62&Sc63&Sc64&Sc65&Sc66&Sc67\\\hline
\hline
\endfoot
Name 1&11&12&13&14&21&22&23&31&32&33&34&35&36&41&42&43&44&45&46&47&48&51&52&53&54&55&61&62&63&64&65&66&67\\\hline
Name 2&11&12&13&14&21&22&23&31&32&33&34&35&36&41&42&43&44&45&46&47&48&51&52&53&54&55&61&62&63&64&65&66&67\\
\end{longtable}

As of this moment, we employ some  pre-fixed heuristics in the
application to break columns at  convenient points. We need to
repeat the left column. See below for examples.

In this table, I can accommodate up to Category 3.

\begin{longtable}[l]{|l|*{13}{C{\ncw}|}}
\hline
Name&\multicolumn{4}{|c|}{Category 1}&\multicolumn{3}{c|}{Category 2}&\multicolumn{6}{c|}{Category 3}\\\hline
Sub-category&Sc11&Sc12&Sc13&Sc14&Sc21&Sc22&Sc23&Sc31&Sc32&Sc33&Sc34&Sc35&Sc36\\\hline
\hline
\endfoot
Name 1&11&12&13&14&21&22&23&31&32&33&34&35&36\\\hline
Name 2&11&12&13&14&21&22&23&31&32&33&34&35&36\\
\end{longtable}

The rest (Category 4--Category 6) goes in this table.

\begin{longtable}[l]{|l|*{20}{C{\ncw}|}}
\hline
Name&\multicolumn{8}{|c|}{Category 4}&\multicolumn{5}{c|}{Category 5}&\multicolumn{7}{c|}{Category 6}\\\hline
Sub-category&Sc41&Sc42&Sc43&Sc44&Sc45&Sc46&Sc47&Sc48&Sc51&Sc52&Sc53&Sc54&Sc55&Sc61&Sc62&Sc63&Sc64&Sc65&Sc66&Sc67\\\hline
\hline
\endfoot
Name 1&41&42&43&44&45&46&47&48&51&52&53&54&55&61&62&63&64&65&66&67\\\hline
Name 2&41&42&43&44&45&46&47&48&51&52&53&54&55&61&62&63&64&65&66&67\\
\end{longtable}

\end{document}


Here is the output from the above code.

So, what we are trying to achieve is to generate long tables from CSV data, break columns at convenient columns when the width exceeds text width (without breaking at mid of any Category n), and (optionally) repeat some the left-most column.

(Please ignore the issue of vertical lines. I know that vertical lines in table did not have much good time in this forum.)

• – Dr. Manuel Kuehner Jan 19 '18 at 6:40
• @Dr.ManuelKuehner No, the solution provided there manually splits the table into two tables. For my more than hundred tables, I am looking for a solution which splits at convenient points when the width exceeds the current text width. – Masroor Jan 19 '18 at 8:18
• Yes, it's related but not a duplicate. I wanted to provide a small contribution after you did not get any response for over 12 hours. Nothing more. And maybe the manual solutions activate your creativity and help to find a solution that fits your needs. – Dr. Manuel Kuehner Jan 19 '18 at 8:52
• You should imho do it with boxes and not with tabular or longtable. – Ulrike Fischer Jan 21 '18 at 18:31
• One possible solution is read the CSV file as a data frame in R, were with the xtable, kable or similar packages can export to nice latex tables of selected columns and rows (also in longtable format at least withxtable ) Code it all in the own LateX source code with knitr is straightforward. The main problem are the main multicolumns categories that do not fit the structure of a data frame, but they can be added to a particular sub data frame. – Fran Jan 24 '18 at 10:38

Here's a possible solution. It uses datatooltk to split off the first line from the CSV file. Either run datatooltk before LaTeX or run LaTeX with -shell-escape. (Version 1.8 is needed for the option --csv-skiplines.)

Assumptions:

1. The first column (Name) doesn't have sub-categories and always appears at the start of each table block.
2. The number of rows doesn't exceed the page height. (Replace tabular with longtable if that occurs.)

MWE:

\documentclass{article}

\usepackage[a4paper]{geometry}
\usepackage{datatool}

\immediate\write18{datatooltk --name datablocks --csv testdata.csv --nocsv-header --truncate 1 -o datablocks.dbtex}
\immediate\write18{datatooltk --name data --csv testdata.csv --csv-header --csv-skiplines 1 -o data.dbtex}

\def\blockspan{#1}%
}

\newcount\blockidx
\newcount\colidx

% First column is the name, which is a special case.

\blockidx=1

\DTLgetvalue{\currentvalue}{\datablocks}{1}{\blockidx}
\csdef{blockrange\number\blockidx}{1}
\csdef{blockspan\number\blockidx}{1}

\colidx=1

\loop
\ifnum\blockidx>\DTLcolumncount\datablocks
\else
\DTLgetvalue{\currentvalue}{\datablocks}{1}{\blockidx}%
\ifdefempty\currentvalue
{% empty columns caused by discrepancy between column count on
% line 1 of CSV being less than column count of remaining lines
\edef\totalblocks{\number\numexpr\blockidx-1}%
% break loop
\blockidx=\DTLcolumncount{\datablocks}%
}%
{%
\cslet{blockspan\number\blockidx}{\blockspan}%
\edef\endrange{\number\numexpr\colidx+\blockspan}%
\csedef{blockrange\number\blockidx}{\number\numexpr\colidx}%
{%
\loop
\csxappto{blockrange\number\blockidx}{,\number\numexpr\colidx}%
\ifnum\colidx<\endrange
\repeat
}%
}%
\fi
\ifnum\blockidx<\DTLcolumncount{\datablocks}
\repeat

\makeatletter
% iterate over columns in given block
\newcommand*{\forblock}[2]{%
\letcs{\rangelist}{blockrange\number#1}%
\@for\thiscol:=\rangelist\do{#2}%
}

% iterate over columns in given table
\newcommand*{\fortableblock}[2]{%
\letcs{\blockrangelist}{blocktablecolspan\number#1}%
\@for\thiscol:=\blockrangelist\do{#2}%
}

% iterate over blocks in given table
\newcommand*{\fortableblockrange}[2]{%
\letcs{\blockrangelist}{blocktablerange\number#1}%
\@for\thisblock:=\blockrangelist\do{#2}%
}
\makeatother

% find maximum widths (including \tabcolsep)

\newcommand*{\defandsetlength}[2]{%
\global\newlength{#1}%
\global\setlength{#1}{#2}%
}

\newcount\rowidx

% in case header needs to use a different font:

\newcommand{\computeblockwidths}{%
{%
\dimen0=\dimexpr\dimen0+2\tabcolsep\relax
\expandafter\defandsetlength
\csname columnwidth\number\thiscol\endcsname{\dimen0}%
}%
\loop
{%
\blockidx = 0\relax
\loop
\forblock{\blockidx}{%
\DTLgetvalue{\currentvalue}{\data}{\rowidx}{\thiscol}%
\settowidth{\dimen0}{\currentvalue}%
\dimen0=\dimexpr\dimen0+2\tabcolsep\relax
\ifcsdef{columnwidth\number\thiscol}
{%
\ifnum\dimen0>\csname columnwidth\number\thiscol\endcsname
\csname columnwidth\number\thiscol\endcsname=\dimen0
\fi
}%
{%
\expandafter\defandsetlength
\csname columnwidth\number\thiscol\endcsname{\dimen0}%
}%
}%
\ifnum\blockidx<\totalblocks
\repeat
}%
\ifnum\rowidx<\DTLrowcount{\data}
\repeat
% compute block widths
\blockidx=0
\loop
\expandafter\newlength\csname blockwidth\number\blockidx\endcsname
\forblock{\blockidx}{%
\csname columnwidth\number\thiscol\endcsname
}%
% check if block headers are wider
\settowidth{\dimen0}%
\dimen0=\dimexpr\dimen0+2\tabcolsep\relax
\ifdim\dimen0>\csname blockwidth\number\blockidx\endcsname
\csname blockwidth\number\blockidx\endcsname=\dimen0\relax
\fi
\ifnum\blockidx<\totalblocks
\repeat
}

% create table code

\newlength\currentwidth
\newcount\currenttable
\newcount\maxtables

\newcommand{\createtable}[1]{%
\csgdef{blocktablecolspec\number#1}{l}%
\csxdef{blocktablecolspan\number#1}{\csuse{blockrange1}}%
\csxdef{blocktablerange\number#1}{1}%
}

\newcommand{\dotable}{%
\computeblockwidths
% first column always present
\global\currentwidth=\csname blockwidth1\endcsname
\global\currenttable=1\relax
\createtable{1}%
% loop over remaining blocks
\blockidx=1
\loop
\global\currentwidth=\dimexpr\currentwidth
+\csname blockwidth\number\blockidx\endcsname\relax
\relax
\ifdim\currentwidth>\linewidth
\global\currentwidth=\dimexpr\csname blockwidth1\endcsname
+\csname blockwidth\number\blockidx\endcsname\relax
\createtable\currenttable
\fi
\csgappto{blocktablecolspec\number\currenttable}{|}%
{%
\colidx=0
\loop
\csgappto{blocktablecolspec\number\currenttable}{l}%
\ifnum\colidx<\csname blockspan\number\blockidx\endcsname
\repeat
}%
\csxappto{blocktablecolspan\number\currenttable}%
{,\csuse{blockrange\number\blockidx}}%
\csxappto{blocktablerange\number\currenttable}{,\number\blockidx}%
\ifnum\blockidx<\totalblocks
\repeat
% save table count
\maxtables=\currenttable
% create table code
\currenttable=0\relax
\loop
% tabular setup
\def\currenttablecode{\begin{tabular}}%
\eappto\currenttablecode{%
{|\csname blocktablecolspec\number\currenttable\endcsname|}%
\noexpand\hline}%
\fortableblockrange{\currenttable}{%
\ifnum\thisblock>1
\appto\currenttablecode{&}%
\fi
\eappto\currenttablecode{%
\noexpand\multicolumn
{\csname blockspan\number\thisblock\endcsname}
{|c|}%
{%
}%
}%
}%
\appto\currenttablecode{\\}%
\fortableblock{\currenttable}{%
\ifnum\thiscol>1
\appto\currenttablecode{&}%
\fi
}%
% table body
{%
\rowidx=0
\loop
\gappto\currenttablecode{\\\hline}%
\fortableblock{\currenttable}{%
\DTLgetvalue{\currentvalue}{\data}{\rowidx}{\thiscol}%
\ifnum\thiscol>1
\gappto\currenttablecode{&}%
\fi
\xappto\currenttablecode{\expandonce\currentvalue}%
}%
\ifnum\rowidx<\DTLrowcount{\data}
\repeat
}%
% tabular end
\appto\currenttablecode{\\\hline\end{tabular}}%
% do table
\bigskip\par\noindent\currenttablecode
\ifnum\currenttable<\maxtables
\repeat
}

\begin{document}

\dotable

\end{document}


Result:

This is a partial solution because it doesn't automate the building of the headers, but I think it could be useful because, at least, you don't have to re-type your data.

If you manage to put a # or a % before the first row of your .csv, you could try this:

\documentclass{article}

\usepackage{longtable}
\usepackage{array}
\newcolumntype{C}[1]{>{\centering\arraybackslash\hspace{0pt}}p{#1}}

\usepackage[legalpaper,landscape,left=25.0mm,right=25.0mm]{geometry}

\parindent 0.0mm

\usepackage{pgfplotstable}
\pgfplotsset{compat=1.14}
\usepackage{filecontents}

% The following code lines are added only to create myfile.csv. Of course, you don't need them in your code
\begin{filecontents*}{myfile.csv}
#Name,4:Category 1,3:Category 2,6:Category 3,8:Category 4,5:Category 5,7:Category 6
Sub-category,Sc11,Sc12,Sc13,Sc14,Sc21,Sc22,Sc23,Sc31,Sc32,Sc33,Sc34,Sc35,Sc36,Sc41,Sc42,Sc43,Sc44,Sc45,Sc46,Sc47,Sc48,Sc51,Sc52,Sc53,Sc54,Sc55,Sc61,Sc62,Sc63,Sc64,Sc65,Sc66,Sc67
Name 1,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67
Name 2,11,12,13,14,21,22,23,31,32,33,34,35,36,41,42,43,44,45,46,47,48,51,52,53,54,55,61,62,63,64,65,66,67
\end{filecontents*}
% end code to create myfile.csv

\begin{document}

\newlength{\ncw}
\setlength{\ncw}{10.00mm}

\pgfplotstabletypeset[
begin table={\begin{longtable}},
\hline
Name&\multicolumn{4}{c|}{Category 1}&\multicolumn{3}{c|}{Category 2}&\multicolumn{6}{c|}{Category 3}\\
\hline
},
},
after row=\hline,
end table={\end{longtable}},
every first column/.style={column type={|l|}},
every column/.style={column type={C{\ncw}|}},
columns={[index]0,[index]1,[index]2,[index]3,[index]4,[index]5,
[index]6,[index]7,[index]8,[index]9,[index]10,[index]11,
[index]12,[index]13},
string type
]{\mytable}

\pgfplotstabletypeset[
begin table={\begin{longtable}},
\hline
Name&\multicolumn{8}{|c|}{Category 4}&\multicolumn{5}{c|}{Category 5}&\multicolumn{7}{c|}{Category 6}\\
\hline
},
},
after row=\hline,
end table={\end{longtable}},
every first column/.style={column type={|l|}},
every column/.style={column type={C{\ncw}|}},
columns={[index]0,[index]14,[index]15,[index]16,[index]17,[index]18,
[index]19,[index]20,[index]21,[index]22,[index]23,[index]24,
[index]25,[index]26,[index]27,[index]28,[index]29,[index]30,[index]31,
[index]32,[index]33},
string type
]{\mytable}
\end{document}


• How does your solution takes care of, 1. Automatic splitting of table at appropriate column? I see that you are manually putting Category 4-6 in the second table. 2. Variable number of columns in the CSV file? The total number of data columns in not always 33. It may be more, it may be less. – Masroor Jan 19 '18 at 8:16
• @Masroor I said this is only a partial solution, just to avoid to retype the data. I'm afraid a complete solution is above my level of knowledge. Maybe you could start a bounty, since I saw nobody else answered. – CarLaTeX Jan 19 '18 at 8:22