Import data sources in Dictionary from CSV files

Modified on: Mon, 29 Jul, 2024 at 3:07 PM

The purpose of this article is to explain how to import dictionary objects from a CSV file.

To understand more in-depth the concept of property and objects, you can consult this article.

As a reminder: the module dictionary describes the data sources.

The goal of this feature is to:

Ease the import of dictionary type object,
Accelerate the objects update with the import of data previously exported.

The import option is available:

from the "Shortcuts" widget on the home page of the clientspace or workspace
from the Dictionnary module when it is empty
from the contextual menu of the Dictionnary module, to the right of the filtered views.
from the contextual menu of the source you want to import in the Dictionnary module (which is in the upper right-hand corner of the object card)

Which elements can be imported?

The dictionary module is made of 4 sources type: Database, FileStore, NoSQL & Tagbase data source. Different type of sources means different kinds of objects are managed :

You can also import information about your objects using the attributes. This feature is available for sources, containers, structure and field objects.

Source type	Container	Structure type	Sub-structure	Fields type	Primary Keys	Foreign keys	Relations)
DataBase	Model	Table	N/A	Column	Yes	Yes	Yes
FileStore	Folders	File	Yes	Field	N/A	N/A	N/A
NoSql	Model	Table	N/A	Column	N/A	N/A	N/A
NoSql	Folders	File	N/A	Field	N/A	N/A	N/A
NoSql	Folders	Document	Yes	Field	N/A	N/A	N/A
Tagbase	Equipement	Tag	N/A	Field	N/A	N/A	N/A

What are the mandatory elements when importing

For containers, structure and field objects, if some options can be set later, the file to be imported must always contain the following columns:

The object path
The object type
+ a column for each attribute that is part of the object card that you want to import

Example of a CSV file to import for a container of a FileStore type source:

"Type";"Path";"Summary";"Status";"DataOwners"
"\Directory";"\Workspace";"Home";"Proposed";"dataowner@datagalaxy.com"
"\Directory";"\Users";"Files of users";"Proposed;dataowner@datagalaxy.com"

For primary keys import, the file to be imported must always contain the following columns:

The Table object path
The column name of primary key
+ a column for each attribute that is part of the object card that you want to import

Example of a CSV file to import in a Database type source:

"TablePath";"TableType";"ColumnName";"PKTechnicalName";"PkOrder"
"\pays";"\Table";"id";"PK_PAYS";"1"

For foreign keys import, the file to be imported must always contain the following columns:

The Table object path of primary key
The column name of primary key
The Table object path of foreign key (child)
The column name of foreign key
The technical name of primary key
The technical name of foreign key

Example of a CSV file to import in a Database type source:

"FKTechnicalName";"PKTechnicalName";"PkTablePath";"PkTableType";"PkColumnName";"ChildTablePath";"ChildTableType";"ColumnName"
"FK_PK_PAYS";"PK_pays";"\pays";"\Table";"id";"\map";"\Table";"id_pays"

For functional keys (relations) import, the file to be imported must always contain the following columns:

The Parent Table object path
The Parent Table object type
The Child Table object path
The Child Table object type
The functional label of relation

Example of a CSV file to import in a Database type source:

"ParentTablePath";"ParentTableType";"ParentTableTechnicalName";"FunctionalLabel";"ChildTablePath";"ChildTableType"
"\AGG_COMMISSIONS_MOIS";"\Table";"Aggregate at the monthly level";"\FAIT_COMMISSIONS";"\Table"

How to do it?

In this example, we will update a relational database with a import of structures:

1. Go to the Dictionnary module

2. Click on the 3 dots icon

3. In the drop-down menu, click on Import
4. Select or drag and drop your file to be imported into the CSV File area available at the top left.

5. Select Structures in the Dictionnary module

The central area allows you to adjust the technical parameters of the file (encoding, separator) and the table gives you an overview of the file content.

When selecting an item in the Dictionary module, the source setting is mandatory. You must choose one of the following options:

Create a new source: allows you to create a source and import the elements in it
Update an existing source: allows you to import elements into an existing source

In the example, select an existing source and click on the Next button

6. The next step allows you to map properties and perform functional checks before importing. When possible, an automatic mapping between source and target fields is performed and the verification of your file is launched.

If the mandatory fields are not mapped, the verification is not launched and the import is not possible.
if the check is sucess, the import is avaible (the Import button is immediately active).
if the check is failure, the checking are in the Cheks tab.

By clicking on the check status, the tab opens with the list of failures checking has been detected:

When a blocking error can be solved, the solution is in the "Action" column.
These controls are mandatory and must be re-launched when a modification in the Mapping tab is made.

7. Check the accuracy of the fields to be recovered in the Mapping tab

You have to drag the fields of the left column on the corresponding fields on the right.
Only the fields type and path, identified in red, are mandatory.
For files with many attributes: it is possible to search for attributes and hide the fields used by checking the box.

NB : In the case of updating your objects via this import, if you import empty values of the attributes in your file, which are already filled in your object cards, these empty values will be ignored by default and your existing values are saved. But, by checking this option (figure below), they will empty the existing values.

8. The Preview tab allows you to preview the values for each attribute to be imported, for which a match has been defined.

9. When these steps are completed and the check is successful, the Import button becomes active. Click on Import to launch the import. A confirmation message display with the number of elements impacted by the import or a error message with a table listing the errors found.