Why Use

Features

Sys-Dev

 Use Cases

Companies

Documentation

Contact Us

Downloads

PDF of current page

The CSV Importation (and export) Utility

The Orixa System includes a dedicated set of tools to assist with the import/export of data into/from the database using "CSV" files.
This is a technical process and should only be undertaken with care.
A Developer importing data should first test the importation process on a test database, and make a backup of their main database before running the import/export process if they are at all unsure about how it will work. 

Imported data will always be added with ID values that are sequential, so if a large-scale importation occurs and is incorrect it can be undone using a SQL DELETE statement passing in the range of IDs.

Data Importation /Exportation

Orixa uses strongly relational data. This means that many fields in an Orixa App hold "ID" data, stored in the database as numbers, which link to other data. For exporting data, provided SQL scripts are crafted to generate a human-readable output the process is fairly straightforward. However for importation the relational nature of Orixa makes data-importation significantly harder. For Importation to work, either the imported data must be relatively simple in structure, or a multi-step importation is required, to map text values that are being imported across to the "ID" values which Orixa stores.

For detailed coverage of data-importation, please see the following link:

Data Import and Export using SQL procedures

 If you are importing relatively straight-forward data into a single data-table in your App, then the CSV Importation Utility can be extremely useful. It can also be used to export and import data, allowing data to be passed from one Orixa App to another. The utility can also be used in more complex cases, as part of a multi-part importation process.

Before you begin

  1. You will need a CSV file which contains data in a suitable format for importation. Data must be separated by commas, and if items such as dates are present all dates must be formatted in the same way. If one date is written: 04/02/2020 and another is written Oct-2020 the importation will fail. Please take time to ensure imported data strictly meets the data-requirements of Orixa before trying to import it.
  2. Copy this CSV file into a Store on the server where the App can access it for importation.
  3. The main purpose of the CSV Importation Utility is to import pre-prepared CSV Files which may have been created by another Orixa App. For example you can use the CSV Importation Utility to Export data into a CSV file on one system, and then import it into another.

Ensuring you have the correct CSV File Format

Correct CSV File Format  

Unfortunately CSV Files come in many formats. Although the name means "Comma Separated Values" and you would think thatall CSV files would be identical, advances in how data is stored mean that there are now at least 5 competing versions of the CSV data-format. When you are saving data in simple editors (such as Notepad) this is not an issue, the standard CSV file is used. However in Excel, and other programmes many different CSV options are available. Be sure to use the most basic one: "CSV (Comma Delimited)", other CSV formats add special formatting which may cause problems during the importation process.

Open and use the CSV Importation Utility

Opening the CSV Import Utility  

Find the CSV Importation Utility in the System Menu, as shown at "1.", in the image above. Note that only users with Developer SecurityLevel will see the System Menu.

Accessing a CSV file to import  

Once the CSV Utility is open, it shows a user-interface to allow access to CSV Files which can be imported. To increase security it is only possible to import CSV files which are copied into a database store on the server. This makes it impossible for general users to run the CSV Utility and import random data into the database.

  1. Store list. All the stores in your App are shown here. Click on anyone to activate it.
  2. Once a Store is selected, if any CSV Files are present in that Store they will display in the Files List.
  3. Once a file is selected in the Files List, it will be added as a "Source File"

Picking a Target Table and the Fields to Import

 

Pick Table  

Once you have selected the file you want to import, click on "Pick Table" and a list of all the data-tables in your App will open. Click on one to pick it as a target for the importation process.

Elements of the CSV Import Utility User Interface  

Once you have picked a table set other values as per your requirements.

  1. List the "Fields to Use" in the importation process. Note that there must be a precisely one-to-one relationship between the list of fields and the number of comma-separated columns in the CSV file.
  2. If you want to compare the field names with the columns in the CSV File you can click "Open CSV File" or "Show Table" to see the CSV file and Table-structure respectively.
  3. The Importation Utility allows the user to select the date-format they wish to use. This should exactly match the date-format used in the CSV file.
  4. As you undertake steps using the CSV Importation Utility a memo will update with your actions so you can see what you have done.
 

Import Now / Export Now  

 Once you have set up the Utility so you are happy with it, click "Import Now" to import data from the CSV File into your database.

Click "Export Now" to export the selected table, and Fields to Use to the selected CSV File.

The CSV Importation Utility creates a wrapper for the SQL IMPORT TABLE statement. Developers can write SQL to undertake the same steps that occur when you select items in the Utility. Details on how to do this, are available in the link close to the top of this document.