Open Source ERP Academy

High-Volume Record Count Upload/Import

The purpose of this page is to discuss how to efficiently import high volumes of data into iDempiere. I was working with a customer that had hundreds of thousands of CRM Leads to import into iDempiere. The question came up "what is the best way to get it into iDempiere?" There are times when you are better served getting raw (csv) data into a newly created table in PostgreSQL. Once you get the data into the database, you have more options in how you migrate the data to its final destination. Here is the summary of how the CRM Lead data played out:
  1. Get the csv data into a newly created table in iDempiere - using PostgreSQL copy command
  2. Migrate the data into the Lead window (AD_User table) - using 'insert into .. select .. from temp_table'
  3. Use the data as is needed
Granted, I could have imported the data directly into the lead window (AD_User); however, manipulating millions of records in Excel to match the needed format is painful. Instead, I opted to import the data into a temporary table where I could use SQL to manipulate the data very quickly. Also, the thought of importing millions of records into AD_User is not appealing. I would rather import all records into a separate table then migrate them as needed into the Lead window. Most csv files come with a header record. I used the header record to create my temporary table. Be generous with the size of your columns. There is no good reason to go too small. Here is an example: 
create table test_01 (
bus_id character varying(255), 
business character varying(255),
address character varying(255),
city character varying(255),
state character varying(60),
zip character varying(60),
plus4 character varying(60),
dpb character varying(60),
crrt character varying(60),
contact character varying(255),
firstname character varying(255),
lastname character varying(255),
title character varying(255),
position character varying(255),
gender character varying(60),
phone character varying(60),
fax character varying(60),
sic character varying(60),
sicdesc character varying(255),
sic2 character varying(60),
sicdesc2 character varying(255),
sic3 character varying(60),
sicdesc3 character varying(255),
sic4 character varying(60),
sicdesc4 character varying(255),
empsizecode character varying(60),
empsize character varying(60),
SLSVOLCODE character varying(60),
SLSVOLFULL character varying(60),
EstimatedSalesVolume character varying(60),
CorpSlsVol character varying(60),
TotalSalesVolumeActual character varying(60),
FIRM_OR_INDIV character varying(60),
BUS_STATUS character varying(60),
COTTAGE_BUS character varying(60),
WOMANOWNED character varying(60),
SMALL_BUS character varying(60)
);
Remove your header record from your csv and copy it to your server's /tmp/ directory. I used filezilla to copy my file. In your favorite SQL editor, like phppgadmin, issue the following command: 
copy test_01 from '/tmp/YourFileName.csv' DELIMITERS ',' CSV;
This will upload your csv data to your newly created table. Be aware that the PostgreSQL copy command is very particular. Every column must be represented in the csv file (no more no less). In my case, the copy command uploaded about 100K records per second - maybe faster. Migrate your records using a query like this: 
insert into ad_user 
(ad_user_id, name, 
ad_client_id, ad_org_id,
created, createdby, 
updated, updatedby,
Issaleslead, description,
Bpname, value,
Phone, fax,
Leadsourcedescription,
Chuboe_CRMEmployeeSize,
Chuboe_CRMZipCode,
ChuBoe_CRMSalesVolume,
comments

)
select 
nextidfunc(25,'N') as ad_user_id, coalesce(contact, business) as name,
11 as ad_client_id ,0 as ad_org_id,
now() as created, 0 as createdby,
now() as updated, 0 as updatedby,
'Y' as issaleslead, 'imported' as description,
Business as bpname, bus_id as value,
Phone, fax,
'Imported' as leadsourcedescription,
EMPSIZECODE||'_'||empsize as Chuboe_CRMEmployeeSize,
Zip as Chuboe_CRMZipCode,
Case when Slsvolcode is not null and slsvolfull is not null then Slsvolcode||'_'||slsvolfull else '' end || case when corpslsvol is not null and totalsalesvolumeactual is not null then corpslsvol||'_'||totalsalesvolumeactual else '' end as ChuBoe_CRMSalesVolume,
Case when estimatedsalesvolume is not null then 'Est Sales: '||estimatedsalesvolume||chr(13)||chr(10) else '' end ||
Case when totalsalesvolumeactual is not null then 'Est Sales: '||totalsalesvolumeactual ||chr(13)||chr(10) else '' end ||
Case when address is not null then 'Address: '||address||chr(13)||chr(10) else '' end ||
Case when city is not null then 'City: '||city||chr(13)||chr(10) else '' end ||
Case when state is not null then 'State: '||state||chr(13)||chr(10) else '' end ||
Case when crrt is not null then 'CRRT: '||crrt||chr(13)||chr(10) else '' end ||
Case when dpb is not null then 'DPB: '||dpb||chr(13)||chr(10) else '' end ||
Case when title is not null then 'Title: '||title||chr(13)||chr(10) else '' end ||
Case when position is not null then 'Position: '||position||chr(13)||chr(10) else '' end ||
Case when gender is not null then 'Gender: '||gender||chr(13)||chr(10) else '' end ||
Case when firm_or_indiv is not null then 'Firm/Ind: '||firm_or_indiv||chr(13)||chr(10) else '' end ||
Case when bus_status is not null then 'BusStatus: '||bus_status||chr(13)||chr(10) else '' end ||
Case when cottage_bus is not null then 'CottageBus: '||cottage_bus||chr(13)||chr(10) else '' end ||
Case when womanowned is not null then 'Woman Owned: '||womanowned||chr(13)||chr(10) else '' end ||
Case when small_bus is not null then 'Small Business: '||small_bus||chr(13)||chr(10) else '' end ||
Case when sic is not null then 'SIC: ' ||sic||chr(13)||chr(10) else '' end ||
Case when sicdesc is not null then 'SIC_Desc: ' ||sicdesc||chr(13)||chr(10) else '' end ||
Case when sic2 is not null then 'SIC2: ' ||sic2||chr(13)||chr(10) else '' end ||
Case when sicdesc2 is not null then 'SIC2_Desc: ' ||sicdesc2||chr(13)||chr(10) else '' end ||
Case when sic3 is not null then 'SIC3: ' ||sic3||chr(13)||chr(10) else '' end ||
Case when sicdesc3 is not null then 'SIC3_Desc: ' ||sicdesc3||chr(13)||chr(10) else '' end ||
Case when sic4 is not null then 'SIC4: ' ||sic4||chr(13)||chr(10) else '' end ||
Case when sicdesc4 is not null then 'SIC4_Desc: ' ||sicdesc4||chr(13)||chr(10) else '' end
From test_01 
;
Here are a couple of notes about inserting data into the AD_User table from your newly created table:
  1. You can use the nextidfunc() db function to grab the next AD_User_ID from iDempiere. The first parameter is the ad_sequence_id that should be used. The second parameter tells where to use a 'System' record or your client's record. System records should be used for IDs. Client records should be used for Search Keys.
  2. I executed the System Admin role=> Enable Native Sequence process to move systemID creation out of iDempiere and into a DB sequence. This makes determining the next AD_User_ID much much faster (5x to 10x). Be sure to test this before executing in production.
  3. In the above table, there are a number of field that do not exist in the AD_User table. Rather than create these fields, you can concatenate many of them in the AD_User's comments field using a new line \n character to put field each on a new line. Here is an example of what the data would look like:
    1. SIC: 98089 SIC_Desc: Really Cool Company SIC2: 90879 SIC_Desc: Motorcycle Manufacturer WomenOwned: Yes Etc....