Sometimes it is desirable to export TWiki forms based data from TWiki applications. CSV (comma separated values) is a simple file format that is widely supported by many applications, including spreadsheet programs such as Excel. This blog introduces the CSV format and explains how to generate a CSV file on the fly in a TWiki application. The CSV format is not clearly defined, there is no real CSV standard. In common usage almost any delimiter separated text data may be referred to as a CSV file. Traditionally, however, lines in the text file represent rows in a table, and commas separate the columns. This traditional understanding is defined in 4180, which is the best known effort to formalize a CSV standard. In its most basic form, each record is a line, and fields are separated by a comma. Here is an example that contains a header row representing the field names, and two records: First name,Last name,Age Jimmy,Neutron,14 Mickey,Mouse,16 Fields may contain commas, double quotes, and line breaks (CRLF); in which case they should be enclosed in double quotes. If double quotes are used to enclose fields, then a double quote appearing inside a field must be escaped by preceding it with another double quote. Example: Field A,Field B,Field C,Field D normal field,”field, with, commas”,”field with line feed”,”field with “”quoted”" text” Now that we understand the basics of CSV, let’s see how we can generate a CSV file from TWiki content. As long as content is structured using TWiki forms it is easy to generate reports on the fly using a SEARCH variable. The search can be formatted to produce output in CSV format. Let’s look at the ContactDbAddOn, which is a contact database. Each record is a TWiki page that has a ContactForm with fields Salutation, Name, Job Title, Company, Phone, Mobile, Fax, Email, and URL. Here is a search that shows all records using the default search format: %SEARCH{ “form.name ‘ContactForm’” type “query” excludetopic ” Template” nonoise “on” }% Now let’s add a search format to generate the CSV output. We add a header line containing the names of the fields (the format parameter should be on one line) : %SEARCH{ “form.name ‘ContactForm’” type “query” excludetopic ” Template” nonoise “on” header “Salutation,Name,Job Title,Company,Phone,Mobile,Fax,Email,URL” format “$quot$formfield(Salutation)$quot,$quot$formfield(Name)$quot,$quot$formfield(Job Title)$quot,$quot$formfield(Company)$quot, $quot$formfield(Phone)$quot,$quot$formfield(Mobile)$quot,$quot$formfield(Fax)$quot,$quot$formfield(Email)$quot,$quot$formfield(URL)$quot” }% This search already produces the proper CSV output with fields enclosed in double quotes. There are some limitations though: 1. Double quotes in fields are not escaped they should be escaped. 2. WikiWords in form fields are automagically expanded into links they should be escaped. 3. Even if a page only contains the SEARCH, the TWiki skin adds headers with pulldown menus and footers …

(last changed by PeterThoeny)

Original post of TWiki.
Votez pour ce billet sur Planet CMS.

Sometimes it is desirable to export TWiki forms based data from TWiki applications. CSV (comma separated values) is a simple file format that is widely supported by many applications, including spreadsheet programs such as Excel. This blog introduces the CSV format and explains how to generate a CSV file on the fly in a TWiki application. The CSV format is not clearly defined, there is no real CSV standard. In common usage almost any delimiter separated text data may be referred to as a CSV file. Traditionally, however, lines in the text file represent rows in a table, and commas separate the columns. This traditional understanding is defined in 4180, which is the best known effort to formalize a CSV standard. In its most basic form, each record is a line, and fields are separated by a comma. Here is an example that contains a header row representing the field names, and two records: First name,Last name,Age Jimmy,Neutron,14 Mickey,Mouse,16 Fields may contain commas, double quotes, and line breaks (CRLF); in which case they should be enclosed in double quotes. If double quotes are used to enclose fields, then a double quote appearing inside a field must be escaped by preceding it with another double quote. Example: Field A,Field B,Field C,Field D normal field,”field, with, commas”,”field with line feed”,”field with “”quoted”" text” Now that we understand the basics of CSV, let’s see how we can generate a CSV file from TWiki content. As long as content is structured using TWiki forms it is easy to generate reports on the fly using a SEARCH variable. The search can be formatted to produce output in CSV format. Let’s look at the ContactDbAddOn, which is a contact database. Each record is a TWiki page that has a ContactForm with fields Salutation, Name, Job Title, Company, Phone, Mobile, Fax, Email, and URL. Here is a search that shows all records using the default search format: %SEARCH{ “form.name ‘ContactForm’” type “query” excludetopic ” Template” nonoise “on” }% Now let’s add a search format to generate the CSV output. We add a header line containing the names of the fields (the format parameter should be on one line) : %SEARCH{ “form.name ‘ContactForm’” type “query” excludetopic ” Template” nonoise “on” header “Salutation,Name,Job Title,Company,Phone,Mobile,Fax,Email,URL” format “$quot$formfield(Salutation)$quot,$quot$formfield(Name)$quot,$quot$formfield(Job Title)$quot,$quot$formfield(Company)$quot, $quot$formfield(Phone)$quot,$quot$formfield(Mobile)$quot,$quot$formfield(Fax)$quot,$quot$formfield(Email)$quot,$quot$formfield(URL)$quot” }% This search already produces the proper CSV output with fields enclosed in double quotes. There are some limitations though: 1. Double quotes in fields are not escaped they should be escaped. 2. WikiWords in form fields are automagically expanded into links they should be escaped. 3. Even if a page only contains the SEARCH, the TWiki skin adds headers with pulldown menus and footers …

(last changed by PeterThoeny)

Original post of TWiki.
Votez pour ce billet sur Planet CMS.