Difference between revisions of "Data Import"

From ADempiere
Jump to: navigation, search
This Wiki is read-only for reference purposes to avoid broken links.
(suggesting tutorials)
(Intermediate save - more to follow.)
Line 51: Line 51:
 
* If the Data Type is Date, be sure to indicate the format and watch for differences between systems, such as dd/mm/yy vs. mm/dd/yy.
 
* If the Data Type is Date, be sure to indicate the format and watch for differences between systems, such as dd/mm/yy vs. mm/dd/yy.
 
{{Note|As you create the Import Loader Format, it is helpful to create header rows in your spreadsheet that match the '''Start No''' and '''Name''' columns.  However, when you save the spreadsheet to CSV, it is best to not save the header rows since these do not contain valid data.  You can delete them before the save or add another sheet to the workbook that displays all the data from the first except for the header rows and then only save that sheet as a CSV file.}}
 
{{Note|As you create the Import Loader Format, it is helpful to create header rows in your spreadsheet that match the '''Start No''' and '''Name''' columns.  However, when you save the spreadsheet to CSV, it is best to not save the header rows since these do not contain valid data.  You can delete them before the save or add another sheet to the workbook that displays all the data from the first except for the header rows and then only save that sheet as a CSV file.}}
 +
 +
=== Preparing the data for Import ===
 +
 +
