More Excel Tools During Migration

[red1]

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 832 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 833 times

After running this you will see the results magically done at the Master sheet:

MasterSheet.png (94.69 KiB) Viewed 832 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 833 times

The SplitSymbol was a space.

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

DataMapMenu.png (12.09 KiB) Viewed 828 times

Binary with 2Pack in https://sourceforge.net/projects/red1/f ... Migration/

[red1]

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 826 times

The result is as follows:

TableSheet.png (23.11 KiB) Viewed 826 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]

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 347 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 347 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.