Data Import Tool (CSVimp)

The data import tool CSVimp is built by xTuple, for xTuple ERP. Explore this in-depth article to learn how this useful tool can simplify your data import projects.

Introduction to CSVimp

CSVimp is a tool designed to import Comma Separated Value (CSV) files into the database for xTuple ERP.This guide discusses the following topics:

• Basic Structure

• Understanding the xTuple databases

• Important tables and views

• CSVimp concepts

• Screen shot tutorial

While using CSVimp, a couple of other tools may also prove useful when exploring and working with the xTuple Database schema:

PgAdmin

• A powerful administration and development platform for the PostgreSQL database, free for any use. The application runs under Linux, xBSD, Mac, and Windows.
• Designed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. The application also includes a query builder, an SQL editor, a server-side code editor and much more.
• pgAdmin III is released with an installer and does not require any additional driver to communicate with the database server.

PostgreSQL Autodoc

• A utility which will run through PostgreSQL system tables and generate output which describes the database. The following output formats are available: HTML, Dot, Dia, and DocBook XML.

In addition to these tools, you can also check for additional resources in the forums section of the website.

Basic Structure of CSVimp

The import utility provides a simple way of migrating data objects to the xTuple database. Software is available to support Windows/Linux/Mac platforms.

The utility has three sections:

• Data section
• Map section
• Import section

Before Importing data it is important to understand the database at a higher level.

• Database: Can be divided into schemas. A schema logically separates database objects while allowing them to reside in the same database and interact with one another.

• Schema: Consists of database objects namely aggregates, functions/procedures, triggers, sequences, tables, views etc. The xTuple Databases contain two schema: "public" and "api". All data records and business logic for the xTuple Applications are stored in "public." The "api" schema contains the API views.

• Business Logic: Stored in database objects called Procedures (in pgAdmin these are listed under the Functions object) and the physical data is stored in Table objects.

The xTuple databases have over a hundred relations.

Naming Conventions

This naming convention becomes important when you are attempting to deduce relations.

1. A table name comprises of two parts <Table name><Field name>

   Example: The table cust has fields named: cust_name, cust_number, cust_state, etc.

2. Each table has an "id"

   Example: The field cust_id in the cust table.

3. In case of inter-table relation the field name will be <table name><referenced field name>

   Example: The cust table references the custtype_id field in the custtype table. It follows, then, that the name of the reference field on the cust table is cust_custtype_id.

CSVImport can migrate data into database tables and API(Application Programmer Interface) views. An import into a view helps in preserving data integrity and in some cases imports data into multiple tables simultaneously.

List of Important Tables

The following table is by no means an exhaustive list. However, it should give you a good basis for knowing which tables you will want to examine more closely when migrating.

Relational Dependencies

Below is a list of tables that define groupings of customers, vendors, and items. This list forms a partial list of relational dependencies that must be considered before importing data into xTuple:

As a general rule, we recommend populating all references with appropriate information. However, be sure to take particular care to populate the following: 

API Views

All data records and business logic for the xTuple Applications are stored in a schema called "public." The API views are stored in a separate schema called "api." Schemas provide a way to logically separate database objects from one another while still allowing them reside in the same database and interact with one another. The business logic is stored in database objects called Procedures (in pgAdmin these are listed under the Functions object) and the physical data is stored in Table objects.

When CSVimp or any other application performs a SQL SELECT, INSERT, UPDATE, DELETE or other command on a View, it executes the logic contained in the View itself and utilizes database procedures on one of more physical tables. This means that utilizing a View can result in data manipulation across multiple tables while preserving referential integrity through application logic.

Understanding How to Load Data

Loading data into CSVimp includes loading the files, mapping the data to actual table fields and importing the data.

Data Section

The Data section of CSVimp is the area where you load the CSV files you want to import into your xTuple Database.

1. From the menu select option File--->Open CSV.

2. If the first line of the CSV file is a header line, you can check the box “Treat first row as header information” option located at the bottom of the screen

Map Editor

What Is a Map?

A map defines the relationship between the columns of a specific CSV file data set and the fields of a single table or API view located within a database. Maps are saved in an XML file format. A collection of a map is called an atlas.

The creation of maps requires both an understanding of the data in a CSV file and the data as it should be stored in the database. When you first create a map, you will be prompted to provide a name for the map. This name should be brief yet descriptive. Once you have entered a map name, you will be prompted to identify the database table or API View the map is intended for. Once you have selected the table or view, your new map will be selected and you will be presented with the map information.

Map Types

There are three types of maps and each one is handled slightly differently.

1. Import Maps: Inserts the data into the database as simple inserts.

