Re: Creating labels from excel sheets

Also since I had difficulty converting the xslx file to csv, I have used a
simple data file.

On Wed, Dec 17, 2014 at 5:45 PM, Harish Kumar Holla <
[email protected]> wrote:
>
> 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 <[email protected]> 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 <
>> [email protected]> 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 <[email protected]> wrote:
>>>>
>>>>
>>>>
>>>>
>>>> Dear Sir
>>>>
>>>> The sample Excel Sheet is enclosed.
>>>>
>>>> Thanking you
>>>>
>>>> On Wed, Dec 10, 2014 at 5:53 PM, Harish Kumar Holla <
>>>> [email protected]> wrote:
>>>>
>>>>> Can you send a sample csv file?
>>>>>
>>>>> On Wed, Dec 10, 2014 at 5:20 PM, bhutex <[email protected]> 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
> =========================
>


-- 
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