Tags:
create new tag
view all tags

CSV To TWiki Add-On

Convert CSV (Comma Separated Values) file into TWiki-Forms-based topics

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

import-diagram.png

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:

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.

  • For an automated installation, run the configure script and follow "Find More Extensions" in the in the Extensions section.

  • Or, follow these manual installation steps:
    • Download the ZIP file from the Plugins home (see below).
    • Unzip SsoLoginContrib.zip in your twiki installation directory. Content:
      File: Description:
      data/TWiki/CsvToTWikiAddOn.txt Documentation topic
      lib/TWiki/Contrib/CsvToTWikiAddOn.pm Add-on Perl module
      pub/TWiki/CsvToTWikiAddOn/import-diagram.png Import diagram
      pub/TWiki/CsvToTWikiAddOn/test.csv Test CSV file
      tools/csv2twiki.pl CSV to TWiki topics converter script
    • Set the ownership of the extracted directories and files to the webserver user.

  • Test if installation is successful:

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-2015 Peter Thoeny
© 2014-2015 TWiki:TWiki.TWikiContributor
License: GPL (GNU General Public License)
Sponsor: Wave Systems Corp.
Dependencies: none
Version: 2015-05-29
Change History:  
2015-05-29: TWikibug:Item7604: Switch from GPL v2 to v3
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

Topic attachments
I Attachment History Action Size Date Who Comment
PNGpng import-diagram.png r1 manage 96.0 K 2015-05-29 - 20:57 TWikiAdminUser Saved by install script
Unknown file formatcsv test.csv r1 manage 0.3 K 2015-05-29 - 20:57 TWikiAdminUser Saved by install script
Edit | Attach | Watch | Print version | History: r2 < r1 | Backlinks | Raw View | More topic actions
Topic revision: r2 - 2015-06-17 - TWikiAdminUser
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 1999-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.CsvToTWikiAddOn.