[Tugindia] Creating labels from excel sheets
Harish Kumar Holla
harishkumarholla at pec.edu
Wed Dec 17 13:15:31 CET 2014
I have added this as an answer at
http://tex.stackexchange.com/q/218017/11232
Here is some code that should serve as a starting point. This uses
`datatool` package. I have added a column with a short key for `center` in
addition to your columns.
center,RollNo,Subject,Venue
Bangalore,022701,Sociology,Deptt. of Physics
Bangalore,022702,Sociology,Deptt. of Physics
Bangalore,022704,Sociology,Deptt. of Physics
Bangalore,022705,Sociology,Deptt. of Physics
In fourth column, you may specify the full center if you want.
\documentclass[a4paper,12pt]{article}
\usepackage[left=.5cm,right=.5cm,top=.5cm,bottom=.5cm]{geometry}
\usepackage{xcolor}
\usepackage{filecontents}
\begin{filecontents*}{data.csv}
center,RollNo,Subject,Venue
Bangalore,022701,Sociology,Deptt. of Physics
Bangalore,022702,Sociology,Deptt. of Physics
Bangalore,022704,Sociology,Deptt. of Physics
Bangalore,022705,Sociology,Deptt. of Physics
Bangalore,022706,Sociology,Deptt. of Physics
Bangalore,022707,Sociology,Deptt. of Physics
Bangalore,022708,Sociology,Deptt. of Physics
Bangalore,022709,Sociology,Deptt. of Physics
Bangalore,022709,Sociology,Deptt. of Physics
Bangalore,022710,Sociology,Deptt. of Physics
Mangalore,023001,Psychology,Deptt. of Physics
Mangalore,023002,Psychology,Deptt. of Physics
Mangalore,023004,Psychology,Deptt. of Physics
Mangalore,023005,Psychology,Deptt. of Physics
Mangalore,023006,Psychology,Deptt. of Physics
Mangalore,023007,Psychology,Deptt. of Physics
Mangalore,023008,Psychology,Deptt. of Physics
Mangalore,023009,Psychology,Deptt. of Physics
Mangalore,023010,Psychology,Deptt. of Physics
\end{filecontents*}
\usepackage{datatool}
\DTLloaddb{data}{data.csv}
\begin{document}
This is for Bangalore center
\DTLforeach*[\DTLisopenbetween{\center}{B}{C}]{data}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\clearpage
This is for Mangalore
\DTLforeach*[\DTLisopenbetween{\center}{M}{N}]{data}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\clearpage
This is for Sociology
\DTLforeach*[\DTLisopenbetween{\sub}{S}{T}]{data}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\clearpage
This is for Psychology
\DTLforeach*[\DTLisopenbetween{\sub}{P}{Q}]{data}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\end{document}
Note that for large csv files, this may compile slowly. This can be cured
to some extent by using `pgfplotstable`. Here a large, single data file can
be filtered out for a particular center or subject and individual csv files
can be created on the fly. Once these are created, you can create the
labels using your `csvsimple` package way or using `datatool`. Here is the
sample code that uses the same `data.csv` as in the previous code.
\documentclass[a4paper,12pt]{article}
\usepackage[left=.5cm,right=.5cm,top=.5cm,bottom=.5cm]{geometry}
\usepackage{xcolor}
\usepackage{pgfplotstable,xstring}
\pgfplotsset{compat=1.11}
\usepackage{datatool}
\pgfplotstableread[col sep=comma]{data.csv}\data
\begin{document}
%%----------------------------------------------------------subjectwise
%% following will filter all Psychology rows and save it as
Psychology.csv
\pgfplotstablesave[col sep=comma,
row predicate/.code={%
\pgfplotstablegetelem{#1}{Subject}\of\data%
\IfStrEq{\pgfplotsretval}{Psychology}{%True %% >>imp<<
Psychology is case sensitive, psychology won't work
\relax}%
{\pgfplotstableuserowfalse}%False
}%
]
{\data}
{Psychology.csv}
%%----------------------------------------------------------
%% from here use datatool
\DTLloaddb{Psychology}{Psychology.csv}
\DTLforeach{Psychology}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\clearpage
%%---------------------------------------------------------- centerwise
%% following will filter all Bangalore rows and save it as bangalore.csv
\pgfplotstablesave[col sep=comma,
row predicate/.code={%
\pgfplotstablegetelem{#1}{center}\of\data%
\IfStrEq{\pgfplotsretval}{Bangalore}{%True %%
>>imp<< Bangalore is case sensitive
\relax}%
{\pgfplotstableuserowfalse}%False
}%
]
{\data}
{bangalore.csv}
%%----------------------------------------------------------
%% from here use datatool/csvsimple
\DTLloaddb{bangalore}{bangalore.csv}
\DTLforeach{bangalore}{%
\center=center,\roll=RollNo,\sub=Subject, \venue=Venue}{%
\noindent\fbox{\parbox{6.3cm}{%
{\color{white}.}\\[7pt]
\centering{\LARGE UGC NET - 2014}\\[2pt]
\raggedright
{\Large Subject : \sub}\\[5pt]
{\Large Roll No. : \textbf{\roll}}\\[12pt]
}}\hfill % This is for parbox
}%
\end{document}
On Tue, Dec 16, 2014 at 2:04 PM, bhutex <bhutex at gmail.com> wrote:
>
> Please find enclosed the Excel sheet containing 1000 records. It contains
> many fields - but what I require are - Roll No., Subject to be printed and
> it should be center wise - A center will contain minimum 2 subjects.
>
>
>
> On Mon, Dec 15, 2014 at 6:23 PM, Harish Kumar Holla <
> harishkumarholla at pec.edu> wrote:
>>
>> Thanks for the file but I meant the csv file (which you are planning to )
>> that combines data for different centers. Right now the csv file contains
>> no information on centers.
>>
>> On Thu, Dec 11, 2014 at 10:17 AM, bhutex <bhutex at gmail.com> wrote:
>>>
>>>
>>>
>>>
>>> Dear Sir
>>>
>>> The sample Excel Sheet is enclosed.
>>>
>>> Thanking you
>>>
>>> On Wed, Dec 10, 2014 at 5:53 PM, Harish Kumar Holla <
>>> harishkumarholla at pec.edu> wrote:
>>>
>>>> Can you send a sample csv file?
>>>>
>>>> On Wed, Dec 10, 2014 at 5:20 PM, bhutex <bhutex at gmail.com> wrote:
>>>>
>>>> > We have to create desk slips for conducting an examination. The desk
>>>> slip/
>>>> > label will have the following information :
>>>> >
>>>> > ...... examination - 2014
>>>> > Subject : xyz
>>>> > Roll No. 0293993
>>>> >
>>>> > The data of RollNo, Subject and Venue of examination (examination
>>>> center)
>>>> > are maintained in an excel sheet - nearly 40 examination centres and
>>>> 80
>>>> > subjects total candidates about 20 thousand.
>>>> >
>>>> > I created desk slip with the use of csvtools and the code is given
>>>> below :
>>>> >
>>>> > \documentclass[a4paper,12pt]{article}
>>>> >
>>>> > \usepackage{csvtools}
>>>> > \usepackage[left=.5cm,right=.5cm,top=.5cm,bottom=.5cm]{geometry}
>>>> > \usepackage{multicol}
>>>> > \usepackage{xcolor}
>>>> >
>>>> > \begin{document}
>>>> >
>>>> > \applyCSVfile{data.csv}{% here put the name of the file
>>>> > %\begin{multicols}{2}
>>>> > \noindent\fbox{\parbox{6.3cm}{%
>>>> > {\color{white}.}\\[7pt]
>>>> > \centering{\LARGE UGC NET - 2014}\\[2pt]
>>>> > \raggedright
>>>> > {\Large Subject : \insertSubject}\\[5pt]
>>>> > {\Large Roll No. : \textbf{0\insertRollNo}}\\[12pt]
>>>> > }} % This is for parbox
>>>> > %\end{multicols}
>>>> > }% This is for CSV file
>>>> >
>>>> >
>>>> > \end{document}
>>>> >
>>>> > With the above my purpose is solved. But for this for every subject
>>>> and
>>>> > centre we have to create separate csv files.
>>>> >
>>>> > Is there any better to get the lables printed - centre wise/ subject
>>>> wise
>>>> > from a single csv file without creating many csv files. It will be
>>>> better
>>>> > If we can print 8 x 3 lables on a4paper.
>>>> >
>>>> > --
>>>> > Happy (La)TeXing
>>>> > The BHU TeX Group
>>>> > क्या आप यह देख पा रहें हैं।
>>>> > इस का मतलब आप का कम्प्यूटर यूनीकोड
>>>> > को समझती है। देर किस बात की हिन्दी मे
>>>> > चिठ्ठियां लिखिये।
>>>> > _______________________________________________
>>>> > Home: http://www.tug.org.in/
>>>> > TeX FAQ: http://www.tex.ac.uk/faq
>>>> > unsubscribe: http://tug.org/mailman/listinfo/tugindia
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> with regards
>>>> =========================
>>>> Dr. Harish Kumar
>>>> Dept. of Physics
>>>> Pondicherry Engineering College
>>>> Puducherry 605 014
>>>> Ph: 0413 2655281-286 (Extn: 646)
>>>> ---------------------------------------------
>>>> Residence:
>>>> Dr. Harish Kumar
>>>> # 29 (New No.) I Main Cluny Road
>>>> Avvai Nagar, Lawspet
>>>> Puducherry 605 008
>>>> Ph: 0413 2253652, 9443052957
>>>> =========================
>>>> _______________________________________________
>>>> Home: http://www.tug.org.in/
>>>> TeX FAQ: http://www.tex.ac.uk/faq
>>>> unsubscribe: http://tug.org/mailman/listinfo/tugindia
>>>
>>>
>>>
>>>
>>> --
>>> Happy (La)TeXing
>>> The BHU TeX Group
>>> क्या आप यह देख पा रहें हैं।
>>> इस का मतलब आप का कम्प्यूटर यूनीकोड
>>> को समझती है। देर किस बात की हिन्दी मे
>>> चिठ्ठियां लिखिये।
>>>
>>>
>>>
>>>
>>> --
>>> Happy (La)TeXing
>>> The BHU TeX Group
>>> क्या आप यह देख पा रहें हैं।
>>> इस का मतलब आप का कम्प्यूटर यूनीकोड
>>> को समझती है। देर किस बात की हिन्दी मे
>>> चिठ्ठियां लिखिये।
>>>
>>>
>>
>> --
>> with regards
>> =========================
>> Dr. Harish Kumar
>> Dept. of Physics
>> Pondicherry Engineering College
>> Puducherry 605 014
>> Ph: 0413 2655281-286 (Extn: 646)
>> ---------------------------------------------
>> Residence:
>> Dr. Harish Kumar
>> # 29 (New No.) I Main Cluny Road
>> Avvai Nagar, Lawspet
>> Puducherry 605 008
>> Ph: 0413 2253652, 9443052957
>> =========================
>>
>
>
> --
> Happy (La)TeXing
> The BHU TeX Group
> क्या आप यह देख पा रहें हैं।
> इस का मतलब आप का कम्प्यूटर यूनीकोड
> को समझती है। देर किस बात की हिन्दी मे
> चिठ्ठियां लिखिये।
>
>
--
with regards
=========================
Dr. Harish Kumar
Dept. of Physics
Pondicherry Engineering College
Puducherry 605 014
Ph: 0413 2655281-286 (Extn: 646)
---------------------------------------------
Residence:
Dr. Harish Kumar
# 29 (New No.) I Main Cluny Road
Avvai Nagar, Lawspet
Puducherry 605 008
Ph: 0413 2253652, 9443052957
=========================
More information about the tugindia
mailing list