In an ideal world, you want to perform the data import to the intermediate table without errors and then process the intermediate table, also without errors.  Errors within ADempiere take more effort to fix than applying the same corrections to a spreadsheet.  So, if at all possible, prepare the data in a spreadsheet before attempting the import. 
 +
{{Note| If you have too much data to deal with or no control over the format, you may have to consider developing a customer import loader.  See the section [[#For Developers|For Developers]].}}
 +
To properly prepare the data, it is important to understand the import process and what the Import File Loader and the Import processes are looking for.
 +
{{Note|First, review the concepts of [[Key Concepts#Database Tables and Common Fields|Database Tables and Common Fields]].}}
 +
Assuming your data has been read properly by the Import File Loader and is now sitting in the Import Table, when you press the button to perform the import, the import processes generally follow this sequence.
 +
 +
For each record in the import data table that has not previously been imported, the software assigns default values to the mandatory fields (AD_Org_ID, AD_Client_ID, Created, CreatedBy, IsActive, Updated, UpdatedBy) and sets two other fields: I_ErrorMsg = ' ' and I_IsImported = 'N'.
 +
 +
The software then tests the field values looking for errors.  If one is found, a suitable error message is saved in the field I_ErrorMsg.
 +
 +
==== IDs and Value Fields ====
 +
 +
It is important to understand the differences between the Value/Search Key, Name and ID fields.  The ID fields are usually hidden numerical keys that distinguish one record from another.  Their value is controlled by the software and they are often not available for searches.  The Value fields, on the other hand, are visible, often updateable and frequently used as the unique key for searches.
 +
 +
For example, a Business Partner in the database may have a Name "C&W Construction", a Value "C&W" and a C_BPartner_ID field = 117. 
 +
 +
When you import a table directly, the ID field is assigned by the software and you can set the key value to what ever you want.  It is pretty straight forward.  However, if you want to link your import data to data that is already existing in the database, for example connecting an invoice to a business partner such as "C&W Construction" you have to import the Value field "C&W" or Name so the software can link that to the C_BPartner_ID field 117.
 +
 +
The same approach applies to many of the look-up or reference fields.  The database may use hidden keys for these fields that need to be matched by name.  To import the data, you have to know the name, not the key.
 +
 +
In some cases, if related records are not already in the database, the import process will try to create them. For example, if unknown business partners are included in import invoice data, the business partners will be added to the database.
 +
 +
==== Cross Linking Data ====
 +
 +
Where the import data uses different data formats than the database, you may need to perform a data translation using intermediate tables that relate the keys in the external data to the keys in the database.
 +
 +
==== Scripts and Advanced Processing ====
 +
 +
In some cases, the import data can be processed by scripts and rules to prepare it for processing.  This is an advanced topic covered in the [[Table of Contents#Developer Documentation|Developer Documentation]] section of the manual.
 +
 +
== Fields and Defaults ==
 +
 +
For advice on which fields are mandatory or optional, see the individual Import window pages:
 +
 +
* {{WindowRef|ImportBusinessPartner|Import Business Partner}}
 +
* {{WindowRef|ImportProduct|Import Product}}
 +
* {{WindowRef|ImportProductPlanning|Import Product Planning}}
 +
* {{WindowRef|ImportPriceList|Import Price List}}
 +
* {{WindowRef|ImportAccount|Import Account}}
 +
* {{WindowRef|ImportReportLineSet|Import Report Line Set}}
 +
* {{WindowRef|ImportInventory|Import Inventory}}
 +
* {{WindowRef|ImportOrder|Import Order}}
 +
* {{WindowRef|ImportInvoice|Import Invoice}}
 +
* {{WindowRef|ImportConfirmations|Import Confirmations}}
 +
* {{WindowRef|ImportCurrencyRate|Import Currency Rate}}
 +
* {{WindowRef|ImportGLJournal|Import GL Journal}}
 +
* {{WindowRef|ImportPayment|Import Payment}}
 +
* {{WindowRef|ImportBankStatement|Import Bank Statement}}
  
 
== For Developers ==
 
== For Developers ==
Line 58: Line 109:
 
** org.compiere.apps.form.VFileImport.java
 
** org.compiere.apps.form.VFileImport.java
  
The software the performs the import can be found in:
+
The software that performs the import can be found in:
 
* base/src
 
* base/src
 
** org.compiere.impexp.ImpFormat.java
 
** org.compiere.impexp.ImpFormat.java
Line 64: Line 115:
 
** org.compiere.impexp.MImpFormat.java
 
** org.compiere.impexp.MImpFormat.java
 
** org.compiere.impexp.MImpFormatRow.java
 
** org.compiere.impexp.MImpFormatRow.java
 +
 +
The software that process the import can be found in
 +
* base/src
 +
** org.compiere.process.Import<table>.java where <table> is the import table name.
  
 
The following articles provide examples and tutorials:
 
The following articles provide examples and tutorials:
 
*You can refer to [[Red1.org]] to look at one such tutorial pack [http://www.red1.org/compiere/XML2AD.zip XML2AD] that reveals how the ImportLoader is under the hood.  
 
*You can refer to [[Red1.org]] to look at one such tutorial pack [http://www.red1.org/compiere/XML2AD.zip XML2AD] that reveals how the ImportLoader is under the hood.  
 
*A simpler example will be [http://compiere.red1.org/ImportPriceSetup.zip ImportPrice Loader].
 
*A simpler example will be [http://compiere.red1.org/ImportPriceSetup.zip ImportPrice Loader].

Revision as of 21:08, 11 August 2010

Under construction.gif This page is incomplete.

Please help adding more relavant content to the page. Create a user account in Adempiere wiki today. You can click on the edit button and start fixing the content.

Table of Contents{{#if: Standard Features| | Standard Features }}{{#if: System Administration| | System Administration }}{{#if: | | [[{{{4}}}]] }}{{#if: | | [[{{{5}}}]] }} | Data Import{{#if: | | [[{{{next}}}]] }} ⇒

The Data Import features of ADempiere allow you to import data into the database as part of the process of establishing opening balances, entering historical data or for routine entry of orders, invoices and general journal entries.

Introduction

The data import process has two main steps. Data is read into the system and placed in a temporary table. From there it is processed and entered into the main database. The two step process helps prevent errors in the data from affecting the main database. The first step of importing the data requires a definition of the data file that will be imported and information about where to put it in the temporary tables. This is performed by an Import File Loader and an Import Loader Format.

Import Loader Format

The {{#if: Import Loader Format|Import Loader Format|ImportLoaderFormat }} Window is simply a window that defines the target intermediate table and a list of the fields that are to imported. In the {{#if: |{{{3}}}|Import Format }} Tab, you define both the import table and the format of the data to be imported.

The following import tables are available as a default:

  • Business Partner
  • Product
  • Product Planning
  • Price List
  • Account
  • Report Line Set
  • Inventory
  • Order
  • Invoice
  • Confirmations
  • Currency Rate
  • GL Journal
  • Payment
  • Bank Statement
Note.gif Note:

This is simply a list of all tables in the database with the prefix I_. (See the validation rule AD_Table Import Tables.) If, for example, the target table was C_BPartner, the import table would be I_BPartner. The relation with the target table is not one-to-one. The import tables are used to create subordinate information as well. The I_Invoice table is used to create invoice headers and lines. The I_GLJournal table is used to create journal batches, journal entries and the journal line items.

While you can add tables simply, the processing required to import the data from the import table to the target table is involved and the subject of other articles. See the section For Developers

The import formats supported for the data file you wish to import include:

  • Comma Separated (CSV)
  • Custom Separator Char
  • Fixed Position and
  • Tab Separated

To create a new Import Loader Format based on your data, login to your client and browse to System Admin{{#if: Data |  » Data }}{{#if: Import Loader Format |  » Import Loader Format }}{{#if: |  »  }}{{#if: |  »  }}. This will open the {{#if: Import Loader Format|Import Loader Format|ImportLoaderFormat }} Window. Create a new record, give it a name and description of your choice then pick the import Table and the format of your data file. Move to the {{#if: |{{{3}}}|Import Format }} Tab.

Note.gif Note:

It is helpful to give the Import Loader Format a name similar to the import data file. A leading number in the name can help keep the order of imports straight. For example, the csv file is called "0 - Opening Trial Balance.csv" and the Import Loader Format name is set to "0 - Opening Trial Balance". This makes it easy to deal with lots in import files and their associated formats.

The Import Format Tab is where you connect the data you want to import to the columns in the import table. An example will help here.

Suppose you have a list of invoices to import. Your list is in a spreadsheet which contains all the necessary columns. The Search Key/Values used in the spreadsheet match those already in the ADempiere database for the Business Partners and Products. (If they don't, you will have to use an intermediate table to translate the key values. More on that later.) In this case, you can simply define a row in the Import Format tab for each column of data in the spreadsheet. Pay attention to the following:

  • The Start No field is important since it determines where to look for the record data. For a CSV file, it is the column number, with the first (left most) column being 1.
  • The Sequence field simply provides another way to order the records. It has no other purpose. It is helpful to use it to keep the rows in the Import Format tab in the same order as the columns in the spreadsheet. Use the Start No or multiply by ten. For example, if you have data in columns 1, 2, 4, 7, 8 and 9 that should be imported, set the sequence to 10, 20, 40, 70, 80, 90 respectively.
  • If the Data Type is Date, be sure to indicate the format and watch for differences between systems, such as dd/mm/yy vs. mm/dd/yy.
Note.gif Note:

As you create the Import Loader Format, it is helpful to create header rows in your spreadsheet that match the Start No and Name columns. However, when you save the spreadsheet to CSV, it is best to not save the header rows since these do not contain valid data. You can delete them before the save or add another sheet to the workbook that displays all the data from the first except for the header rows and then only save that sheet as a CSV file.

Preparing the data for Import

In an ideal world, you want to perform the data import to the intermediate table without errors and then process the intermediate table, also without errors. Errors within ADempiere take more effort to fix than applying the same corrections to a spreadsheet. So, if at all possible, prepare the data in a spreadsheet before attempting the import.

Note.gif Note:

If you have too much data to deal with or no control over the format, you may have to consider developing a customer import loader. See the section For Developers.

To properly prepare the data, it is important to understand the import process and what the Import File Loader and the Import processes are looking for.

Note.gif Note:

First, review the concepts of Database Tables and Common Fields.

Assuming your data has been read properly by the Import File Loader and is now sitting in the Import Table, when you press the button to perform the import, the import processes generally follow this sequence.

For each record in the import data table that has not previously been imported, the software assigns default values to the mandatory fields (AD_Org_ID, AD_Client_ID, Created, CreatedBy, IsActive, Updated, UpdatedBy) and sets two other fields: I_ErrorMsg = ' ' and I_IsImported = 'N'.

The software then tests the field values looking for errors. If one is found, a suitable error message is saved in the field I_ErrorMsg.

IDs and Value Fields

It is important to understand the differences between the Value/Search Key, Name and ID fields. The ID fields are usually hidden numerical keys that distinguish one record from another. Their value is controlled by the software and they are often not available for searches. The Value fields, on the other hand, are visible, often updateable and frequently used as the unique key for searches.

For example, a Business Partner in the database may have a Name "C&W Construction", a Value "C&W" and a C_BPartner_ID field = 117.

When you import a table directly, the ID field is assigned by the software and you can set the key value to what ever you want. It is pretty straight forward. However, if you want to link your import data to data that is already existing in the database, for example connecting an invoice to a business partner such as "C&W Construction" you have to import the Value field "C&W" or Name so the software can link that to the C_BPartner_ID field 117.

The same approach applies to many of the look-up or reference fields. The database may use hidden keys for these fields that need to be matched by name. To import the data, you have to know the name, not the key.

In some cases, if related records are not already in the database, the import process will try to create them. For example, if unknown business partners are included in import invoice data, the business partners will be added to the database.

Cross Linking Data

Where the import data uses different data formats than the database, you may need to perform a data translation using intermediate tables that relate the keys in the external data to the keys in the database.

Scripts and Advanced Processing

In some cases, the import data can be processed by scripts and rules to prepare it for processing. This is an advanced topic covered in the Developer Documentation section of the manual.

Fields and Defaults

For advice on which fields are mandatory or optional, see the individual Import window pages:

For Developers

The Import File Loader form software can be found in:

  • client/src
    • org.compiere.apps.form.VFileImport.java

The software that performs the import can be found in:

  • base/src
    • org.compiere.impexp.ImpFormat.java
    • org.compiere.impexp.ImpFormatRow.java
    • org.compiere.impexp.MImpFormat.java
    • org.compiere.impexp.MImpFormatRow.java

The software that process the import can be found in

  • base/src
    • org.compiere.process.Import.java where
      is the import table name.

      The following articles provide examples and tutorials:

      • You can refer to Red1.org to look at one such tutorial pack XML2AD that reveals how the ImportLoader is under the hood.
      • A simpler example will be ImportPrice Loader.