Export CSV

Export CSV

Notes:

• The Export CSV function provides a direct copy of selected database table as tabulator separated text file. If you want to generate flles that give a strutured overview of descriptors or description data, you should prefer the Export … Lists or the Export Wizard (coming soon).
• The Export CSV function requires the “Bulk Copy” tool, which is part of a local Microsoft SQL Server installation. If “Bulk Copy” is not available on your computer, you will get an error message after opening the window.

To export the tables of the database in the a tabulator, comma or semicolon separated format, choose Data → Export → Export CSV … from the menu. A window as shown below will open where you can select the tables to be exported in sections Selection criteria and in the Tables for export.

To start the export click on the Start export button. By default the data will be exported into a directory <working directory>\Export\<database_name>. Click on the button to select a different target directory before starting export.

After export the tables are marked with green backgound, if table schema and data were exported successfully. If only the data were exported, this is marked with yellow background, if nothing was exported, the background is red. A detailled export report can be viewd by a click on the export result file name.  

Export Text

Export as text or RIS

To export data as a text file choose Data → Export → Export text or RIS … from the menu. A window as shown below will open. You have several options to adapt the format of the export. E.g. you may use a different style for the first author, specifiy the separator for the last author etc.

Text format

The first tab Export as text provides the plain text export. The colums that should be displayed can be specified in the list Displayed columns. A click on the Transfer to grid button to transfers the data into the data grid beneath the button. The sequence of the columns can be changed by dragging a column in the position of your choice. After setting the correct sequence and formatting options (e.g. Format of year - see below) click on the Transfer to text button. Then the text ready for export will be shown in the textbox below. Click on the Save to file button to store the data in the specified file.

If you select the option Generate tab separated  list and click on the Transfer to text button, the data as shown in the data grid will be inserted in a tabulator-separated text file and column titles will be inserted (see image below). Such a file could be edited with a spreadsheet application an re-imported using the Importwizard.

RIS format (Reference Manager)

The second tab RIS Export provides the export of data in the RIS format according to the commercial database ReferenceManager use the RIS-Export function.

Click on the Start export button to generate the RIS tagged format and on the Save button to store the result in the specified file.

Export Wizard

The export wizard provides a possibility to export the data selected in the main form. The data are exported as tab separated text file. The export may include transformations of the data as well as information provided by linked modules and webservices. Choose Data - Export - Export wizard from the menu and then select one of the export targets (Event, Specimen, ...). For a short introduction see the tutorial.  

Adding tables

There are the following ways to add tables:

• One parallel table
• Several parallel tables according to selected data
• Dependent table

All options will include the depending tables as defined for the default table. The option for several tables will add as many tables as there are found in the data.

If you added parallel tables, you should set the sequence of the datasets within these tables: For the columns that should be used for sorting the data, set the ordering sequence to a value > 0 and choose if the ordering sequence should be ascending or descending .

Adding related values

Certain columns in the database may provide information linked to another table or a module resp. webservice . Click on the button to add a linked value.

Adding and editing file columns

To add columns to the exported file, use the buttons. In the textbox at the top of the file column, you can change the header for the column. To change the position of a file column use the resp. button. To fuse a column with the previous column, click in the gray bar on the left side of the column that will change to for fused columns. To remove a file column, use the button. Pre- and postfixes for the columns can directly be entered in the corresponding fields. To apply transformations on the data click on the button.  

Filter

To filter the exported data, use the filter function. Click on the button and enter the text for the filter. Only data matching the filter string will be exported. If a filter is set, the button will have a red background to remind you of the filter. The filter may be set for any number of columns you need for the restriction of the exported data.  

Rowfilter

This filter in contrast to the filter above strictly applies to the row according to the sequence of the data. For an explanation see a short tutorial .

Test

To test the export choose the Test tab, set the number of lines that should be included in the test and click on the Test export button. To inspect the result in a separate window, click on the button.

SQL

If you want to inspect the SQL commands created during the test check this option. To see the generated SQL click on the SQL button after the Test export. A window containing all commands including their corresponding tables will be shown.

Export

To export your data to a file, choose the Export tab. If you want to store the file in different place use the button to choose the directory and edit the name of the file if necessary. Check the include a schema option if you want to save a schema together with your export. To start the export, click on the Export data   button. To open the exported file, use the button.

Export to SQLite

