Datatool and multiple files

Question

I am trying to achieve the same goal as I tried with pgfplotstable as in this question for which I got good help from the author of pgfplotstable (Dr. Christian Feuersänger) himself. Hence this question may be considered to be of academic interest to me.

I have two csv files: names.csv

Sl. No., Reg. No.,Name,Category,Group,Gate,Sponsored,Department
1,MS001,Ajay-D-Vimal Raj P,PY,OBC,--,No,Physics
2,MS002,Harish Kumar,PY,GE,GATE,Yes,Physics
3,MS003,Ajay-D-Vimal Raj P,PY,OBC,--,No,Physics
4,MS004,Harish Kumar,PY,GE,GATE,Yes,Physics

and marks.csv

number,marks
MS001,67
MS002,25
MS003,62
MS004,55

Requirement: I want to print the first file (names.csv) into a long table in which, I would like to include the second column (i.e., marks column) of marks.csv, as the column coming after the names column (i.e., as the fourth column).

Here is the MWE (working, but not as I desired)

\documentclass[10pt]{article}
\usepackage[a4paper,margin=2cm]{geometry}
\usepackage{longtable}
\usepackage{datatool}
% \usepackage{filecontents}
%
% \begin{filecontents}{names.csv}
% serial,number,name,group,category,gate,sponsored,dept
%     1,MS001,Ajay-D-Vimal Raj P,PY,OBC,--,No,Physics
%     2,MS002,Harish Kumar,PY,GE,GATE,Yes,Physics
%     3,MS003,Ajay-D-Vimal Raj P,PY,OBC,--,No,Physics
%     4,MS004,Harish Kumar,PY,GE,GATE,Yes,Physics
% \end{filecontents}

% \begin{filecontents}{marks.csv}
%     number,marks
%     MS001,67
%     MS002,25
%     MS003,62
%     MS004,55
% \end{filecontents}
%
\begin{document}
\DTLloaddb{names}{names.csv}
\DTLloaddb{marks}{marks.csv}                       
% ----------------------------------------------------------------------%
% ----------------------------------------------------------------------%
{\small
\begin{longtable}{|c|l|p{3.5cm}|c|l|c|c|c|c|}\hline
% -----------------These are headings----------------------------------%
\textbf{} & \textbf{Reg. No.} &  \multicolumn{1}{c|}{\textbf{Name}} & \textbf{Marks}&
\multicolumn{1}{c|}{\textbf{Category}}  & \textbf{GATE} & \textbf{Sponsored} &\textbf{Department} \\ \hline
%
\endfirsthead
%
\multicolumn{8}{c}%
{{\bfseries  Continued from previous page}} \\
\hline
%
\textbf{} & \textbf{Reg. No.} & \multicolumn{1}{c|}{\textbf{Name}} & \textbf{Marks}&
\multicolumn{1}{c|}{\textbf{Category}}  & \textbf{GATE} & \textbf{Sponsored}&\textbf{Department} \\ \hline\hline
\endhead
%
\hline \multicolumn{8}{|r|}{{Continued on next page}} \\ \hline
\endfoot
%
\hline
\multicolumn{8}{|r|}{{Concluded}} \\ \hline
\endlastfoot
%-----------Headings end---------------------------------
%--------------------------table body starts-------------------
\DTLforeach{names}{%
\sl=serial, \reg=number,\name=name, \group=group, \category=category, \gate=gate,\sponsored=sponsored,\dept=dept}{%
\DTLiffirstrow{}{\\\hline}%  
% \DTLiflastrow{\dtlbreak}{}
% \DTLiffirstrow{\dtlbreak}{}
 \DTLforeach{marks}{%
 \marks=marks}{%
 \DTLiffirstrow{}{\\}%
%  \DTLiffirstrow{\dtlbreak}{}
% \DTLiflastrow{\dtlbreak}{}
\sl & \reg & \name & \marks & \group/\category & \gate & \sponsored & \dept
%
}%
}%
%--------------------------table body ends-------------------
\end{longtable}
}%
%
\end{document}

But somehow I am not able to get the looping correct and every name is repeated 4 times.

Questions:

1. How to get the corresponding mark from marks.csv file and print it. (get the \DTLforeach correctly?)
2. How to sort the resulting table in the increasing order of marks while keeping only the serial numbers in the first column untouched. (All other columns should be sorted as per their corresponding marks)?

Any more clarifications will be gladly obliged.

Answer 1

Although LaTeX is not designed as a DBMS and it is thus really ugly doing a join with it, using a match on the second \DTLforeach you will get the desired result:

\DTLforeach[\DTLiseq{\subnum}{\reg}]{marks}{%
 \subnum=number, \marks=marks}{%

But I strongly recommend using a dedicated DBMS to generate the table you like and only as a final step reading it into LaTeX. That way you won't need any such nested loops.

As for the second question the datatool documentation tells us in section 5.8 how to do it:

\DTLsort{number}{names}

just anywhere between loading the names db and displaying the data will do. Keeping the serials in their original order does not make sense to me. But if that is what you need, I would save the order before sorting and later read that. But if you only want a consecutive numbering of the rows, you can use a counter. Define it with \newcounter{row} and print every row with:

 \therow\refstepcounter{row}
 & \reg & \name & \marks & \group/\category & \gate & \sponsored & \dept