Exporting Data

The following article outlines the options for exporting data from DataJet, as well as providing some guidelines for maximising performance.

Overview

Export From

Data can be exported using the following paradigms:

Ad-hoc export from the Desktop Application:
Automated Export using scripting methods:
Export from the web application:
- audience rules & counts
- audience members
- marketplace export

Export To

Data can be exported to:

File
- flat file
- excel workbook
Table

External Processing

To export data for use in a 3rd party process (i.e., a python module), first export to file and then use XProcess to call the external process

Export Contents

The details of what is being exported depends on project settings. The default is that the content that is exported matches what is seen on screen, but some projects are configured to export underlying codes rather than display data.

See How to configure export to export source fields for further details on this.

Export Locations

Data can be exported to the following locations.

Available Locations

My Export Folder = [realm-directory]/output/[username] (e.g., .../dj-localhost/output/user@something.com )
- equivalent to: %MYEXPORT%
Team Export Folder = [realm-directory]/output
- equivalent to: %OUTPUT%
My List Folder = [realm-directory]/Lists/[username]
- equivalent to: %MYLISTS%
Team List Folder = [realm-directory]/Lists
- equivalent to: %LISTS%
Data Source = [data sources]
- equivalent to: %DATAPATH%
Templates = [realm-directory]/templates
- equivalent to: %TEMPLATES%

See System Variables for more information on location variables.

Export Monitoring

Exports can take some time to complete, depending on the data being exported. Every export, regardless of method, will create and start a new job. Once the export job has begun, the Tools | Jobs dialog will automatically open and show the progress of the export.

When complete, right-click the job in the Job Dialog and choose "View" to see a subset of the exported data. (Note: this is only available when viewing file exports). If an audit has been created, it is also possible to view the Audit file.

Export History

Exports to File will cause an entry to made in the Export history Log:

The following output files may/will be created:

Data File - always created [exportFileName]
Export Summary report - always created [exportFileName].info.json
Audit Spreadsheet - generated if audit=true [exportFileName].xlsx
Info Audit report - extended version of export summary report, generated if infoAudit = true [exportFileName].info.json
Load Script - generated if createImportScript is true [exportFileName].json

See Export for further details on export output files.

Export Templates

Export templates are Excel Templates that control layout and formatting of exports. They can be viewed by administrators in Admin | Management Console | Export Templates.

Export Templates are created using CreateExportTemplate

Exporting from Dataset Scratch

The Dataset Scratch report allows for the building of Ad-hoc queries as well as the display of existing datasets and is the primary source of export, other than through scripting.

The following export options are available:

Export to Table

Creates a new datajet table from the active dataset. The records that are exported into the new table will be the same records that are in the data-set.

Table Name

Provide the name of the table to create. If Table Name matches an existing table-name, append must be selected. See Field and Table Names for details of valid table names.

Template

By default, the fields in the new table will match the template of the current data set view (ie. the columns that are currently displayed). It is also possible to select an existing template from the template drop-down, in which case the field list of the selected template will overwrite the open dataset field list. The order of fields in the new table will match the order of fields in the field list.

Template Table

It is possible to specify a field template from a table other than the owner table of the dataset that is being exported, as long as the two tables have field names in common.

Re-Cast Types

Data Types will be preserved by default, even if the number of records and/or discrete values in the output table is low enough to warrant a change for a field from Continuous Fieldto Discrete Field.

To force the export to use discrete fields where appropriate, select "Re-Cast Types". See also:

Append

Select Append in order to add data to the end of an existing table.

Field Order does not matter in the field template that is used to append data, as long as the list of field names in the Export Template exactly matches the the fields in the existing table.

Appending Data

2 additional scenarios are possible:

Some fields are present in the Export Template but are missing in the Target Table
1. error message: [Fieldname] does not exist on target
Some fields are present in the Target Table but are missing in the Export Template
1. error message: append will produce unbalanced table - see 'completeFields'

In both scenarios, it is necessary to use the "ExportIntoTable" method in order to append, with the key "completeFields" set to true.

NOTE: It is easy to confuse data when using completeFields, so be sure processes are robust.

See ExportIntoTable for further details.

Export To Excel

NOTE: This option is only functional if Excel is installed on the server.

Creates or appends to the specified workbook and worksheet. Data exported is the existing data view. Excel Template files can be used to pre-configure output.

Location

Required. Data can only be exported to the server, and only to one of the pre-configured locations. See Export Locations

Workbook Name

Required. Name of the workbook to create/append to.

Tip!

Make sure the specified workbook is not open in another process, or the export will fail.

Worksheet Name

Optional. Name of worksheet to create/append to. If not provided, data will be exported to "Sheet1"

Export Template

Optional.

Export Templates (not to be confused with Field Templates) are excel files that can be used to control formatting and content of excel output.

Use the following steps to implement export templates:

Create the export template - this is an excel workbook that contains one or more worksheets with pre-defined column headers and/or formatting.
Add the export template to the system using CreateExportTemplate
Select the Export Template from the list of configured Export Templates in the drop-down.

{
  "method": "CreateExportTemplate",
  "name": "%ClientName%_PlatformTemplate",
  "fromWorkbook": {
    "filename": "%ExportTemplateFilenameAndPath%",
    "worksheet": "Product Data Inputs",
    "headerRow": 2
  },
  "project": "Y81_CreateID_Jun28_4"
}

Example Template:

Start Row

Optional. Default = 1

The excel row into which the first row of data will be written. If headers are included, this will be the row containing column header names.

Include Headers

Optional. Default = false

If false, StartRow will contain the first record from the dataset. If true, StartRow will contain columns names.

Export As Text

Optional. Default = false

If false, excel will automatically detect column contents and apply a data-type accordingly. If true, columns will be forced to be DataType "Text"

