ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (15.3.0) 15.2.0 15.1.x 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Importing and Exporting Data in RDM

This article provides information about how to manually import and export data from and to RDM via a text file. This is an easy way to transfer data between RDM and external applications.

Importing data

Importing data from a text file in RDM can be done in two data-viewing modes: EDIT and IMPORT.

To import data, you must have the permissions to create, edit, and delete records. See Setting Permissions in RDM.

Choosing the import mode

If you need to quickly import new records to RDM, use the EDIT mode. If you need to update or delete records as well as add new ones, use the IMPORT mode. One more advantage of importing data in the IMPORT mode is the ability to preview the state of records before pushing the data to the EDIT mode for publishing.

Import requirements

The import procedure has the following requirements:

  • The table must have a defined primary key.

  • The imported file must have the CSV extension.

  • The file must have the same column names as in the database; the column order does not matter. To view database column names, go to Columns setup and change In table headers display to column IDs (for detailed instructions, see Viewing Data in RDM, section Columns Setup).

  • The default options for column separators are a semicolon (;) and a tab. If required, additional separators can be specified by selecting Custom.

  • The default options for line endings are CRLF (Windows) or LF (Linux). If required, additional endings can be specified by selecting Custom.

  • Encoding can be UTF-8, UTF-16, ISO/IEC 8859-1, ASCII, UTF-16 little-endian, or UTF-16 big-endian.

Date and time formatting requirements

Date and time can be specified in one of the following formats:

Datetime Date

yyyy-MM-dd HH:mm:ss (ISO)

yyyy-MM-dd (ISO)

MM/dd/yyyy HH:mm:ss

MM/dd/yyyy

dd/MM/yyyy HH:mm:ss

dd/MM/yyyy

dd.MM.yyyy HH:mm:ss

dd.mm.yyyy

yyyy-MM-dd (ISO)

MM/dd/yyyy

dd/MM/yyyy

dd.mm.yyyy

Custom

Select Custom and define the format - it should follow Java conventions).

In addition, keep in mind the following:

  • If no format is selected, then the MM/dd/yyyy HH:mm:ss datetime format is used by default.

    After a successful import or export, the last used date or datetime format is saved and preselected in the settings when you initiate a new import or export.

  • Only one format can be used per import. If there are multiple datetime columns in the import, they need to be in the same format.

During import, the format MM/dd/yyyy HH:mm:ss is used by default. Even if data has been exported in this format it can be altered, for example, by Microsoft Excel.

Ensure values have not been converted before import, or alter the import format accordingly.

It is not necessary to manually remove null values before import, or to define a custom format to accommodate null values. For example, if your CSV file contains values in the format 18.05.20 00:00:00, specifying dd.MM.yyyy is sufficient.

However, if the values do exist, for example, for time, and the parsing pattern does not include these, they will be lost. For example, if the data would be in the format '2020-01-01 12:13:14' and datetime pattern is 'yyyy-MM-dd', '2020-01-01' is exported and by default '2020-01-01 00:00:00' would then be imported.

Currently both Java and RDM datetime format include time by default so it is set to zero when not specified in the parsing pattern.

Importing data in the EDIT mode

Importing data in the EDIT mode is a fast way to get new data to an empty or filled table. Data is compared via the primary key. Any imported record whose primary key matches that of any existing records is marked as INVALID.

To update or delete data as well as add new records, use the IMPORT mode. See Importing data in the IMPORT mode.

Example of an imported .csv file
first_name;last_name;birth_number;birth_date
John;Sanders;31101978111;1978-10-31
Hugh;Kensington;06071988112;1988-07-06

To import data in the EDIT mode:

  1. Make sure you are in the EDIT mode and have the permissions to create and modify all columns of the table to which the data is imported.

  2. Select Bulk > Import.

  3. Select Browse and select the prepared CSV file. Select Import.

If the import was successful, the imported records appear in the table with green dots displayed at the beginning of their rows.

In case of an error, go to System > Action History to see the details. See Tracking Data and System Errors in RDM, section Action history.

Importing data in the IMPORT mode

Importing data in the IMPORT mode (as opposed to the EDIT mode) detects differences between imported and currently published records. As such, you can use it in the following cases:

  • Initial load (import data to an empty table).

  • Full load (fully replace current data with imported).

  • Incremental load (import changed and new data plus delete specially marked records).

To import data in the IMPORT mode:

  1. Open a table into which you want to import data.

  2. Switch to the IMPORT mode.

  3. In the mode selector, select Setup filters and make sure Show grouped record is selected.

    In case you want to perform an incremental load, also select Incremental compare and then Apply.

  4. Select Bulk > Import.

  5. Select Browse and select the prepared CSV file. Select Import.

  6. Select Bulk > Create Grouped Records.

  7. Select Bulk > Merge.

Exporting data

To export data from an RDM table:

  1. Open a table you want to export.

  2. Select Bulk > Export.

  3. In the subsequent dialog, configure the export by selecting:

    1. The data as seen in which mode: PUBLISHED, EDIT, HISTORY, or ALL_HISTORY.

    2. Whether to apply the currently used filter.

    3. Export type: CSV, TXT, or XLS.

    4. Headers type: labels, names (as available in the database) or no headers.

  4. Select Export. The file is now available for download.

    In case warnings appeared after the export or the data failed to export, go to System > Action History to see the details. See Tracking Data and System Errors in RDM, section Action history.

Was this page useful?