Data import/export

This page contains information about how you can load simple data files into Scai and export data into csv files from Scai.

Load data file

By clicking the load file menu, a window will be open to load data from a file into the database. The file type can be one of csv, xls or xlsx.

Before trying to load any file into the database, please create a schema called scai into every data source you wish to load data to. By default, Scai will write temporary tables to store the data loaded by you.

The following steps should then be performed:

Select the path of the file you would like to load
For all files, the following elements should be filled in:
- File has header row? - if the file has a table header, which should be read or not.
  true if the file header will give the name of the columns
For .csv files, the following elements should be additionally filled in:
- Field separator - by default, the columns in csv are separated through ,. If you have a file that contains other field separator you can fill it in here.
- Escape character - by default, the escape character is \. This character is used to escape the quoting character inside field values.
- Quote character - by default, the quote character of columns is ". This character is used to surround a field value that contains the field separator.
For .xls(x) files, the following options are present:
- Sheet - the number of the sheet you would like to load. Sheets are numbered from 1 and in the order they appear in the XLS(X) document.
Once these options are filled in, you can click the Save button.
If the previous step is successful, a widget will be displayed containing the file columns (left) and the data loaded into a table (right). By default, the data will be displayed only with columns which have the data type TEXT. If the columns should be converted to other data types, the next steps can be applied:
1. Scai can also perform an automatic detection of the data types of the columns. You can activate this option, by clicking the Detect data types button.
  Pay attention that this feature may not always detect the best data types for the columns. That is why it is important to verify the data types are appropriate for the data loaded. The basic set of data types that Scai supports through the user interface are: date, timestamp, time, bigint, decimal, double precision, boolean, text.
2. Once you are satisfied with the selected data types, click the Change data types button. This will alter the data types to the ones selected.
3. You can add filters to reduce the number of records to a specific search
Next, there are two possible ways you can save the data into the database with Scai:
A. by creating a new table -> click the button Create new table (upper right). A new widget with the table schema and name will be presented on the left side and the data on the right side. Once you have filled in the table details, click the Save button located in the lower right side of the widget.
B. by loading the file into an existing table -> click the button Insert into existing table (upper right). You can select the name of the table you may want to save to. The available tables are sorted in an ascending order. Once a table is selected, a list of how to match the file fields to the columns of the table will be presented. For each column of the selected table you will see two checkboxes. This allows you to decide if you would like to:
- insert the database default value for that given column for each row inserted. In this case do not check any of the checkboxes.
- insert a static value for that given column for each row inserted. In this case you would check the Value checkbox and then enter the static value in the input that appears.
- insert the values of a field from the file loaded into that given column. In this case you would check the Column checkbox and then select the file field.
  Examples
  - You can set a null value for a given column for each row you insert by clicking the Value checkbox and leaving the input field empty.
  - To insert the values of the field 'A' into the column 'name' of a given table, you would check the Column checkbox next to the 'name' label and then select from the list field 'A'.
You can click the Save button, once you have found the table you are interested in and configured how to match the file fields to its columns. If inserting into an existing table was successful (i.e. no errors occurred due to mismatch in data types or the format of the data), you can click Go to table to redirect you to the inserted into table.

Troubleshooting possible errors

Some common errors you might run into are:

The CSV file is not appropriately formatted for the chosen options. For example, the fields are separated by tab characters instead of the chosen comma field separator, or the escape or quoting characters are used within field values. In this case you will probably see an error with the row that failed parsing
The File has header row? checkbox has been checked, but some of the header names are empty. In this case the ScaiPlatform will give an error since these header names are used to give names to table columns and these cannot be empty.
The XLS(X) file is not properly formatted. The data in the spreadsheet file loaded must be in tabular format, with constant number of fields for each row.
The schema scai does not exist in the connected to data source. When loading files, the ScaiPlatform creates a (temporary) table to store the contents of the file before deciding what to do with them (i.e. insert them into an existing table or create a new one). These temporary tables are created by default in the scai schema.
Encoding issues. If the loading fails with parsing the CSV file due to encoding issues, make sure that the file is UTF-8 encoded. At this moment, the ScaiPlatform only supports loading of UTF-8 encoded CSV files.

Export

For each table or view that you can access in Scai, as well as for each widget that is mapped to a list task in the workflow module, you will have an Export link in the upper right part of the table/view widget. When you click it, you will be asked to save/open a csv file. This file contains all the table/view data. If you have added filters, then only the rows matching those filters will be part of the exported file.