How to export topic data as CSV

Problem

You want to export all data 'trapped' in your Foswiki topics, using DataForms as CSV

Context

You want your data captured in Foswiki to be transportable and reusable

Solution 1: Using core functionality only

Caveat: This solution is less reliable than Solution 2, because it assumes the list of fields in the first topic found via SEARCH that happens to use the DataForm, rather than actual list of fields from the DataForm definition itself. If you have some topics which are not using the current DataForm definition, it's possible an out-of-date topic could be used to obtain the list of fields.

%STARTSECTION{"csvcore"}%%INCLUDE{
  "%TOPIC%"
  section="csv"
  fields="%STARTSECTION{"fieldscore"}%%QUERY{
    "'%SEARCH{
      "form.name='%URLPARAM{"form" default="%WEB%"}%'"
      type="query"
      web="%URLPARAM{"inweb" default="%WEB%"}%"
      order="modified"
      reverse="on"
      limit="1"
      nonoise="on"
      format="$web.$topic"
    }%'/fields.name"
  }%%ENDSECTION{"fieldscore"}%"
}%%ENDSECTION{"csvcore"}%
%STARTSECTION{"csv"}%<literal>%SEARCH{
     "form.name='%URLPARAM{"form" default="NoSuchForm"}%'" 
     type="query" 
     web="%URLPARAM{"inweb" default="%WEB%"}%" 
     limit="%URLPARAM{"limit" default="100"}%"
     nonoise="on"
     header="\"Topic\",%FORMAT{
       "%URLPARAM{"fields" default="%fields%"}%"
       type="string"
       format="\\"$item\\"" 
       separator="," 
     }%$n"
     format="\"$web.$topic\",\"%FORMAT{
       "%URLPARAM{"fields" default="%fields%"}%"
       type="string"
       format="$dollarpercntENCODE{
         $dollarpercntQUERY{\\"'$web.$topic'/$item\\" style=\\"json\\"}$dollarpercnt
         old=\\"$dollardollarquot\\"
         new=\\"$dollardollarquot$dollardollarquot\\"
       }$dollarpercnt" 
       separator="$dollardollarquot,$dollardollarquot"
     }%"
     separator="\"$n"
     footer="\""
}%</literal>%ENDSECTION{"csv"}%

A link such as BestPracticeTip27 will get all BestPracticeTips in CSV format. An additional URL parameter, fields, may be used to specify the list of formfields.

Here is a little form which makes extracting Foswiki topics as CSV even easier:

Web: Form:

Current options:
  • Web: Support
  • Form: BasicForm
  • Fields: Automatically derived:

get CSV

Solution 2: Using FlexFormPlugin

Although the code below depends on the csv topic section defiend above, FlexFormPlugin is a more reliable than Solution 1 as it obtains the list of fields from the dataform itself, rather than from the list of fields found on the first topic which happens to use the dataform.
warning You don't have FlexFormPlugin enabled! This example won't work.
%STARTSECTION{"csvflexform"}%%INCLUDE{
  "%TOPIC%"
  section="csv"
  fields="%STARTSECTION{"fieldsflexform"}%%RENDERFORDISPLAY{ 
       form="%URLPARAM{"form" default="NoSuchForm"}%" 
       format="\\"$name\\"" 
       separator="," 
     }%%ENDSECTION{"fieldsflexform"}%"
}%%ENDSECTION{"csvflexform"}%

warning You don't have FlexFormPlugin enabled! The link below won't work.
get BestPracticeTips as CSV

Known Uses

Known Limitations

  • Doesn't inhibit macros in formfields. Formfield values which contain macros will be expanded in the CSV output. This may further inhibit CSV quoting.

Recently fixed:
  • NEW This tip now does proper CSV quote escaping (" as "" )

See Also

BestPracticeTipsForm edit

Category Developing Wiki Applications
Related Topics Faq41, Faq70
Topic revision: r9 - 19 Apr 2012, PaulHarvey - This page was cached on 23 Oct 2017 - 12:05.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License