Difference between revisions of "Data Import"

Revision as of 20:59, 8 August 2010

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 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: In the database, these tables are given the prefix I_ to indicate the import table. If the target table was C_BPartner, for example, the import table would be I_BPartner. The relation 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.

The import formats supported 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.

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.
• 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.