The SCANCSV.LUA library

1. The SCANCSV.LUA library Jaroslav Hajtmar

2. ApologyEnglish-speakingparticipants Sorry, but this talk is only in Czech. Due to mylanguage skills I would probably just did not know enough to say everything important. I will try to at least the guide slideshow in English. Thanks for your understanding.

3. Abstract In the data processing are often used data, stored in CSV (Comma Separated Values) files. The presentation will describe the author's library ScanCSV.lua, the method of its formation and will be demonstrated practical examples of its use in ConTeXt MKIV. Author shows how easily and quickly create print reports, letters, forms, certificates, invitations, cards, business cards, double-sided cards, tables, animations etc. using external texts CSV databases.  Users of ConTeXt MKIV (but LuaLATEX and LuaTEX too) can through the library practicaly use data from external CSV tables in own documents through TeX macros built on library and have this data available in an attractive and very simple and natural way.

4. Introduction • SCANCSV.LUA library – easy way to use text database data stored in external CSV files in ConTeXt MkIV (in LuaLaTeX and LuaPlain is working too). • Easily create documents LuaTeX which handle multiple data (CSV simple database). • Varied uses: printing of various forms, collective letters, certificates, invitations, cards, business cards, double-sided cards, tables, animations etc. • Main objective : easy to use without knowledge of Lua, use in LuaLaTeX and LuaPlainTeX too, access CSV data by TeX macros built in library functions (without Lua code), motivate other users to use LuaTeX.

