+1-757-461-3022 x124

Data Import Tool (CSVimp)

Introduction to CSVimp

CSVimp is a tool designed to import Comma Separated Value (CSV) files into the database for the xTuple Applications (PostBooks® and OpenMFG).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

Note: The Map, an XML file, and the CSV source file are available on SourceForge in the CSVimp package under the PostBooks® project.

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.

  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.

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.

  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.

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: 
Default: The default value according to the database schema is used 
Use Column: The column specified in the Column # field is to be used 
Use Empty String: An empty string is to be used for this field
UseAlternateValue: The value from the Alt. Value is to be used 
Use Null: Uses a NULL value

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: 
Nothing: Do nothing - use the NULL value
Use Default: Use the default value according to the database schema 
Use Empty String: Use an empty string 
UseAltenateValue: Use the value specified in Alt. Value
Use Alternate Column: Uses the data in the column from the CSV data file specified in the Alt. Column #

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: 
Nothing: Do nothing - use the NULL value
Use Default: Use the default value according to the database schema 
Use Empty String: Use an empty string 
UseAlternateValue: Use the value specified in Alt. Value

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 wecreated previously or the example provided on SourceForge, 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.

up
120 users have voted.