User interface (CSV import)
The following screenshot shows the user interface to upload CSV information to create literature reference entities in IUCLID.
Upload of CSV information
For each of the supported IUCLID entities, the IIP supplies a predefined Excel template plus an example with test data in CSV format – see the “Administration” perspective or check the download page. The data has to be supplied in this predefined format. Please make sure to choose the correct template for the IUCLID format of your IUCLID server.
The first row of the Excel / CSV contains the technical names of the fields in IUCLID that are being filled. In the download section you find a Word document for each target IUCLID entity, that contains a tabular description of the entity, explaining each field including the technical field name, as used in the input template. Also, in the attribute mask, when you move the mouse on the type symbol before the label, the field name will be displayed as well, as in the following example:
Each entity in IUCLID can contain single-value fields and multi-value fields (e.g. multiple attachments), the latter either as individual field or as part of a repeating block of fields, like a tabular row. Therefore, the input CSV file can contain multiple lines for one entity, where in each line the different values for one (or many) repeating attributes can be supplied. In order to clearly assign a line in the CSV to one or another entity, the first column of the CSV contains the value for the “primary key” of the respective IUCLID entity. Each line in the Excel belongs to the entity defined by the value of the primary key – or, when the value is not provided, to the preceding entity, where this value was defined in one of the preceding lines before.
The definition of the “primary key” is a definition done by the IIP, not by IUCLID. It allows the IIP to
- group information from multiple lines into a single entity when creating an entity
- search for already existing entities in IUCLID to be checked and eventually updated, to avoid the creation of duplicates: Prior to uploading information / attachments, the IIP checks in IUCLID for existing instances with the value of the primary key in the respective field in IUCLID. If an instance is found, the source data and the data in IUCLID is compared and the instance in IUCLID is update, if needed. The comparison is done using all business attributes. No duplicates are being created. Please note that this is done without knowing any UUID information in the CSV information. This is different from an import via an I6Z package in IUCLID, where the identity is solely determined by the UUID and not any business attribute value.
The definition of the “primary key” is defined per entity, see the sections further below.
Please note, that the IIP can not process Excel format directly – when coming from the Excel template the worksheet has to be saved in CSV format first.
The term “CSV” stands for “comma separated value”. More generally it stands for a file, where tabular information is separated by a given delimiter. This format can be generated directly by reporting tools or from Excel. To create a CSV that can be processed by the IIS please consider the following:
- Please refer to the example files in the CSV example folder (see perspective “Administration) for each entity
- The default delimiter is the semicolon (“;”), the delimiter can be changed on the “Administration” perspective (stored locally for each user). Please make sure that no data field (e.g. authors) contains the delimiter.
- Provide date values in the format YYYY-MM-DD. To avoid that Excel interprets your input and changes it to the local date format, use the prefix ‘ (apostrophe) before the date value.
- When you save the data to CSV based on the Excel template, you should save the file using the format option “CSV UTF-8 (comma-delimited) (*.csv)”. This sets the character encoding to UTF-8 BOM and preserves also characters outside of ASCII.
- You can verify your data by opening the resulting CSV file in a texteditor like Notepad++ priot to importing into the IIP. This editor can also show you the encoding. Make e,g. sure, that Excel does not save an integer 123456 as 123.456 as it is eventually displayed.
Job status, data validation and update
Each entity in the CSV will be one job entry in the initial “New” status. When the CSV data of the entity cannot be correctly validated (e.g. wrong date format, referenced attachment cannot be found), then the job status is Error_Import (a bomb icon).
When the job list is processed, the status changes to “Processing” and then either to “Created”, “Updated” or “Error”.
The operation of creation or update depends on the “primary key” (see sections further below
- An new entity in IUCLID is created, when no IUCLID entity with the same primary key is found.
- An entity in IUCLID is updated, when the primary key attribute is already present in IUCLID for that instance and the other business values / attachments are different from within IUCLID.
Job lifetime and logging
Jobs are being processed on the IIP. The effect of a job execution is displayed in the web browser and also added to a log file that is created in the same directory and with the same filename as the originating CSV file used to create the jobs. Multiple executions of jobs in the CSV file will be appended to the same log file.
When the IIP browser tab is closed or refreshed (F5), then jobs in status “Processing” will continue to be processed and the results will be written to the log file. Other jobs will be deleted.
HowTo Quick Guide
- Download, install, configure and start the IIP. Please use the latest versions of Chrome, Edge or Firefox as browser to display the application. Minimize the command window – and do not click inside the window during operation.
- In the IIP, in the Administration perspective, enter the information for your IUCLID instance that you want to fill.
- To connect to the IUCLID instance you have to supply the URL to IUCLID (e.g. “http://<hostname>:<port>” or “https://<hostname>:<port>”) and UserName/Password for an existing IUCLID user who has the access rights to create / update the respective IUCLID entities.
- Make sure the status information turns to “Connected” after saving, after a 30-60 seconds latest. If not, check the error information in the application or eventually the command window.
- Look at the Excel template, the Word definition and the sample data for the IUCLID entity you want to fill. See perspective “Administration” in the running IIP – or you can also download those resources separately on the download page, if you want to see those file prior to the installation. Make sure you choose the right templates for your major IUCLID version. The IUCLID format may change for major versions. If you use an intermediate IUCLID version, the format does not change. For more details please consult the ECHA webpage.
- Generate your own input data in CSV format based on those examples.
- Move your CSV file into the correct directory below the IIP import directory. This is a directory accessible to both the user as well as the IIP. For server installations please ask your IT admin what the name and location of this folder, as seen from your PC, is.
- Navigate to the perspective of the entity that you want to upload to (e.g. Literature Reference)
- Above the left pane, click on “Import CSV”, select your input data from the available files in the import folder and click OK. The left pane will populate and for sucessfully imported jobs the status information in the leftmost column in the left pane will show the job status NEW as icon
- To verify the jobs, click on each line to see details on the right pane
- When ready, click on “Execute Jobs” above the left pane. The information is now being processed and uploaded to IUCLID. Please note the status information in the leftmost column changing. Use the mouse to hover above the symbols to see their meaning. With the checkbox above the left pane you can hide sucessfully terminated jobs, to only see jobs with error status. For the selected job you see the job details in the lower left part. For successfull jobs you will get the IUCLID UUID identification and can click on this ID to open the instance in IUCLID directly. For errors you will be able to display the available error information.
- You can delete the jobs and repeat the above steps with the same CSV – in this case the IIP will detect that IUCLID already contains the correct information. If you change the data in the CSV the entity in IUCLID will be updated
Import IUCLID entities
The first two perspectives in the left bar each support the import of CSV data to create / update a specific IUCLID entity.
The literature reference upload uses the IUCLID field “Study no.” (technical field name “GeneralInfo.CompanyOwnerStudyNo”) as “primary key”.
When active, the demo mode uses the field “Report no.” to store the fixed string “Created by IUCLID Integration Platform”
The reference substance upload uses the IUCLID field “Reference substance name” (technical field name “ReferenceSubstanceName”) as “primary key.
When active, the demo mode uses the field “IUPAC name” to store the fixed string “Created by IUCLID Integration Platform”