5. CSV data format and SCANCSV.LUA • Exchange data, export to CSV (f.e. the MySQL database), a simpler alternative to the XLM, easy handling (sorting and editing), spreadsheets (Excel, Calc, Gnumeric, ...) • Description CSV format generally • CSV format suitable for SCANCSV.LUA: • file must be encoded in UTF-8! (Exported XLS files to be recoded – handicap) • Field separators: basically anything, default value is ; semi-colon (MS Excel) • Spacers fields: anything, left and right may be different (most often "quotes), the default value is without spacers! • The parsing algorithm SCANCSV.LUA is very simple (although it can be freely adjusted) => limitation (if set spacers must be used everywhere - in general, it needs not to be)

6. SCANCSV – history, inspiration • 2005 - appearance macro scanbase.tex of PetrOlšák. Macroprocessing text files in a certain format. • PetrOlšák modify and generalized the macro scanbase.tex to macro scancsv.tex – itprocessing text files in CSV format. I used it in plainTeX to 2008. • 2008 - modificationofmacroforLaTeX (Jaromír Kuben) andforConTeXt (Petr Olšák). I usedit in ConTeXtMkIItoday. • 2010 - I began to use ConTeXtMkIV. Originalmacrodoes not work. ConTeXtisworkingwithcharacter set UTF8, but macro is unable to process thischaracter set. • March 2010 – my familiarization with LuaTeX, Lua language and I start creating the libraryscancsv.lua. First version was practicaly useless. • July 2010 – firstrealapplicableversion • today – improvements, tuningandexpansionofoptions

7. The operating principle of the library • Load library scancsv.lua (single Lua code in the source text, context). • Optional settings flag header, separator elements, and spacers (Otherwise, the default value). • Opening CSV file (different ways). • Loading CSV table row (manually or in a cycle) • Parse row (column separation data). • Retrieving column data to TeX macros. • Repeat steps 4 to 6 for all lines of CSV tables.Method of processing of first row of the table depends on whether it's "head" or not. After loading the column data in the macro data are available ConTeXt. Rows can browse the "manually", using the standard cycles or macros of library.

8. Using in the "manual" mode • Load library\directlua{dofile(scancsv.lua)} • Setting a flag header (when the head) \setheader (or unset - \resetheader) • Opening CSV file \opencsvfile{file.csv} • Then, in source text, we use the macros \cA, \cB ... (or \Firstname, \Lastname, ... if line first line contains header Firstname, Lastname, …). These macros contains the column values of the current CSV row • \Nextrow - go to the next table row (macro \cA, \cB ... or \Firstname, \Lastname, … are filled with new values)

9. Main TeX macros for using the library • \setfiletoscan{CSVFile} – setting of name of CSV file • \setheader– set a flag header • \resetheader – unset a flag header • \setsep{,}, \setld{*}, \setrd{!} – setting of separator of columns and spacers of columns to user value (nondafault value) • \resetsep, \resetld, \resetrd– unset to default values • \opencsvfile{CSVFile}, \openheadercsvfile{CSVFile} - • \nextrow – go to to next row of CSV file • \printline, \printall – print all of line / all of CSV table • \filelineaction, \filelineaction{CSVfile},\filelineaction{CSVfile}{to}, \filelineaction{CSVfile}{from}{to} –macros for processing of user-defined macro \lineaction in a cycle

10. TeXmacrosforaccessingof columns data 1;Petr;Novák;19.5.1989;m;Nymburk;U Brány 72;Jan;Novotný;5.7.1991;m;Praha;Uhlířská 1783;Zuzana;Vašíčková;13.9.1984;ž;Ostrava;Jánská 14… CSV file without Header (default option - \resetheader) \cA \cB \cC \cD … \resetheader Possibility setof Roman numbers of columns:\cI, \cII, \cIII, \cIV, … (defalut UserColumnNumbering=‘XLS’) no header data lines CSV file with Header (switch with \setheader) \cA = \Surname \cB = \Firstname \cC = \Birthdate … \setheader Header (no data) Surname;Firstname;Birthdate;Sex;City;Zipcode;Street Novák;Jan;14.10.1997;m;Zbečno;27024;Farní 21 Pospíšilová;Hana;4.1.1996;ž;Zábřeh;78901;Studénky 420 … data lines

11. TeX macros to obtain „system“ information • \csvfilename – name of actual open CSV file • \numcols – number of columns of the CSV table • \numrows – number of processed (offered) lines • \numline – the serial number of the currently loaded row • \csvreport – Report information on open CSV file Hooksfor data processing(default \relax) • \blinehook, \elinehook – begin line hook,end l.h. – macros are executed before and after processing row macro \lineaction (ie CSV table row) • \bfilehook, \efilehook – performed before and after processing the entire CSV table • \bch, \ech – begin column hook, end c.h. - can be manually set in lua code, because of the impossibility of testing the macro, this option is disabled TeXIF fortesting EOF CSV file • \ifEOF – TRUE,if we get to the end of processing a CSV file • \ifnotEOF – opposite \ifEOF

12. Using „manual“ mode • In the source code we use the macros\cA, \cB, or ... \Firstname, \Lastname, ... (if first line contains a header) containing a column value of the current CSV row.\Nextrow - go to the next table row (macros\cA, \cB ... are filled with new values)

13. Modification of functions of library • Default settings can be changed by editing the file scancsv.lua - in the introductory section of code • During the processing of ConTeXt MKIV (LuaLaTeX) can continuously change settings separator, spacers, headers, using TeX macros ... • Possibility of processing different CSV files in one document (with different dividers and spacers columns) • Use Hooks – default are\relax

14. Main Lua library functions • ParseCSVdata() -- Functions for parsing of individual records (rows) CSV table • lineaction()-- processingof user macro \lineaction according to the specified range of lines at the open CSV file • CreatePageFiles()-- create a two CSV files from open CSV file. It would by used to print double-sided cards, printed on the page in block R x C (the "Reposition" CSV file from the 2nd page so that the front and back of the tiles match) • Filelineactioncards() – printing  1st and 2nd sides of list of cards from the files created by the previous function • CSVReport() – get report information about open CSV file • csvfilename()– name of actualy open CSV file • TMN(s)– (TeX Macro Name). Macro name must not contain prohibited characters • ar2rom()-- Convert Arabic numbers to Roman. Used for "numbering" column in the macro • ar2xls()-- convert numbers to the column name (Excel format) • ar2colnum() -- podle nastavení glob. proměnné vrací označení sloupce TeXového makra • printline()-- vypíše aktuální řádek CSV tabulky • printall() -- vypíše celou CSV tabulku • printallcontext() -- vypíše celou CSV tabulku v ConTeXtové syntaxi

15. Testing and cycles Conditions with AND and OR (see Olšák TBN) % Condition A AND B \doloop{ \ifnum\Id>2 \ifnum\Id<10\lineaction \fi \fi \ifEOF\exitloop\else\nextrow\ifEOF\exitloop\fi\fi } % Condition A OR B \def\AorB{\lineaction} \doloop{ \ifnum\Id=1\AorB% \else\ifnum\Id>3\AorB\fi \fi \ifEOF\exitloop\else\nextrow\ifEOF\exitloop\fi\fi }

16. SCANCSV.LUA and cycles Examples of ConTeXt cycles: \dorecurse{5}{\lineaction\nextrow} - \lineactionmacro for next 5 rows \doloop{\lineaction\nextrow\ifnum\numline>7\exitloop\fi} \doloop{\ifEOF\exitloop\else\lineaction\nextrow\fi} \doloop{\lineaction\nextrow \if\Id3 \exitloop \fi} Examplesof library cycles(only in test version SCANCSV.LUA): The macros are based on \doloop macro to easier use in source code. \doloopwhile{\Trida}{3.A}{\tableaction} % List all meet the criterion \doloopuntil{\Trida}{3.A}{\tableaction} % list until it is not satisfied \doloopforall{\lineaction} – for all lines will\lineaction macro \doloopfromto{3}{7}{\lineaction} \doloopaction – without parameter done for all rows macro \lineaction. \doloopaction{\useraction} – done for all rows user macro \useraction \doloopaction{\useraction}{5} – for the first 5 rows will doing \useraction macro \doloopaction{\useraction}{5}{7} - for rows 5-7 will doing \useraction macro

17. Practical demonstrations of the use of libraries • Forms, multiple letters, etc. • Cards, business cards, … • Tables • Metapost animation • Use ConTeXtových cycles, IF tests • SCANCSV.LUA "drifts" (TeX macros in a CSV file, change \lineaction during processing CSV) • Samples of work for CTM & TE

18. Constraints, compatibility, flaws • SCANCSV.LUA does not handle general CSV files. Reason: The parsing algorithm is very simple.If the item contains a column separator “,” the CSV output is current: 1, Jan, Novotny, "The Gate 4, 111 50 Prague", ... Solution: an better (general) algorithm that.Only suffice change ParseCSVdata function (). • Occasional problems with the expansion. Eg. I failed to get SCANCSV.LUA in the module database (\usemodule [database]) Mojca Miklavec • Some things work only in ConTeXt

19. Possibilities of improvement ... • Improvements and generalizations parsing algorithm • Using for XML processing?? • Create a separate module ONLY FOR MKIV (gone for a number of limitations LuaLaTeX)

20. Thanks… • Members of mail conference ntg-context@ntg.nl for advice about ConTeXt and Lua. The library would not have given their kind assistance. Special thanks to Taco Hoekwater, Hans Hagen, Wolfgang Schuster. • Members of mail conference cstex@cs.felk.cvut.cz for advice about TeX and LaTeX. Especially Mr. Zdenek Wagner, Vit Zýka, Pavel Stříž, Petr Olšák .. • Pavel Stříž for inspiration, testing, advice and for convincence to me to finish the library and presented at this conference.

21. Discussion