Child pages
  • Data Export tricks and hints

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

This is the documentation for ConfiForms Server/Data Center app

However, this might also work for ConfiForms cloud and in most cases it does. But please see this page to understand the differences between server and cloud versions of the ConfiForms app.

Export formats

ConfiForms provides a way to export data in 4 different formats (or in 6 if you count flattened views as separate types)

  • CSV (+flattened view) - comma separated values
  • XLS (+flattened view) - Microsoft Excel format
  • JSON - JSON format
  • XML - dataset exported in an XML format

First 2 are for end-users mainly and second 2 are for further data processing, as it depends how the data for complex fields is exported. JSON and XML formats export internal representation of the data, that means you will see IDs for dropdowns, GUIDs for smart classifiers, page IDs for pages and blogposts, date and time fields exported as timestamps (epoch) and also IDs for database-backed fields


Let's consider an example. Here is a form with 3 fields

  • mytf - text fied
  • mydf - dropdown field (contains 3 choices)

    IDLABEL
    1one
    2two
    3three
  • mydate - is of type date


Export is enabled by default in the Admin UI for the form

We will add couple of records and will show how the exported result for each format look like


 Exported as CSV

Id,Created By,Owned By,Created,Created,My text field,My Dropdown Field,My date field
f4ea4796-0dd2-455c-a1b4-926209260a02,sash,sash,1465197019719,2016-06-06 03:10,hello export,one,"Jun 04, 2016"
66cee179-2f33-4fd1-b8b8-bb889043e06f,sash,sash,1465197038331,2016-06-06 03:10,confiforms tutorial,three,"Jun 07, 2016"

Exported as XLS

Exported as JSON

{
  "list": {
    "entry": [
      {
        "recordId": 1,
        "deleted": 0,
        "createdBy": "sash",
        "created": 1465197019719,
        "id": "f4ea4796-0dd2-455c-a1b4-926209260a02",
        "fields": {
          "mydate": 1465012800000,
          "class": "fields",
          "mytf": "hello export",
          "mydf": 1
        },
        "ownedBy": "sash"
      },
      {
        "recordId": 2,
        "deleted": 0,
        "createdBy": "sash",
        "created": 1465197038331,
        "id": "66cee179-2f33-4fd1-b8b8-bb889043e06f",
        "fields": {
          "mydate": 1465272000000,
          "class": "fields",
          "mytf": "confiforms tutorial",
          "mydf": 3
        },
        "ownedBy": "sash"
      }
    ]
  }
}

Exported as XML

<list>
   <entry>
      <id>f4ea4796-0dd2-455c-a1b4-926209260a02</id>
      <recordId>1</recordId>
      <ownedBy>sash</ownedBy>
      <createdBy>sash</createdBy>
      <created>1465197019719</created>
      <deleted>0</deleted>
      <fields class="fields">
         <mytf>hello export</mytf>
         <mydf>1</mydf>
         <mydate>1465012800000</mydate>
      </fields>
   </entry>
   <entry>
      <id>66cee179-2f33-4fd1-b8b8-bb889043e06f</id>
      <recordId>2</recordId>
      <ownedBy>sash</ownedBy>
      <createdBy>sash</createdBy>
      <created>1465197038331</created>
      <deleted>0</deleted>
      <fields class="fields">
         <mytf>confiforms tutorial</mytf>
         <mydf>3</mydf>
         <mydate>1465272000000</mydate>
      </fields>
   </entry>
</list>


In the examples above you can see the difference in how the data differs (for non-text fields, see "mydf" and "mydate" fields)

Form Admin UI also provides a way to see the structure of the form dataset, providing RAW data format.

This is mainly used for troubleshooting and data recovery


What about flattened views?

These are used for forms which have multi-select fields and you want to export each choices as aseparate row

Consider the following example

We have added one more field of type multi-select into the form (described above)

It is called "mymf" and of type "Multi-select"

We changed our dataset to 

It still contains 2 fields and the exported view as CSV looks like this:

Id,Created By,Owned By,Created,Created,My text field,My Dropdown Field,My date field,My multi field
f4ea4796-0dd2-455c-a1b4-926209260a02,sash,sash,1465197019719,2016-06-06 03:10,hello export,one,"Jun 04, 2016",select B
66cee179-2f33-4fd1-b8b8-bb889043e06f,sash,sash,1465197038331,2016-06-06 03:10,confiforms tutorial,three,"Jun 07, 2016",select A select C

