ERP 2.50:User Manual/Import Data
Contents |
Introduction
The following items can be imported into Openbravo ERP:
- Business Partners
- Products
- Accounts
- Orders
- Budgets
- Taxes
Importing data is performed in three steps.
Step 1: Define the CSV format
The imported data needs to be in a comma separated file (.csv). The format of the CSV file needs to be defined first. This is done by naming the columns of the CSV file. Openbravo has several, predefined formats you can use.
Make sure you have administrative rights (ClientAdmin).
Select Master Data Management > Import Data > Import Loader Format. Select one of the predefined import formats or create a new one. In case a new format is created, a table needs to be selected to which the data is written. This table (I_xxx) is a temporary table, see step 2. After supplying a name for the new import format, the columns can be specified.
Some of the column names refer to an ID, e.g. C_BPartner_ID. This is the internal number used by Openbravo to refer to a business partner. The only way of determining this ID is to look into the database directly, e.g. using pgAdmin in case of a ProgreSQL database.
Select the tab Field Format. Each column in the CSV file needs to be defined. The column is represented by a row in the field format. Notice that the column names are of no importance, only the sequence.
Each import requires mandatory values:
- For Business Partners: Search key (Value) and Name
- For Products: Search key, Name, Product type, Business Partner and Product Category
Step 2: Import the CSV file to a temporary table
After the import format is defined select Master Data Management > Import Data > Import File Loader. This step involves loading the CSV file into a temporary table. This table format is specified in step 1, e.g. I_BPartner.
Fill in the CSV file to import and the import format (Format file) you just created. If the CSV file contains column header names, select "Header first line". To test the import, click Ok. If everything is correct you should see (part) of the CSV file contents in the bottom part of the windows. If an error occurs, look in the tomcat log file to find a indication of the error.
Common import errors are:
- Not all values are between quotes.
- The number of columns is not equal to the ones specified in the import format, step 1.
- The are empty lines at the bottom of the CSV file.
- Not all mandatory values are supplied in the CSV file.
- The text length in the CSV file exceed the related database field size.
To finish the import, select the CSV file and the Format file again and click process. The content of the CSV file is now written to the temporary table.
Step 3: Finish the import
Select Master Data Management > Import Data > Import xxx (e.g. Import Business Partner). Each line of the CSV file is written to a different record and the first record is shown in the window. You will see the imported values are filled in and additional information can be supplied. After you finish checking and adding all imported record, click Import xxx (e.g. Import Business Partner). The contents of the temporary table is now written to the final table in the database and the import process is finished.
Import Loader Format
Define the file layout for product information which will be imported.
Field Format
Define individual fields and the required format of the import file.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Callout: | A series of actions that occur when data is modified. | |
| Client: | Client for this installation. | |
| Column: | A link to the database column of the table. | |
| Constant Value: | Constant value | |
| Data Format: | Format String in Java Notation, e.g. ddMMyy | |
| Data Type: | Type of data | |
| Decimal Point: | Decimal Point in the data file | |
| Divide by 100: | Divide number by 100 to get correct amount | |
| End No: | ||
| Format Field: | ||
| Import Format: | ||
| Name: | A identifier for a document which can be used as a search tool. | |
| Organization: | Organizational entity within client | |
| Script: | A code used to create or modify the database or the database data. | |
| Sequence: | The order of records in a specified document. | |
| Start No.: | The first number that will be used in a standard or control sequence. |
Import Format
Create and edit import formats for specific database tables.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Client: | Client for this installation. | |
| Copy Lines: | Copy Lines from other Import Format | |
| Description: | A space to write additional related information. | |
| Format: | Format of the data | |
| Import Format: | ||
| Name: | A identifier for a document which can be used as a search tool. | |
| Organization: | Organizational entity within client | |
| Table: | A dictionary table used for this tab that points to the database table. |
Import File Loader
Import .csv data files into temporary tables according to the parameters and selected format.
Import Products
Import products in .csv format from previously used databases/applications and past business operations.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Business Partner : | Anyone who takes part in daily business operations by acting as a customer, employee, etc. | |
| Business Partner Search Key: | An abbreviated name to help find a business partner. | |
| Classification: | Classification for grouping | |
| Client: | Client for this installation. | |
| Comments: | A space to write additional related information. | |
| Currency: | An accepted medium of monetary exchange that may vary across countries. | |
| Description: | A space to write additional related information. | |
| Description URL: | An address for the product description which can be accessed via internet. | |
| Discontinued: | A statement mentioning that this product will no longer be available on the market. | |
| Discontinued by: | The name of the person who discontinues an item. | |
| EDI Code: | UOM EDI Code | |
| Fixed Cost per Order: | The additional added fixed cost for a purchased product. | |
| Help/Comment: | A comment that adds additional information to help users work with fields. | |
| Image URL: | An address for the product image which can be accessed via internet. | |
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| Import Product: | Import Item or Service | |
| Import Products : | Imports products from a file into the application | |
| ISO Code: | A coding standard for countries. | |
| List Price: | The official price of a product in a specified currency. | |
| Manufacturer: | The business partner that makes a specific product. | |
| Minimum Order Qty.: | The minimum number of an item that must be purchased at one time from a vendor. | |
| Name: | A identifier for a document which can be used as a search tool. | |
| Organization: | Organizational entity within client | |
| Price Applicable from: | The time from which a price is valid. | |
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Product: | An item produced by a process. | |
| Product Category: | A classification of items based on similar characteristics or attributes. | |
| Product Category Key: | ||
| Product Type: | An important classification used to determine the accounting and management of a product. | |
| Purchase Order Price: | The price charged to purchase a specific item. | |
| Quantity per Package: | The number of a specific item that comes in one package. | |
| Royalty Amount: | (Included) Amount for copyright, etc. | |
| Scheduled Delivery Time: | The date that a task, process, or action is to be completed or delivered by. | |
| Search Key: | A fast method for finding a particular record. | |
| Shelf Depth: | Shelf depth required | |
| Shelf Height: | Shelf height required | |
| Shelf Width: | Shelf width required | |
| SKU: | A "stock keeping unit" used to track items sold to business partners. | |
| Units Per Pallet: | Units Per Pallet | |
| UOM: | A non monetary unit of measure. | |
| UPC/EAN: | A bar code with a number to identify a product. | |
| Vendor Category: | A classification of vendors based on similar characteristics or attributes. | |
| Vendor Product No.: | The identifier used by a vendor to indentify a product being purchased by their business partners. | |
| Volume: | Volume of a product | |
| Weight: | Weight of a product |
Import Business Partner
Import business partners in .csv format from previously used databases/applications and past business operations.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Address Line 1: | A space to write the location of a business partner. | |
| Address Line 2: | A space to write the location of a business partner. | |
| Alternative Phone No.: | A second contact telephone number for a business partner. | |
| Birthday: | An anniversary of birth for a business partner. | |
| BP Contact Greeting: | Greeting for Business Partner Contact | |
| Business Partner : | Anyone who takes part in daily business operations by acting as a customer, employee, etc. | |
| Business Partner Category: | A classification of business partners based on defined similarities. | |
| City: | A populated defined area located within a larger area such as a state, province, or country. | |
| Client: | Client for this installation. | |
| Comments: | A space to write additional related information. | |
| Contact Description: | Description of Contact | |
| Contact Name: | The name of the person to get in touch with. | |
| Country: | A state or a nation. | |
| Description: | A space to write additional related information. | |
| D-U-N-S: | Dun & Bradstreet Number | |
| Email: | An email address for a specified business partner. | |
| Fax: | A fax number for a specified business partner. | |
| Group Key: | Business Partner Group Key | |
| Import Business Partner: | ||
| Import Business Partners: | Import Business Partners | |
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| ISO Country Code: | The geographic country code for a country based on the ISO standard. | |
| NAICS/SIC: | Standard Industry Code or its successor NAIC - http://www.osha.gov/oshstats/sicser.html | |
| Name: | A identifier for a document which can be used as a search tool. | |
| Name 2: | Additional space to write the name of a business partner. | |
| Organization: | Organizational entity within client | |
| Partner Address: | The location of the selected business partner. | |
| Password: | A secret code used to allow access to a specified window or tab. | |
| Phone: | A telephone number for a specified business partner. | |
| Position: | A defined job, title, or ranking within a company. | |
| Postal Code: | A identification code used to help get items to a specific location. | |
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Region: | An area of a specific country. | |
| Region Name: | The name of an area in a specific country. | |
| Search Key: | A fast method for finding a particular record. | |
| Tax ID: | The government defined unique number for assigning and paying taxes. | |
| Title: | A description, often abbreviated, of how to address a business partner. | |
| User/Contact: | An acquaintance to reach for information related to the business partner. |
Import Account
Import accounts in .csv format from previously used databases/applications and past business operations.
Fields:
| Account Element: | A identification code for an account type. | |
| Account Sign: | Indicates the Natural Sign of the Account as a Debit or Credit | |
| Account Type: | Indicates the type of account | |
| Accounting Element: | A unique identifier for an account type. | |
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Client: | Client for this installation. | |
| Column: | A link to the database column of the table. | |
| Default Account: | Name of the Default Account Column | |
| Description: | A space to write additional related information. | |
| Document Controlled: | Control account - If an account is controlled by a document, you cannot post manually to it | |
| Element Name: | Name of the Element | |
| Import Account: | Import Account Value | |
| Import Accounts: | Import Natural Accounts | |
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| Name: | A identifier for a document which can be used as a search tool. | |
| Organization: | Organizational entity within client | |
| Parent Account: | The parent (summary) account | |
| Parent Key: | Key if the Parent | |
| Post Actual: | Actual Values can be posted | |
| Post Budget: | Budget values can be posted | |
| Post Encumbrance: | Post commitments to this account | |
| Post Statistical: | Post statistical quantities to this account? | |
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Search Key: | A fast method for finding a particular record. | |
| Summary Level: | A means of grouping fields in order to view or hide additional information. |
Import Orders
Import orders in .csv format from previously used databases/applications and past business operations.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Activity: | A distinct activity defined and used in activity based management. | |
| Address Line 1: | A space to write the location of a business partner. | |
| Address Line 2: | A space to write the location of a business partner. | |
| Bill to UPC: | ||
| Business Partner : | Anyone who takes part in daily business operations by acting as a customer, employee, etc. | |
| Business Partner Search Key: | An abbreviated name to help find a business partner. | |
| Business Partner UPC: | ||
| City: | A populated defined area located within a larger area such as a state, province, or country. | |
| Client: | Client for this installation. | |
| Contact Name: | The name of the person to get in touch with. | |
| Country: | A state or a nation. | |
| Currency: | An accepted medium of monetary exchange that may vary across countries. | |
| Description: | A space to write additional related information. | |
| Document No.: | An often automatically generated identifier for all documents. | |
| Document Type: | A value defining what sequence and process setup are used to handle this document. | |
| Document Type Name: | A value defining what sequence and process setup are used to handle this document. | |
| Email: | An email address for a specified business partner. | |
| Freight Amount: | The charge amount for a specified shipment. | |
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Order: | Import Orders | |
| Import Orders: | Import Orders | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| Invoice to Address: | The location where the invoice payment request will be sent. | |
| ISO Country Code: | The geographic country code for a country based on the ISO standard. | |
| Line Description: | An additional detail regarding one item or charge. | |
| Location / Address: | A specific place or residence. | |
| Name: | A identifier for a document which can be used as a search tool. | |
| Order Date: | The time listed on the order. | |
| Order Reference No.: | ||
| Organization: | Organizational entity within client | |
| Partner Address: | The location of the selected business partner. | |
| Payment Term Key: | Key of the Payment Term | |
| Payment Terms: | The setup and timing defined to complete a specified payment. | |
| Phone: | A telephone number for a specified business partner. | |
| Postal Code: | A identification code used to help get items to a specific location. | |
| Price List: | A catalog of selected items with prices defined generally or for a specific partner. | |
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Product: | An item produced by a process. | |
| Product Search Key: | A fast method for finding an item. | |
| Project: | A defined task or undertaking | |
| Quantity: | The number of an item involved in a transaction, given in standard units. It is used to determine price per unit. | |
| Region: | An area of a specific country. | |
| Region Name: | The name of an area in a specific country. | |
| Sales Campaign: | An advertising effort aimed at increasing sales. | |
| Sales Order: | A unique and often automatically generated identifier for a sales order. | |
| Sales Order Line: | A unique and often automatically generated identifier for a sales order line. | |
| Sales Representative: | The person in charge of carry out an order. | |
| Sales Transaction: | An indication that a transfer of goods and money between business partners is occurring. | |
| Scheduled Delivery Date: | The date that a task, process, or action is to be completed or delivered by. | |
| Shipping Company: | The name of the company making the shipment. | |
| SKU: | A "stock keeping unit" used to track items sold to business partners. | |
| Tax: | The percentage of money requested by the government for this specified product or transaction. | |
| Tax Amount: | The total sum of money requested by the government of the specified transaction. | |
| Tax Search Key: | A fast method for finding a a specific tax. | |
| Trx Organization: | The organization which performs or initiates the transaction. | |
| Unit Price: | The price that will be paid for a specified item. | |
| UOM: | A non monetary unit of measure. | |
| UPC Address: | ||
| UPC/EAN: | A bar code with a number to identify a product. | |
| User/Contact: | An acquaintance to reach for information related to the business partner. | |
| Vendor UPC: | ||
| Warehouse / Service Point: | The location where products arrive to or are sent from. |
Import Budget
Import budgets in .csv format from previously used databases/applications and past business operations.
Fields:
| Accounting Combination: | An identification code comprised of an individual account number and additional dimensions such organization, product, and business partner. | |
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| Activity: | A distinct activity defined and used in activity based management. | |
| Activity ident.: | ||
| Amount: | A monetary total. | |
| Bp group ident: | ||
| Budget: | A group of planned expenses for a task. | |
| Budget line: | ||
| Business Partner : | Anyone who takes part in daily business operations by acting as a customer, employee, etc. | |
| Business Partner Category: | A classification of business partners based on defined similarities. | |
| Campaign ident: | ||
| Client: | Client for this installation. | |
| Combination ident: | ||
| Currency: | An accepted medium of monetary exchange that may vary across countries. | |
| Currency ident: | ||
| Description: | A space to write additional related information. | |
| I_Budgetline_ID: | ||
| Import Budget: | ||
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| Org Trx ident: | ||
| Organization: | Organizational entity within client | |
| Partner ident: | ||
| Period: | A specified time period. | |
| Period ident: | ||
| Price: | The cost or value of a good or service. | |
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Prod category ident: | ||
| Product: | An item produced by a process. | |
| Product Category: | A classification of items based on similar characteristics or attributes. | |
| Product ident: | ||
| Project: | A defined task or undertaking | |
| Project ident: | ||
| Quantity: | The number of a certain item. | |
| Sales Campaign: | An advertising effort aimed at increasing sales. | |
| Sales Region: | A defined section of the world where sales efforts will be focused. | |
| Sales region ident: | ||
| Sequence: | The order of records in a specified document. | |
| Trx Organization: | The organization which performs or initiates the transaction. | |
| UOM: | A non monetary unit of measure. |
Import Taxes
Import taxes in .csv format from previously used databases/applications and past business operations.
Fields:
| Active: | A flag indicating whether this record is available for use or de-activated. | |
| BP Tax Cat Desc.: | ||
| BP Tax Cat Name: | ||
| BP Tax Category: | Bussines Partner Tax Category as a Vendor | |
| Cascade: | Any additional discount built upon the remaining total after applying previous discounts. | |
| Client: | Client for this installation. | |
| Code Country From: | ||
| Code Country To: | ||
| Code Region From: | ||
| Code Region To: | ||
| Commodity Code: | Commodity code used for tax calculation | |
| Country: | A state or a nation. | |
| Destination Country: | The country receiving a shipment | |
| Destination State/Province: | The state/province inside of a country receiving the shipment. | |
| Import Error Message: | A message that appears when errors occur during the importing process. | |
| Import Process Complete: | A indication that the desired process has been completed successfully. | |
| Import Taxes: | Import taxes in .csv format from previously used data bases/applications and past business operations. | |
| I_Tax_ID: | ||
| Line No.: | A line stating the position of this request in the document. | |
| Organization: | Organizational entity within client | |
| Parent Tax: | Parent Tax indicates a tax that is made up of multiple taxes | |
| Parent Tax Name: | ||
| Processed: | A confirmation that the associated documents or requests are processed. | |
| Rate: | The percentage to be multiplied by the source to arrive at the tax or exchange amount. | |
| Region: | An area of a specific country. | |
| SO/PO Type: | Sales Tax applies to sales situations, Purchase Tax to purchase situations | |
| Summary Level: | A means of grouping fields in order to view or hide additional information. | |
| Tax: | The percentage of money requested by the government for this specified product or transaction. | |
| Tax Cat. Description: | ||
| Tax Category: | A classification of tax options based on similar characteristics or attributes. | |
| Tax Category Name: | ||
| Tax Description: | ||
| Tax Exempt: | A condition stating that for a specific case, taxes must not be applied. | |
| Tax Name: | ||
| Tax Search Key: | A fast method for finding a a specific tax. | |
| Valid from Date: | A parameter stating the starting time of a specified request. |