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 modal will be open to load data from a file into the database. The file type can be one of csv, xls or xlsx.The following steps should then be performed:

  1. Select the path of the file you would like to load

  2. 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

  3. 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.
  4. 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.
  5. Once these options are filled in, you can click the Save button.

  6. 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 whether these are indeed the data types to which you may want to have the data saved. The 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. If you would like to add filters and to reduce the number of records to a specific search, this is easily possible by adding filters on columns.

  7. Next, there are two possible ways you can load the data into the database with Scai:

    A. by creating a new table -> click button Create new table (upper right). A new widget with the table schema and name will be present on the left side and the data on the right side. Once you have filled in the table details, click Save button located in the lower right side of the widget.

    B. by loading the file into an existing table -> click 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 field from the file that you would like to map to the given column.

      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.

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.