CSV To TWiki Add-On

Introduction

When TWiki is used as an application platform hosting TWikiForms-based applications, there are cases when you want to import existing content from an external database. This add-on is designed to import data from an external database into TWiki in a structured way.

A table in an external database is first exported as a CSV (Comma-Separated Values) file, which is the most commonly used interchange format for tables. After that, the csv2twiki.pl script of this add-on is used to convert the CSV file into a set of topics. Each row in the CSV file results in a topic that has a TWiki form containing the data imported from the CSV table row.

Detailed Instructions

Converting a table of an external database into a TWikiForms-based application involves three steps:

1. Create a TWiki application
2. Export database table into a CSV file
3. Import CSV file into TWiki

1. Create a TWiki application

A TWikiForms-based application typically consists of these topics:

• an application home topic containing an embedded report showing records, such as contacts in a ContactDB
• a topic with form to add a record, such as NewContact
• a form topic, defining the database schema (fields, each with a name, type, value), such as ContactForm
• a template topic, used when creating new topics, such as ContactTemplate
• a header topic (optional), included in record topics, such as ContactHeader

Creating a TWiki application is out of scope of this add-on. To learn more:

• TWiki:Blog.BlogEntry201009x1 - blog post on How to Create a TWiki Application
• TWikiForms - detailed documentation
• TWiki:Plugins.ContactDbAddOn - sample contact database application

2. Export database table into a CSV file

Most database applications have an option to export a table as a CSV file. Sometimes an Excel file is generated, in which case you can use Excel to export the table as a CSV file. In its simplest form, a CSV file may look like this:

Salutation,Name,Job Title,Company,City,ZIP
Mr.,Jimmy Neutron,Chief Scientist,Nickelodeon,New York City,10001
Ms.,Minnie Mouse,Home maker,Disney Company,Burbank,91501

3. Import CSV file into TWiki

Once this add-on is installed, the twiki/tools directory contains the csv2twiki.pl command line script. Use it to generate a set of TWikiForms-based topics from a CSV file. Usage:

perl -I ../bin csv2twiki.pl [-d <level>] [-u <login-name>] <csv-file> <web> <template-topic> [<base-topic>]

• -I ../bin - defines the library include path (can be omitted, script should detect the TWiki scripts automatically)
• csv2twiki.pl - script name
• -d <level> - debug mode with these levels, optional:
  -d 1 - normal debug mode
  -d 2 - verbose mode, showing meta data and topic text
  -d 3 - like mode 2, but suppressing topic creation
• -u <login-name> - login name used to create the topics, optional, default: admin
• <csv-file> - path & name of CSV file, such as /tmp/test.csv
• <web> - name of TWiki web where topics are generated, such as Sandbox
• <template-topic> - name of template topic containing the TWiki form, such as ContactTemplate
• <base-topic> - name of base topic, optional, such as ContactAUTOINC0001

Description:

• A topic is created for each row in the CSV file.
• If a topic already exists, its content is replaced; the old content is retained in the topic history.
• The template topic is assumed to contain a TWiki form with fields.
• The CSV file is assumed to have a header row with form field names matching the TWiki form field names.
• A CSV header of "TOPIC" indicates the topic name to create; if missing, a base topic name can be used to indicate an auto-incremented topic name, default is CsvImportAUTOINC0001. For example, base topic "ID-AUTOINC0001" will create topics named ID-0001, ID-0002, etc.
• A CSV header of "TEXT" indicates the topic content; if missing, the content of the topic indicated in "TOPIC" is used; if that is missing, the content of the template topic is used.
• A CSV header of "PARENT" indicates the topic parent; if missing, the parent of the topic indicated in "TOPIC" is used; if that is missing, the parent of the template topic is used.

Attention:

• It is recommended to run this script as the webserver user.
• Alternatively, if you run this script as another user you need to fix the file ownership of the generated topics to be owned by the webserver user.

Example

We use the TWiki:Plugins.ContactDbAddOn as an example TWiki application. Install it on your TWiki to test the CSV to TWiki converter.

The Contact DB application has these form fields defined in ContactForm: Salutation, Name, Job Title, Company, Phone, Mobile, Fax, Email, URL.

The test.csv file attached to this topic has the following content:

TOPIC,Name,JobTitle,Company,PARENT,TEXT
CsvTestA,A Name,A Job Title,A Company
CsvTestB,B ""quoted"" Name,B Job Title,"B3, comma, Company",ContactDB,"%INCLUDE{ContactHeader}%

This is the topic text from the CSV file

---++ Contact Log

%COMMENT%

__Back to:__ ContactDB
"
,C1 no TOPIC Name,C2 Job,C3 Company
SusanHit,Susan Changed,Marcom,Akme

The first row defines the field names:

• Name, JobTitle, Company - field names matching the ones of the TWiki app will be used on import.
  • The remaining form fields Salutation, Phone, Mobile, Fax, Email and URL will be left empty on topic creation.
• TOPIC - indicates the topic name; the first data row's topic name is CsvTestA.
• PARENT - indicates the parent topic; only set in the second data row.
• TEXT - indicates the topic text; only set in the second data row - it has multiple lines, this needs to be enclosed in double quotes.

On the shell, change to the twiki/tools directory and run this command:

./csv2twiki.pl ../pub/TWiki/CsvToTWikiAddOn/test.csv Sandbox ContactTemplate ContactID

This will run the converter, taking test.csv as the input, and creates/updates topics producing this output:

Converting ../pub/TWiki/CsvToTWikiAddOn/test.csv to TWiki topics using:
- template topic: Sandbox.ContactTemplate
- base topic:     Sandbox.ContactIDAUTOINC0001
- creating topic: Sandbox.CsvTestA
- creating topic: Sandbox.CsvTestB
- creating topic: Sandbox.ContactID0001
- updating topic: Sandbox.SusanHit
Done.

The last topic SusanHit already existed in the Sandbox web, hence the "updating topic" indication.

If you were running the command as a user other than the webserver user you need to fix the file ownership of the generated files in the Sandbox web. Example for Red Hat and CentOS based servers:

chown apache:apache twiki/data/Sandbox/*

Installation Instructions

Note: You do not need to install anything on the browser to use this add-on package. The following instructions are for the administrator who installs the package on the server where TWiki is running.

Show details Hide details

Contrib Info

Short description:

• Set SHORTDESCRIPTION = Convert CSV (Comma Separated Values) file into TWiki-Forms-based topics

Author:	TWiki:Main.PeterThoeny
Copyright:	© 2014 Wave Systems Corp. © 2014 Peter Thoeny © 2014 TWiki:TWiki.TWikiContributor
License:	GPL (GNU General Public License)
Sponsor:	Wave Systems Corp.
Dependencies:	none
Version:	2014-10-31
Change History:
2014-10-31:	TWikibug:Item6895: Initial version
Home:	http://TWiki.org/cgi-bin/view/Plugins/CsvToTWikiAddOn
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/CsvToTWikiAddOnDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/CsvToTWikiAddOnAppraisal

Related Topics: TWikiAddOns