Export Data (Query Results)

The results of the data query can be exported as CSV or SQL files.

To export data as CSV

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
Select a view.
In the toolbar, click Data.
In the results window, click the Export As CSV button.

Depending on your browser, the file will either download automatically or you will be prompted to download it.

The CSV file will contain the data that is displayed on the results log. By default, the first 100 lines will be displayed. You can click the Display drop-down (in the upper-right corner of the results log) and choose 200 to display the first 200 lines. When you click Export As CSV, the CSV file will contain the 100 (or 200) lines displayed in the window.

To export data as SQL

Go to the Company Workspace tab and switch to Admin mode.
In the left Navigator, click Data Structure Setup > Data Views.
The User-Defined Data Views log opens.
Select a view.
In the toolbar, click Data.
In the results window, click the Export As SQL button.

Depending on your browser, the file will be downloaded automatically, or you will be prompted to download the file manually.

This will export your SQL script to create the table required for the data view and the data itself.

Notes on working with the Oracle Database

Only the 100 or 200 rows will be exported at a time. You cannot export the entire result of the view (for example, if there are more than 200 rows) in one click. You must export in groups of 200 rows at the maximum and then manually combine the exported results.
Note that SQL export files have the .sql extension and work best with the Oracle database as the field types used in the creation of the data view are of Oracle type (for example, VARCHAR). If the exported SQL file is to be used in a database other than Oracle, you must manually modify that file to replace the field types with the types that work with the non-Oracle database.
Unifier uses a field type called TIMESTAMP for all date or time fields, so you must modify the session date format to match the timestamp used in Unifier. For example, you can run the following statement for an Oracle database: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
If you are building multiple data views based on other data views, then any changes to a data view (for example, adding a new data element) in the lower hierarchy will necessitate the dependent views to be changed to Draft, in order to refresh, and the data views must be republished, to incorporate the changes. For example, if Dataview 2 is based on Dataview 1, and you add a new field to Dataview 1 and then republish it. You must then change Dataview 2 to Draft and then republish it so that it will contain the change you made in Dataview 1.
Additional cleaning of the data (in case of export to SQL file) might be needed if there are (‘) apostrophes in the values of some fields, or if there are ampersands (&). This will result in inconsistent values between the results in Unifier and the result in the local database after import.

To export data as CSV

To export data as SQL

See Also