Data Import
⇐ 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
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.
- 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.