Page 1 of 1

Migrating Legacy Data

PostPosted: Sun Mar 19, 2017 8:53 am
by red1
[BINARY]: ( and CSVs)

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 (101.65 KiB) Viewed 8879 times
You can see for yourself in this movie:

Re: Migrating Legacy Data

PostPosted: Tue Mar 21, 2017 6:47 am
by red1
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 (35.76 KiB) Viewed 8845 times
Note that this is simplistic, comparing to the more powerful 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 Simialrly there is no attachment via corresponding window tab.

*To use this CSV go to ... 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.)

Re: Migrating Legacy Data

PostPosted: Wed Mar 22, 2017 9:36 pm
by red1
Here is probably the heaviest test of the CSV import which is for US Zip Codes. I scrubbed the original ... tabase.csv and broke it up into two files here (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

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 (80.49 KiB) Viewed 8836 times
MigratedZipCodes.png (168.22 KiB) Viewed 8836 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

Re: Migrating Legacy Data

PostPosted: Mon Apr 03, 2017 10:18 am
by red1
In doing the new WMS data model and code design I also used the Ninja plugin and its Excel CSV bulk importing regime to achieve a high level of visibility and manageability. I could import the Module Creator data itself from a master Excel design that spans from that design till exact data formats. Here is a movie i made to demonstrate it in action, handling an error on the fly. The Excel is uploaded to here

For Libero BOM designing, likewise this tool is invaluable - viewtopic.php?f=45&t=1814