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: About Community Community/Developers Development Download Extensions Extensions/Archived Extensions/Testing Home Main Support Support/ClientSupport Support/Question922 System Tasks Tasks/Testcases Form: BasicForm BestPracticeTip12Form BestPracticeTipsForm FAQForm QuestionForm SecurityAlertForm WikiConsultantForm

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.

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"}%

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:

• This tip now does proper CSV quote escaping (" as "" )

See Also