CsvToTWikiAddOn < TWiki

TWiki Web>CsvToTWikiAddOn (2014-11-01, TWikiContributor) (raw view)Raw edit |
---+!! CSV To TWiki Add-On
<!--
   Contributions to this contrib are appreciated. Please update the contrib page at
   http://twiki.org/cgi-bin/view/Plugins/CsvToTWikiAddOn or provide feedback at
   http://twiki.org/cgi-bin/view/Plugins/CsvToTWikiAddOnDev.
   If you are a TWiki contributor please update the contrib in the SVN repository.
-->
%SHORTDESCRIPTION%
<sticky>
<div style="float:right; background-color:#EBEEF0; margin:0 0 20px 20px; padding: 0 10px 0 10px;">
%TOC%
</div>
</sticky>

---++ Introduction

When TWiki is used as an application platform hosting %SYSTEMWEB%.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 ([[Wikipedia:Comma-separated_values][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
<sticky>
<div style="float: right; padding: 0 0 10px 20px; width: 626px" />
<img src="%ATTACHURLPATH%/import-diagram.png" alt="import-diagram.png" width="626" height="372" />
</div>
</sticky>

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 %SYSTEMWEB%.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
   * %SYSTEMWEB%.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:

<blockquote>
<verbatim>
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
</verbatim>
</blockquote>

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

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

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

#CsvExample
---++ 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 [[%ATTACHURL%/test.csv][test.csv]] file attached to this topic has the following content:
<blockquote>
<verbatim>
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
</verbatim>
</blockquote>

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:
<blockquote>
<verbatim>
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.
</verbatim>
</blockquote>

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.

%TWISTY{
 mode="div"
 showlink="Show details %ICONURL{toggleopen}% "
 hidelink="Hide details %ICONURL{toggleclose}% "
}%

   * For an __automated installation__, run the [[%SCRIPTURL{configure}%][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:
      * See [[#CsvExample][Example]] above.

%ENDTWISTY%

---++ Contrib Info

Short description:
   * Set SHORTDESCRIPTION = Convert CSV (Comma Separated Values) file into TWiki-Forms-based topics

|  Author: | TWiki:Main.PeterThoeny |
|  Copyright: | &copy; 2014 Wave Systems Corp. <br /> &copy; 2014 [[TWiki:Codev.ConsultantPeterThoeny][Peter Thoeny]] <br /> &copy; 2014 TWiki:TWiki.TWikiContributor |
|  License: | GPL ([[http://www.gnu.org/copyleft/gpl.html][GNU General Public License]]) |
|  Sponsor: | [[http://www.wave.com/][Wave Systems Corp.]] |
|  Dependencies: | none |
|  Version: | 2014-10-31 |
|  Change History: | <!-- versions below in reverse order -->&nbsp; |
|  2014-10-31: | TWikibug:Item6895: Initial version |
|  Home: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC% |
|  Feedback: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC%Dev |
|  Appraisal: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC%Appraisal |

__Related Topics:__ %SYSTEMWEB%.TWikiAddOns
Topic revision: r0 - 2014-11-01 - TWikiContributor
TWiki
Webs
External
Main
Sandbox
TWiki
Copyright © 1999-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding ARC TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.CsvToTWikiAddOn.