Difference between revisions of "Data Import"

Revision as of 04:26, 9 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.

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