2. Update Maps: This option exists but the functionality has not been fully developed. The intention is that it will update any existing records on the database matching records to those fields marked as Key Fields.

3. Append Maps: This option exists but the functionality has not been fully developed. The intention is that it will insert records into the database when the fields marked as Key Fields do not match an existing record.

How to Create a Map

• Before creating a map it is important to know how data is arranged in a CSV file and the \]]\data as it should be stored in the database.
• When creating a map we are first prompted to provide a Name for the map. Provide a brief yet descriptive name.
• After entering the map name a prompt asks us to identify the database table / API view.

• Once we have selected the table/view , new map will be selected and map information will appear.

Pre and Post Processing

Maps also contain fields where SQL statements can be entered. There are fields where pre-SQL and post-SQL statements may be entered. Pre-SQL statements are executed before and post-SQL after the import respectively. Any form of SQL may be entered into these fields. The SQL enables us to perform any additional processing that may be required.

The pre-SQL field has an option to “Continue On Errors”. By default, if an error is detected when the pre-SQL is executed, the import will be terminated. If the “Continue On Errors” option is checked, then the import will continue even if an error is detected.

Field Mappings

The Field Mappings area is where we specify the actual map. Each row represents a single field on the destination table or API view we are importing into.

Import Section

Once you have loaded your CSV file and also loaded/created the appropriate map, you are ready to start the import process.

• When you start an import, (Import | Start) you will be asked to select the map you want to use.
• The import will start once you have selected the map. A log window can be viewed from Import | View Log.
• This log window will show any log messages generated from imports during the current session.

Screen Shot Tutorial

By now we are conversant with the concepts of CSVimp. Using this tutorial will enhance our understanding of the various features of CSVimp . It shows us how to....

• Log-in to the tool
• Open the file in the tool
• Create a map
• Use of Pre-SQL and Post-SQL tab
• Mapping the field relationships and importing

Double-click on the CSVimp shortcut. On starting the CSVimp application we will see the Log In screen. The OPTIONS button displays the Login Options dialog and it is here that you reference the IP address of server name for your Postgres server, the name of the database into which you want to import, and the port to which Postgres is set to listen (unless changed in the Postgres configuration file, this will be 5432).

File Preview

The screen below shows the main CSVimp screen called CSV Tool. The source CSV file has been opened using File | Open CSV. Note that the “Treat first row as header information” has been checked because the first row of the CSV file contains column heading descriptions.

Map Creation

Next, the creation of the Map is initiated. This is done with the option Map | Edit and then clicking the ADD button and providing a name (shown below). If we are working with an existing map such as one we created previously, simply click File | Open and locate the mapping file.

New Map

If we are creating a new Map, you will be prompted immediately with the Select Table dialog (see below). Note that API views are prefixed with “api.” because unlike tables which reside in the public schema, API Views are their own “api” schema.

CSV Atlas

Next we are presented with the CSV Atlas screen . Currently only 'Insert ' is functional. Below it is a field for providing a Description for your map.

Data Manipulation Using SQL

We may optionally click on the Pre SQL tab and enter SQL that is performed before the insert is executed. In the example shown, a technique is employed that is common when importing into an empty DB and trial and error is expected. The line (DELETE FROM accnt;) deletes all rows in the table before the insert. So, if our previous import did not provide the desired results, this SQL statement removes those result before the next import is executed.

The Post SQL tab enables us to define SQL that is executed after the CSV insert is performed. In this example, the Quick Books account type was stored in the xTuple Chart of Accounts Comment field during the import. Then, after the import finishes, this SQL updates the newly imported Accounts with the proper xTuple Subaccount Type based on the value found in the Comments field. This SQL also sets each Account to the proper Account type based on the value in the Comments field.

Field Mappings

In the Field Mappings section of the screen we define the relationship between columns in the xTuple DB table accnt and columns in our CSV import file. This example also uses the UseAlternateValue option. For example, all imported accounts were given the value Q (equity) for accnt_type. Then, the Post SQL looked at the value that was placed in the accnt_comments column and updated each account to the correct xTuple account type. (Please refer to the field mappings table in this doc.)

We initiate an import with Import | Start found on the CSV Tool screen. If errors are encountered we see a Log screen immediately. We can always call up this screen with Import | View Log. If there are no errors we receive the message “Your import was completed successfully.” 

Below we see the xTuple Chart of Accounts screen and the details for a specific account displayed in the Account Number screen.

This data was originally exported from QuickBooks into a CSV file. The CSVimp tool was then used to define a map the relates the columns of the CSV file to the columns in the xTuple application table accnt along with Pre SQL and Post SQL. The result is a fully defined and usable Chart of Accounts in the xTuple application.