Open Source ERP Academy

Record Import (upload)

The purpose of this lesson is to help you get information into ADempiere and iDempiere.

2015-10-22 - AM Open Discussion Record Import Overview

This quick video discusses the differences between the older style of import (Import Business Partner via I_BPartner and Import File Loader) and the newer style of import (CSV import via toolbar icon).

Import Records General Process - Demonstrated with Product Import

iDempiere Import CSV File Loader Feature

http://wiki.idempiere.org/en/NF1.0_ImportCSV iDempiere has a new Import File Loader feature located on every window. It is located in the top-right icon in the standard window's toolbar. iDempiere new Import File Loader Here are examples you can use to help you understand the file format:

Import CSV Notes

Import CSV Process

There is a Import CSV Process menu option that provides a couple of benefits over the icon demonstrated above:

Creating Your Own CSV

Steps to create your own example CSV for a new window:
  1. Open the window you wish to import records for.
  2. If you wish to include a specific sub-tab in the export results, click on that sub-tab to make sure it has focus.
  3. Click on the Export Toolbar button and choose CSV. In version 3.0, you can choose multiple sub-tabs to include in your export.
  4. Open the exported file and observe the results.
  5. Please note the export may not include all columns. If you need to import a column that was not exported, simply right-click on the field itself and choose Change Log. In the top-left corner of the Change Log dialog, you will find the name of the column.
This is a really exciting feature for the following reasons:

2014-09-26 - iDempiere New Import and Export

Import New Records into Existing Record's SubTab

Inserting records into an existing record's subtab can be tricky. Instead of using the 'insert' option, you must use the 'merge' option. When using the 'merge' option, you must specify which columns are the key columns (update existing record vs creating a new record). Here is an example where you merge a Product window => Purchasing subtab record. You must make the Product Tab => Value (search key) a '/K' key column to make sure you find the right product. You must also include the columns in the M_Product_PO table are keys as well. 
Value/K,M_Product_PO>M_Product_ID[Value]/K,M_Product_PO>C_BPartner_ID[Value]/K,M_Product_PO>VendorProductNo/K,M_Product_PO>PricePO
"223 058 013-01","223 058 013-01","PL8598956771RFES","223 058 013-01",22
Here is an example header used to insert lines into an existing invoice: 
DocumentNo/K,C_InvoiceLine>C_Invoice_ID[DocumentNo]/K,C_InvoiceLine>C_InvoiceLine_UU/K,C_InvoiceLine>Chuboe_Package_ID[Value]/K,C_InvoiceLine>C_BPartner_Location_ID,C_InvoiceLine>QtyEntered

Here is the sql used to generate this import from a temporary table:

\copy (
    select
    i.documentno "DocumentNo/K",
    i.documentno "C_InvoiceLine>C_Invoice_ID[DocumentNo]/K",
    generate_uuid() "C_InvoiceLine>C_InvoiceLine_UU/K",
    mis.package as "C_InvoiceLine>Chuboe_Package_ID[Value]/K",
    (select min(x.c_bpartner_location_id) from c_invoiceline x where x.c_invoice_id = i.
    c_invoice_id) as "C_InvoiceLine>C_BPartner_Location_ID",
    mis.qty as "C_InvoiceLine>QtyEntered"
from delme_subscription_import_promo_20210702_2 mis
join c_invoice i on mis.c_bpartner_id = i.c_bpartner_id
where i.docstatus in ('DR','IP')
order by i.documentno
)
to
'~/sql/csv/sub_change_20210702.out'
CSV HEADER
;

Convert json to csv and visa versa

I just used a command line (cli) tool named 'recast' to convert a sizeable csv to json. Here are the details:

Update and Merge Records with iDempiere's New CSV Import Tool

In addition to inserting new records, you can also Update existing records and Merge new records in a window. To accomplish these tasks, you must specify one or more columns as key columns. You do this by adding a "/K" to the end of the column name. Here is a practical example:

2014-10-30 - Import Sub-Tab Records into a Record that Already Exists

Import Records Manually via SQL - Demonstrated with BOM Import

Import Website Leads from MySQL Wordpress

The purpose of this section is to demonstrate how you can use the command line to automatically migrate data from one database (mysql) to another (psql) using command line tools. Let's assume that leads are held in a chuboe_lead table in iDempiere. Let's also assume we will create a special import table (i_chuboe_lead) to handle the import from MySQL (your website). 
create table i_chuboe_lead (lead numeric, date timestamp without time zone, name character varying(255), phone character varying(255), email character varying(255), address character varying(255), address2 character varying(255), citystatezip character varying(255), country character varying(255), department character varying(255), note character varying(2000));
The below script performs the following:
  1. Copies over data using the MySQL command line tool
  2. Pipes the results of the MySQL command into Postgresql psql command
  3. Ignores duplicates and copies the new entries into chuboe_lead
psql -h your.psql.host -U adempiere -d idempiere -c 'delete from i_chuboe_lead'


mysql -u web.site.db.username -h your.website wordpress -e \
"select
concat(
'insert into i_chuboe_lead values (',
lead,
',',
'''',
date,
'''',
',',
'''',
coalesce(name,''),
'''',
',',
'''',
coalesce(phone,''),
'''',
',',
'''',
coalesce(email,''),
'''',
',',
'''',
coalesce(address,''),
'''',
',',
'''',
coalesce(address2,''),
'''',
',',
'''',
coalesce(citystatezip,''),
'''',
',',
'''',
coalesce(country,''),
'''',
',',
'''',
coalesce(department,''),
'''',
',',
'''',
coalesce(replace(note,'''',''),''),
'''',
');'
) as insert_statement
from website_view_with_lead_data
where date > date_sub(now(),interval 2 day)  --pull last two days
and (phone is not null or email is not null or address is not null)
and department = 'Sales'
order by lead desc
" | psql -h your.psql.host -U adempiere -d idempiere


psql -h your.psql.host -U adempiere -d idempiere -c \
"insert into chuboe_Lead (ad_client_id, ad_org_id, name, email, phone, C_SalesStage_ID, chuboe_LeadSource_ID,description, address1, address2, CityStateZip, created, createdby, updated, updatedby, chuboe_Lead_id, chuboe_lead_ws) select 1000000, 0, name, email, phone, 1000027, 1000002, note, address, address2, citystatezip, now(), 100, now(), 100, nextidfunc(1000935, 'N'), lead from i_chuboe_lead where lead not in (select coalesce(chuboe_lead_ws,99999999) from chuboe_lead)"


psql -h your.psql.host -U adempiere -d idempiere -c 'delete from i_chuboe_lead'

2014-04-17 AM - Import Records - Business Partner

Import Business Partner Example Data Import Business Partner Example Data Import File Loader Includes the following: See Go-Live Inventory and Accounting Import (establish opening balances)

System IDs

There are times when you need upload records directly into the database via SQL; however, you need iDempiere to generate the next record primary key value (sequence ID). The way to do this is via the iDempiere nextidfunc(AD_Sequence_ID, isSystem) database function. You can find the AD_Sequence_ID in the Document Sequence window by searching on the table's primary key column (example: C_Order_ID). Here is an example of an insert statement using nextidfunc(): 
insert into some_table (ad_client_id, ad_org_id, created, createdby, updated, updatedby, c_project_id, xx_circuit_id, some_table_id)
select ad_client_id, 0, created, createdby, updated, updatedby, c_project_id, xx_circuit_id, nextidfunc(1000297, 'N') from some_other_table;