Migrating Legacy Data

The migration of Adempiere modules to iDempiere

Migrating Legacy Data

Postby red1 » Sun Mar 19, 2017 8:53 am

[BINARY]: https://sourceforge.net/projects/red1/files/Utils/ (red1.ninja and CSVs)
[SOURCE]: http://bitbucket.org/red1/org.red1.ninja

This has always been a big pain and mess to do. Until now. I have created within the Red1 Ninja plugin a bulk importer of CSVs zipped into a single file. No further definition needed other than what is in the CSVs. You can watch how it has progress from Unicenta POS steps of quickly importing in restaurant master information.

And just this morning here in Rangsit, Bangkok of Thailand, I improved the importer to handle master/detail documents rather instantly.

InvoiceMasterDetailCSVs.png
InvoiceMasterDetailCSVs.png (101.65 KiB) Viewed 113 times
You can see for yourself in this movie:

red1
Site Admin
 
Posts: 2634
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Migrating Legacy Data

Postby red1 » Tue Mar 21, 2017 6:47 am

2Pack and the good old migration scripts have been the only good tools to use until now. I prefer my new way which i share to others. It is visible as a normalised Excel csv file. It moves directly into its named table in DB via UPDATE or INSERT. As it has no other validation besides inherent DB constraints, it is light and fast. The issue of validation is minimally solved by trial and error until you get a working CSV. For example the following is to import in ProductPrice to make a test case for Libero to work. (*See bottom of post on how to use this.)

ProductPriceCSV.png
ProductPriceCSV.png (35.76 KiB) Viewed 79 times
Note that this is simplistic, comparing to the more powerful http://wiki.idempiere.org/en/NF1.0_ImportCSV. Mine is providing ready made templates for instant use. Such templates which are a work in progress (ProductMaster and Invoice/Line done) can have VBA scripts to validate before exporting and zipping the CSVs for one step import. (Nakarat here in Bangkok is an expert with VBA apps and will assist in that.)

Been a single zip it can be ported similar to 2Pack.zip. Simialrly there is no attachment via corresponding window tab.

*To use this CSV go to http://sourceforge.net/projects/red1/fi ... facturing/ and take M_ProductPrice4Libero.csv. Since it is a single file you attach this without zipping but put in 'M_ProductPrice' as its name in the ModelMaker tab.
(When used as zipped rename the file to 1_M_ProductPrice.csv or in order according to other CSVs within the same zip. No need to put any value in name of ModelMaker. The movies makes this very clear.)
red1
Site Admin
 
Posts: 2634
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Migrating Legacy Data

Postby red1 » Wed Mar 22, 2017 9:36 pm

Here is probably the heaviest test of the CSV import which is for US Zip Codes. I scrubbed the original http://federalgovernmentzipcodes.us/fre ... tabase.csv and broke it up into two files here https://sourceforge.net/projects/red1/files/Business/ (FreeZipCodeUS-1.csv and FreeZipCodeUS-2.csv) because my Excel handler (Mac Numbers) cannot manage more than 65k rows.

You have to use this Column Set and it will create the metadata to become a full menu window of the data. (You can switch the Q# to normal string by simply removing it.) -
Code: Select all
Q#RecordNumber,Zipcode,ZipCodeType,T#City,T#State,T#LocationType,Lat,Long,Xaxis,Yaxis,Zaxis,T#WorldRegion,T#Country,T#LocationText,T#Location,T#Decommisioned,Y#TaxReturnsFiled,T#EstimatedPopulation,T#TotalWages,T#Notes

If you wish to update changes, you have to reattach the changed CSV, and put some note in the Help/Comment column. It is a hack to trigger the update. An empty Help/Comment will bypass UDPATE to save some time in such a large file.

Below is the first migrated CSV result.

CSVResult.png
CSVResult.png (80.49 KiB) Viewed 70 times
MigratedZipCodes.png
MigratedZipCodes.png (168.22 KiB) Viewed 70 times

I made a movie to provide a better guide



I made some changes to the Ninja plugin to be more faster and give more descriptive error logging. Take it here http://sourceforge.net/projects/red1/files/Utils/
red1
Site Admin
 
Posts: 2634
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia


Return to iDempiere

Who is online

Users browsing this forum: No registered users and 1 guest

cron