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 510 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 511 times
After running this you will see the results magically done at the Master sheet:

MasterSheet.png (94.69 KiB) Viewed 510 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 511 times
The SplitSymbol was a space.

The plugin is at

DataMapMenu.png (12.09 KiB) Viewed 506 times
Binary with 2Pack in ... Migration/
Site Admin
Posts: 2759
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 504 times
The result is as follows:

TableSheet.png (23.11 KiB) Viewed 504 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: 2759
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: More Excel Tools During Migration

Postby red1 » Sat Jun 15, 2019 6:20 am

The Migrate Excel Data can now be used to Update changes to already migrated data from Excel sheets into the iDempiere Database. This is when we wish to make some changes only to certain data.

1. First you prepare the data that you want to change. For example let's say you wish to change M_Product table data.
a. So you have a sheet by that name in your data - M_Product.

MigrateChanges.png (36.42 KiB) Viewed 25 times
2. You put the 'WHERE' values in the first two columns. So in this example, M_Product_ID and M_PriceList_Version_ID is the unique key set.

b. The rest of the columns will be direct 'SET' of the data that will override those that is specified in the 'WHERE' index set.

c. The result SQL statement is 'UPDATE M_Product SET [PriceLimit],[PriceList] WHERE [M_Product_ID] AND [M_PriceListVersion_ID]

In this way, you may change any Table with any data based on a visible Excel spreadsheet that acts as a reference of your changes.

OOPS! I nearly forgot to say how to run it from the Migrate Excel Data program. After bringing it up, fill it with the following.

CheckActiveToUPDATE.png (17.81 KiB) Viewed 25 times
3. Put the link to your Excel workbook.

a. Check the Active box, which is the secret flag to trigger an UPDATE instead of an INSERT.

b. Fill in the additional WHERE option, but it is mandatory so as to force the user to be fully giving the secret knock to the code that this is indeed an UPDATE. But if there is no optional WHERE to use, just use any dummy for example, IsActive='Y'. It can be AD_Client_ID=1000000.
Site Admin
Posts: 2759
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 1 guest