The results of the data query can be exported as CSV or SQL files.
To export data as CSV
- Select a view in the User-Defined Data Views log and click the Data button. The results window will display the query results.
- Click the Export As CSV button.
- At the prompt, you can choose to Open or Save the resulting CSV file to your local drive.
The CSV file will contain the data that is displayed on the results log window. 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 window) 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.
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.
To export data as SQL
- Select a view in the User-Defined Data Views log and click the Data button. The results window will display the query results.
- Click the Export As SQL button.
- At the prompt, you can choose to Open or Save the resulting SQL file to your local drive.
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, changes a data view in the lower hierarchy (example adding a new data element) will necessitate the dependent views to be refreshed by changing them to Draft and then republish the data views 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.