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
RawSheet.png (131.55 KiB) Viewed 1567 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
SynchMaster.png (15.37 KiB) Viewed 1568 times
After running this you will see the results magically done at the Master sheet:

MasterSheet.png
MasterSheet.png (94.69 KiB) Viewed 1567 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
Tanspose.png (15.44 KiB) Viewed 1568 times
The SplitSymbol was a space.

The plugin is at http://bitbucket.org/red1/org.red1.map

DataMapMenu.png
DataMapMenu.png (12.09 KiB) Viewed 1563 times
Binary with 2Pack in https://sourceforge.net/projects/red1/f ... Migration/
red1
Site Admin
 
Posts: 2760
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
MakeMigrateSheet.png (16.91 KiB) Viewed 1561 times
The result is as follows:

TableSheet.png
TableSheet.png (23.11 KiB) Viewed 1561 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.
red1
Site Admin
 
Posts: 2760
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
MigrateChanges.png (36.42 KiB) Viewed 1082 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
CheckActiveToUPDATE.png (17.81 KiB) Viewed 1082 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.
red1
Site Admin
 
Posts: 2760
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