To export your data into a SQLite database, choose the Export to SQLite tab. You may change the preset name of the database in order to keep previous exports. Otherwise you overwrite previous exports with the same filename. To start the export, click on the Export data   button. To view the exported data, use the button.

Schema

To handle the settings of your export, choose the Schema tab. To load a predefined schema, click on the button. To reset the settings to the default, click on the button. To save the current schema click on the button. With the button you can inspect the schema in a separate window.

Import Text

Import tagged text

Reference Manager

To import data stored in the commercial database Reference Manager use the RIS-Export function of Reference Manager.

Import

Choose Data → Import -> Import tagged text … from the menu to start the import. A form will open where you can set the import options.

Filename: Click on the  button to choose the file you want to import from your file system.

Responsible: Select the user who is responsible for the imported data.

Abstracts: Select the language of the abstracts in the references.

Availability: If more than one filing code is placed in one field, enter the separator for the filing codes.

Keywords: Select the language of the keywords in the references. The keywords can be transferred either to the private descriptors or the public available the descriptors. If you use special signs to classify you keywords you can use the mapping to translate this classification into the descriptor concept in DiversityReferences. This is shown in the Mapping for descriptor elements section in the image above. 

Mapping User defined: The UserDefined fields within Reference Manager will be transferred to the corresponding fields within the table ReferenceTitle of DiversityReferences unless you specify it otherwise. If possible, use the more exact targets within DiversityReferences for your import.

Import options: You may either update data allready in the database and import new data from the source or just import new data.

Import format: Currently there is only one import format that is supported by DiversityReferences - Reference Manager (RIS).

Click the Start import button to start the import. You will be informed about import errors and the number of imported datasets by message windows. If any datasets have been imported, they will be displayed in the query list of the main window, when you close the import form (see image below).

Import wizard

The examples below are from the module DiversityAgents, but are valid for any other module as well.

With the current solution please ensure that there are no concurrent imports in the same database.

For some imports like e.g. for Collections in DiversityCollection you will be reminded to update the cache tables for the hierarchies.

With this import routine, you can import data from text files (as tab-separated lists) into the database. A short introduction is provided in a video . Choose Data → Import → Wizard → Agent from the menu. A window as shown below will open that will lead you through the import of the data. The window is separated in 3 areas. On the left side, you see a list of possible data related import steps according to the type of data you choose for the import. On the right side you see the list of currently selected import steps. In the middle part the details of the selected import steps are shown.

Choosing the File and Settings

• File: As a first step, choose the File from where the data should be imported. The currently supported format is tab-separated text. Choosing a file will automatically set the default directory for the import files. To avoid setting this directory, deselect the option Adapt default directory in the context menu of the button to open the file.
• Encoding: Choose the Encoding of the file, e.g. Unicode. The preferred encoding is UTF8.
• Lines: The Start line and End line will automatically be set according to your data. You may change these to restrict the data lines that should be imported. The not imported parts in the file are indicated as shown below with a gray background. If the
• First line: The option First line contains the column definition decides if this line will not be imported.
• Duplicates: To avoid duplicate imports you can Use the default duplicate check - see a video for an explanation.
• Language: If your data contains e.g. date information where notations differ between countries (e.g. 31.4.2013 - 4.31.2013), choose the Language / Country to ensure a correct interpretation of your data.
• Line break: With the option Translate \r\n to line break the character sequence \r\n in the data will be translated in a line break in the database.
• SQL statements: To save all SQL statements that are generated during a test or import, you can check the option Record all SQL statements.
• Schema: Finally you can select a prepared Schema (see chapter Schema below) for the import.

Choosing the data ranges

In the selection list on the left side of the window (see below) all possible import steps for the data are listed according to the type of data you want to import.

The import of certain tables can be paralleled. To add parallels click on the button (see below). To remove parallels, use the button. Only selected ranges will appear in the list of the steps on the right (see below).

To import information of logging columns like who created and changed the data, click on the include logging columns button in the header line. This will include additional substeps for every step containing the logging columns (see below). If you do not import these data, they will be automatically filled by default values like the current time and user.

Attaching data

You can either import your data as new data or Attach them to data in the database. Select the import step Attachment from the list. All tables that are selected and contain columns at which you can attach data are listed (see below). Either choose the first option Import as new data or one of the columns the attachment columns offered like SeriesCode in the table Series in the example below.