However, when "flattened view" is requisted it will split the choices of multi-select field into separate rows

Id,Created By,Owned By,Created,Created,My text field,My Dropdown Field,My date field,My multi field
f4ea4796-0dd2-455c-a1b4-926209260a02,sash,sash,1465197019719,2016-06-06 03:10,hello export,one,"Jun 04, 2016",select B
66cee179-2f33-4fd1-b8b8-bb889043e06f,sash,sash,1465197038331,2016-06-06 03:10,confiforms tutorial,three,"Jun 07, 2016",select A
66cee179-2f33-4fd1-b8b8-bb889043e06f,sash,sash,1465197038331,2016-06-06 03:10,confiforms tutorial,three,"Jun 07, 2016",select C

See last 2 rows, they are practically identical with one exception, last field: each selection for multi-select field is on on it's own row

What if I need internal or transformed data in CSV/Excel format?

This is also possible, but will require a bit of custom work

Consider how the link to export data is constructed in form AdminUI (but also in other views where export is enabled)

Version 2.18.15 and later
https://wiki.vertuna.com/plugins/servlet/confiform/export?t=csv&pageId=8159450&fd=f:8159450&fields=&filter=

Version 2.18.14 and older
https://wiki.vertuna.com/ajax/confiforms/export.action?t=csv&pageId=8159450&fd=f:8159450&fields=&filter=

It expects:

Parameter nameValue
t

Could be:

  • csv
  • flatten.csv
  • xml
  • json
  • xls
  • text
  • flatten.xls


pageIdPage Id of the page where ConfiForms is configured
fd

form name and again page ID where form is defined (some backward compatibility parameter, but still is required). It sould be given in a format

formname:pageId

In the example above we have a form named "f" and it is located on the page with id "8159450"

fields

Field names to export. This is interesting, as you can limit the fields in your export and also use the techniques from Accessing field values and properties to customize the export.

For example:

to export just user-defined fields, without metadata fields

Version 2.18.15 and later
https://wiki.vertuna.com/plugins/servlet/confiform/export?t=csv&pageId=8159450&fd=f:8159450&fields=mytf,mydf,mydate,mymf&filter=

Version 2.18.14 and older
https://wiki.vertuna.com/ajax/confiforms/export.action?t=csv&pageId=8159450&fd=f:8159450&fields=mytf,mydf,mydate,mymf&filter=

result:

My text field,My Dropdown Field,My date field,My multi field
hello export,one,"Jun 04, 2016",select B
confiforms tutorial,three,"Jun 07,2016",select A select C


to export date field as timestamp in CSV format and dropdown field's ID (not label)

Version 2.18.15 and later
https://wiki.vertuna.com/plugins/servlet/confiform/export?t=csv&pageId=8159450&fd=f:8159450&fields=mydf.id,mydate.timestamp&filter=

Version 2.18.14 and older
https://wiki.vertuna.com/ajax/confiforms/export.action?t=csv&pageId=8159450&fd=f:8159450&fields=mydf.id,mydate.timestamp&filter=

result:

mydf.id,mydate.timestamp 
1,146501
3,146527

For fields, you can use Virtual functions and format or transform the values as needed!

filterSame as ConfiForms Filters. To limit the exported dataset
headers

Since ConfiForms version 2.15.3 you can supply the "headers" parameter as comma separated list of names to use for headers in exported CSV/Excel file

Quick example:

headers=My ID,Column2
sortSorting in ConfiForms
limitNumeric value to limit the number of records returned. Missing parameter or limit=0 sets the limit to "no limit"

Customising exports via ConfiForms Views

Same customizations as explained in the table above for "fields" parameter could be managed/configured on ConfiForms views (TableView, CardView, ListView, etc) macros using 

 


Exports are not working for some users

For form non-admin users (see here who is form administrator and who is not - Confluence page permissions and ConfiForms) you need to explicitly allow data exports. This is a security measure to prevent forms data from being exported by anyone when you dont't want to. 

To enable exports for non admin users you need to allow exports in the Form Definition for your form

And then you can enable the option to export the data in the corresponding views

For example, this is how you do it for the ConfiForms TableView



  • No labels