Export and update data

This step allows you to export your Workflow's output data to a file or an External system that uses a JDBC connection.

Each Workflow can contain multiple Export steps, so you can export data at any point. Each of these steps can be configured individually.

Click Export Settings in the step dialog to specify where and how the data will be exported.

Exporting to a file

• Specify the Exporter (.csv, .txt, .xls, .xlsx or .json).

• Enable Overwrite existing to allow a file with the same name to be exported more than once (the latest copy will replace the previous one). If this option is disabled, you will get an error when trying to export to the same file name more than once.

• Column name style determines how the column names will be displayed in the export file:

  • None - the column name as it appears in Data Studio.
  • Database style - the column name will be in upper case and underscores will be used to separate words.
  • Lower case - the column name will be in lower case.
  • Upper case - the column name will be in upper case.
  • Humanized - the first word will be capitalized and underscores turned into spaces.

• Specify the Character set (most sets are supported).

Exporting to an External System using JDBC

• Select JDBC as the Exporter.
• Choose a DBMS system from the list of available ones. This is a list of the External System connections configured in Data Studio that use a JDBC driver.
• Choose a Credential for authenticating with the DBMS. These credentials will have been created alongside the External System definition.

  If there is no Credential for the selected connection assigned to your user, the will be nothing to select here.

• Select the target table from the list in Table name. These are the tables that you can access in the target DBMS using the selected credential.
• Mode determines how the data will be exported into the target system:
  • Insert: New rows are inserted into the table. The equivalent SQL statement is:
    "INSERT INTO tablename (column1, …) VALUES (value1, …)"
  • Update: Existing rows are updated. The mapping dialog will show which source columns are mapped to which target columns and which ones have been specified as keys. You can change the mapping and keys at this stage. The keys referred to are the columns that will appear in the equivalent SQL WHERE clause:
    "UPDATE tablename SET (column1=value1, …) WHERE (columnName=value, … )"
  • Delete: Rows are deleted. The keys are the column names used in the SQL WHERE clause:"DELETE FROM tablename WHERE columnName=value, … "
  • Insert or update (if insert fails): If a row insert fails (there is a key collision), then the statement will be retried as an update.
  • Update or insert (if update fails): If the update fails (no record found with the given key), then the statement will be retried as an insert.
• For Insert mode, you have the option to Truncate table before carrying out the row inserts.
• For all modes other than Insert, you will need to select one or more Key columns from those in your Workflow data. These columns need to match the key fields in your selected target table.

  If the columns in your Workflow data do not match the target table's columns they will automatically be excluded from the export. In order to map your Workflow columns to the target table you can insert a Map to Target step before your Export step.

• Select Atomic database update if the export should be performed in a single transaction. In this mode it is possible that the database may lock table(s) for the duration of the update. This may be for some considerable time for large exports.
• Commit batch size: Commit a batch after the specified number of records have been sent to the database. This setting is only available when Atomic database update is unset. Note that the final batch usually has fewer records.
• Pre SQL: Optionally enter a SQL statement that will be executed before the export. The SQL may contain any valid statements and will be executed on the target database.

  SQL statements may need to end with a COMMIT statement. Stored procedures can be executed using EXEC MyStoredProcedure or CALL MyStoredProcedure although exact database syntax may vary.

• Post SQL: Optionally enter a SQL statement, similar to Pre SQL but which will be executed after the export has completed.