This is sometimes necessary when working with dates, or where leading zeros are present in numeric data stored as a string in DataJet. (E.g., by default excel will convert "00100928" to 100928

Append Sheet

Optional. Default = false.

If false, the specified worksheet will replace any existing worksheets in the specified workbook.

If true, the specified worksheet will be appended to the specified workbook. If the workbook does not already exist, the export will fail unless "Append Can Create" has been selected. An error will be raised if "Append Records" is also selected.

Append Records

Optional. Default = false.

If false, the specified worksheet will replace any existing worksheets in the specified workbook.

If true, records will be appended to the end of the specified worksheet. If the workbook does not already exist, the export will fail unless "Append Can Create" has been selected. An error will be raised if "Append Records" is also selected.

Append Can Create

Optional. Default = false.

If false, and either "Append Sheet" or "Append Records" is selected and the specified workbook does not exist, the export will fail.

If true, and either "Append Sheet" or "Append Records" is selected and the specified workbook does not exist, the workbook will be created.

Ignore If No Rows

Optional. Default = false.

If false and there are no rows to export, the export will fail.

If true and there are no rows to export, the export will be ignored.

Note: Other settings are available when exporting to workbook - see ExportIntoWorkbook for further details.

Export Data

Exports data from a single table to file. The data exported is the active dataset.

Location

Required. Data can only be exported to the server, and only to one of the pre-configured locations. See Export Locations

Template

Optional. If provided the specified template will determine the field list for export. All fields listed in the selected template must be present in the active dataset table.

Filename

Required. The filename to export to. Include file extensions in the name, as extensions are not added by the export process. If the file exists and "Overwrite Existing" is not selected, an error will be raised.

Additional Settings

Audit - creates an excel audit file of the export in the specified location. This will create both the "Fields" and the "Penetration" worksheets, and can add a considerable time to the export, depending on the number of fields to be analysed. This is the same output that is generated by the "Audit" method.
Upload File - TODO: What does it upload where?
Overwrite Existing - If selected existing file will be overwritten
Export Source Fields - only available if the project has been configured to export source fields. See How to configure Export to file to export source fields for details.

Options:

Export Delimiter - Choose from: tab, comma, pipe, semi-colon, HSTART
Export Headers - Include Headers, Exclude Headers
Include URNs - Yes, No
Quoted Text Qualifier - Yes, No.
Include Import Definitions - Yes, No. If Yes, a script containing the "CreateTableFromFile" method required to reimport the data will also be created. This is useful for ensuring field definitions match if the data is going to be re-imported back in DataJet.

Note!

Export "option" settings are saved in DataJet Desktop between sessions. If options are changed from the default, these settings will be automatically applied to the next export to file.

Exporting from Data Audit

The Data Audit report offers the following export options:

Export to Spreadsheet - Exports the data audit display to a DataJet spreadsheet. DataJet spreadsheets can be saved as xlsx files.
Export To Table - Export the data audit display to a DataJet Table.
Export - Export the data audit display to an Excel spreadsheet
Export Profiles - Adds to an Excel audit spreadsheet an individual profile for every field.

Exporting from Reports

Analytical Reports support some combination of the following export options:

Export To Table - Creates a datajet table from results
Export To File - Creates a flat file of results
Export To Excel - Creates and Excel Spreadsheet of results
Copy to Clipboard(All)- Copies all results to the clipboard
Copy to Clipboard(Cell)- Copies selected cell/line results to the clipboard
Copy to Clipboard(Image)- Copies image to the clipboard
Export To Spreadsheet - Adds data to an existing datajet spreadsheet
Drop on Spreadsheet - Creates a new datajet spreadsheet and copies data
Send To Model - Sends data to modelling extension

The following shows the export options offered by each report:

Exporting from script

The following Export methods are available for use with Script Editor:

Export - Exports to file
ExportIntoTable - Exports into a DataJet Table
ExportIntoTables - Exports a single source table into multiple sub tables using a dataset collection to determine records in each sub-table
ExportIntoWorkbook - Exports into Excel

Exporting To

Data can be exported to the following:

File
DataJet table
Datajet spreadsheet
Excel spreadsheet

Exporting to File

Export to file using one of the following:

Export method
Dataset Scratch
Profiles (Simple, Index, Multi-Function)
Data Audit
Analyze Field

Exporting to DataJet Table

See ExportIntoTable for full method details.

Tip!

New tables can also be created using the following methods:

CopySourceTable - copies a table FROM and existing project INTO the current project
CreateKeyTable - Creates a table of unique values from a specified source field - frequently used for analysis and joining data
CreateAuditTableFromTable - Creates a re-organised copy of a table to facillitate comparison of Data IN and Data OUT following data transformations
CreateTableFromWorkbook - Imports data from an excel workbook into a table
CreateTableFromWorkbooks - Appends all the workbooks in a folder into the same table.
ETLSummaryFromTableEx
MergeTables
MergeTablesOnJoin

New Table

Most reports allow data to be exported into a new DataJet Table. The ExportIntoTable method will create a new table if called with "append" = false.

Append to Existing Table

{
  "method": "ExportIntoTable",
  "table": "04_AllVersions",
  "append": true,
  "dataSet": {
    "logic": "or",
    "name": "SubSet",
    "strict": true,
    "set": [
      {
        "logic": "or",
        "stype": "TABLE",
        "entity": "03_KJV_PCE_RAW"
      }
    ]
  },
  "template": "03_KJV_PCE_RAW-ExportTemplate1",
  "project": "BibleProject_Base"
}

Export to Multiple Tables

It is possible to split a dataset into multiple tables, using ExportIntoTables.

Exporting to Excel

using templates - {coming soon}

Maximising Performance

{coming soon}