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:
- 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
Understanding the Database Structure
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.
-
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.
- Each table has an "id"
Example: The field cust_id in the cust table.
-
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.
Note: This rule has some exceptions. One, when a table references another table multiple times, then few additional descriptive words are added. For example, the table salesacct which references acct_id several times, so, descriptive words are added like salesacct_credit_acct_id. Secondly, when a business logic /conditional logic requires a field to refer more than one table. Such a scenario may occur during conversions.
Important Tables
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.
Table Name |
Description |
acct |
G/L Account Master |
addr |
CRM Address Master |
aropen |
Accounts Receivable Open Items |
apopen |
Accounts Payable Open Items |
arapply |
Accounts Receivable Applied |
bomhead |
Bill Of Materials Header |
bomitem |
Bill of Materials Item |
boohead |
Bill Of Operations Master |
booitem |
Bill Of Operations Item |
crmacct |
CRM Account Master |
cohist |
Sales/Customer Order History |
custinfo |
Customer Master Information |
salesrep |
Sales Representative Information |
invhist |
Inventory History |
item |
Item Master Information |
itemsite |
Item Site Master |
salesrep |
Sales Rep Master |
vendinfo |
Vendor Master |
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:
Table Name |
Description |
custtype |
customer types |
vendtype |
vendor types |
plancode |
planner codes |
classcode |
class codes |
prodcat |
product categories |
salesrep |
sales reps |
whsinfo |
warehouses |
Mandatory Table Dependencies
As a general rule, we recommend populating all references with appropriate information. However, be sure to take particular care to populate the following:
Name of Table |
Fields to populate |
custinfo |
cust_custtype_id, cust_salesrep_id |
vendinfo |
vend_vendtype_id |
item |
item_classcode_id, item_prodcat_id |
itemsite |
itemsite_item_id, itemsite_warehous_id, itemsite_plancode_id, itemsite_costcat_id |
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.
View Name |
Description |
custchar |
Interface to import Customer Characteristic data directly into the system. On Import required fields will be checked and default values will be populated. |
custcomment |
Interface to import Customer Comment data directly into the system. On Import required fields will be checked and default values will be populated. |
custcreditcard |
Interface to import Customer Credit Card data directly into the system.The correct encryption key must to be passed on insertions for data to be readable in the system; the key should NOT be STORED on the same server as the database in any way. Only insertions are allowed. Select statements will be encrypted. Use the GUI interface to view and process credit cards. |
customer |
Interface to import Customer data directly into the system.On Import required fields will be checked and default values will be populated. |
custshipto |
Interface to import Customer Ship-to data directly into the system.On Import required fields will be checked and default values will be populated. |
custtax |
Interface to import Customer tax registration data directly into the system. Required fields will be checked |
quote |
Interface to import Quote Header data directly into the system. Required fields will be checked and default values will be populated. |
quotecomment |
Interface to import Quote Comment data directly into the system. Required fields will be checked and default values will be populated. |
quoteline |
To import Quote Line Items data directly into the system. Required fields will be checked . |
quotelinecomment |
To import Quote Line Item Comment data directly into the system. Required fields will be checked and default values will be populated. |
salesline |
To import Sales Order Line Items data directly into the system. Required fields will be checked. |
saleslinechar |
To import Sales Order Characteristic data directly into the system. Required fields will be checked and default values will be populated. |
saleslinecomment |
To import Sales Order Line Item Comment data directly into the system. Required fields will be checked and default values will be populated. |
salesorder |
An interface to import Sales Order Header data directly into the system. Required fields will be checked and default values will be populated. |
salesordercomment |
To import Sales Order Comment data directly into the system. Required fields will be checked and default values will be populated. |
Note: API Views are stored in a separate schema called "api". There are Views in the public schema but these are simply logical representations of physical data and do not implement the application logic contained in and executed by the API Views.
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.
-
From the menu select option File--->Open CSV.
-
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.
-
Import Maps: Inserts the data into the database as simple inserts.
-
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.
-
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.
Options |
Action / Values |
Key field |
Specifies if this field should be treated as a key field for the purpose of Update and Append Maps (not implemented). |
Field |
Name of the field on the destination table for the database. |
Type |
Is the data for the field. |
Required |
Indicates if this field is required on the database schema. Sometimes the default value on these fields are sufficient to meet the database requirements while other times they are not |
Use Value |
These options indicate what value should be used for this field: |
Column # |
The column # specified is to be used from the CSV file |
Column# (If Null) |
If the value read from Column # results as a NULL value then you can provide additional processing according to the selected action specified: |
Alt. Column #: |
The column # specified is to be used from the CSV file if the first column was NULL and the If Col. Null action was Use Alt. Column. |
Alt if Null |
If the value read from the Alt. Column # results as a NULL value then you can provide additional processing according to the selected action: |
Alt. Value |
This is any text you want to be passed to the field if the options UseAlternateValue is specified |
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.