If you select a column for attachment, this column will be marked with a blue background (see below and chapter Table data).

Merging data

You can either import your data as new data or Merge them with data in the database. Select the import step Merge from the list. For every table you can choose between Insert, Merge, Update and Attach (see below).

The Insert option will import the data from the file independent of existing data in the database.

The Merge option will compare the data from the file with those in the database according to the Key columns (see below). If no matching data are found in the database, the data from the file will be imported. Otherwise the data will be updated.

The Update option will compare the data from the file with those in the database according to the Key columns. Only matching data found in the database will be updated.

The Attach option will compare the data from the file with those in the database according to the Key columns. The found data will not be changed, but used as a reference data in depending tables. 

Empty content will be ignored e.g. for the Merge or Update option. To remove content you have to enter the value NULL. As long as the column will allow emty values, the content will be removed using the NULL value.

Table data

To set the source for the columns in the file, select the step of a table listed underneath the Merge step. All columns available for importing data will be listed in the central part of the window. In the example shown below, the first column is used to attach the new data to data in the database.

A reminder in the header line will show you which actions are still needed to import the data into the table:

• Please select at least one column   = No column has been selected so far.
• Please select at least one decisive column   = If data will be imported depends on the content of decisive columns, so at least one must be selected.
• Please select the position in the file   = The position in the file must be given if the data for a column should be taken from the file.
• Please select at least one column for comparison   = For all merge types other than insert columns for comparison with data in the database are needed.
• From file or For all   = For every you have to decide whether the data are taken from the file or a value is entered for all
• Please select a value from the list   = You have to select a value from the provided list
• Please enter a value   = You have to enter a value used for all datasets

The handling of the columns in described in the chapter columns.

Testing

- To test if all requirements for the import are met use the Testing step. You can use a certain line in the file for your test and then click on the Test data in line:  button. If there are still unmet requirements, these will be listed in a window as shown below.

If finally all requirements are met, the testing function will try to write the data into the database and display any errors that occurred as shown below. All datasets marked with a red background, produced some error.  

To see the list of all errors, double click in the error list window in the header line (see below).

If finally no errors are left, your data are ready for import. The colors in the table nodes in the tree indicate the handling of the datasets:

• INSERT
• MERGE
• UPDATE,
• No difference
• Attach
• No data

The colors of the table columns indicate whether a column is decisive , a key column or an attachment column .  

If you suspect, that the import file contains data already present in the database, you may test this and extract only the missing lines in a new file. Choose the attachment column (see chapter Attaching data) and click on the button Check for already present data. The data already present in the database will be marked red (see below). Click on the button Save missing data as text file to store the data not present in the database in a new file for the import. The import of agents contains the option Use default duplicate check for AgentName that is selected by default. To ensure the employment of this option the column AgentName must be filled according to the generation of the name by the insert trigger of the table Agent (InheritedNamePrefix + ' ' + Inheritedname + ', ' + GivenName  + ' ' + GivenNamePostfix + ', ' + InheritedNamePostfix + ', ' + AgentTitle - for details, see the documentation of the database).

If you happen to get a file with a content as shown below, you may have seleted the wrong encoding or the encoding is incompatible. Please try to save the original file as UTF8 and select this encoding for the import. 

Import

- With the last step you can finally start to import the data into the database. If you want to repeat the import with the same settings and data of the same structure, you can save a schema of the current settings (see below). You optionally can include a description of your schema and with the button you can generate a file containing only the description.

Schedule for import of tab-separated text files into DiversityAgents

• Target within DiversityAgents: Agent
• Database version: 02.01.13
• Schedule version: 1
• Use default duplicate check: ✔
• Lines: 2 - 7
• First line contains column definition: ✔
• Encoding: UTF8
• Language: US

Lines that could not be imported will be marked with a red background while imported lines are marked green (see below).

If you want to save lines that produce errors during the import in a separate file, use the Save failed lines option. The protocol of the import will contain all settings according to the used schema and an overview containing the number of inserted, updated, unchanged and failed lines (see below).

Description

- A description of the schema may be included in the schema itself or with a click on the Import button generated as a separate file. This file will be located in a separate directory Description to avoid confusion with import schemas. An example for a description file is shown below, containing common settings, the treatment of the file columns and interface settings as defined in the schema.