More Excel Tools During Migration

What Trunk or Branches to use and mix? Which is the Official version? How to survive growth?
Forum rules
This forum is personally pruned to avoid redundant posts. Related topics are moved to the same sub-forum. Language will be corrected for readability.

More Excel Tools During Migration

Postby red1 » Fri Mar 15, 2019 11:31 pm

I made a new plugin 'Data Map' that helps in preparing Excel sheets during migration. For example in ProductMigration.xls, the Raw Sheet needs to map to the Master Sheet, and the user needs to copy and paste and move columns around. To deal with thousands of records this can be slow and so i made a SynchMaster process to give visibility to the mapping progress and the magic Java code will synch it for you within seconds. The trick is to insert a top row and reference to the same cell in Master sheet.

RawSheet.png (131.55 KiB) Viewed 255 times
Note the highlighted cell. They are not typed in, but by placing '=' and then go to the column header that you wish the content to be matched to.

SynchMaster.png (15.37 KiB) Viewed 256 times
After running this you will see the results magically done at the Master sheet:

MasterSheet.png (94.69 KiB) Viewed 255 times
Note again in the highlighted cell, I programmatically copied over a reference address. Hope you get the idea.

But before doing that, notice that i could split the Name column to populate the Value column, which was blank before. That is done by using this:

Tanspose.png (15.44 KiB) Viewed 256 times
The SplitSymbol was a space.

The plugin is at

DataMapMenu.png (12.09 KiB) Viewed 251 times
Binary with 2Pack in ... Migration/
Site Admin
Posts: 2753
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: More Excel Tools During Migration

Postby red1 » Sat Mar 16, 2019 1:31 pm

As usual, i am on a roll. Remember that we can Migrate Excel Data directly into iDempiere, we still need to prepare the sheet and header cell names. So now we can just dump each table for us onto our spreadsheet.

MakeMigrateSheet.png (16.91 KiB) Viewed 249 times
The result is as follows:

TableSheet.png (23.11 KiB) Viewed 249 times
Now you can use this as part of the mass legacy data set to be imported in at one go. In future i will weave the data content to automatically reference the Master sheet.
Site Admin
Posts: 2753
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Return to Building ADempiere

Who is online

Users browsing this forum: No registered users and 3 guests