Generic Reference Data Loader

[PointsWell]

I have a system with 49 reference data objects and frankly I'd rather not have to import them all manually every time I update my system. I also don't particularly want to write a new import process for each one. So I got constructively lazy.

Using EXEC_STRING and a reference object table I have a generic reference data logger.

There are some prerequisites:
A list of Reference Object Names, a sort order (for items that might have references)
A SystemSettings record with a folder structure to load records from
Correctly formatted CSV files for your reference data.

Thanks to Jaymer I have managed to get Import Logging working properly.

I have attached some sample data for you to play with. The sample data is "as is", namely if you use it outside this example then I am assuming that you have error checked this. There are some known issues with the data, partly deliberate.
There are duplicate dial codes. This is to give you an import log that contains some errors.
There is a typo in Bosnia and Herzegovina in one of the files. I don't know which one at the moment, so it will create a spurious Country record that lacks the rest of the Country data.

Step One - Create a directory with three sub directories, you need a holding, processing and completed directory. You will need them for step four. Copy the reference data files into whatever you have named the holding directory. The path fields in SystemSettings are 200 characters long. If your path is longer than this then you will need to edit the field max length in SystemSettings.

Step Two - Import the RefObjectList from the 0000-RefObjectList.csv

Step Two - Import the RefObjectList.CSV

Screen Shot 2019-02-07 at 14.01.43.png (75.21 KiB) Viewed 8314 times

I could probably have written a process to do this but why re-invent the wheel?

Return to the Home page and you will see the screen below. The Fields are Sort - this determines the order in which the reference data is loaded, File Name - the CSV file that it is looking for (the file can be named anything, I have just added the sort number as a prefix in order to check things more easily) Object Name - the name of the object being loaded and CSVFile this is a document attribute - don't put anything in here.

Step Three - Return to the Home page

Screen Shot 2019-02-07 at 13.10.20.png (97.31 KiB) Viewed 8315 times

Next press the Start Import button.

You'll then be asked for the path to the three subdirectories you created in Step One. This is a once-off activity field max length is 200 characters.

Step Four - define where AIM should be looking

Screen Shot 2019-02-07 at 13.51.10.png (30.56 KiB) Viewed 8315 times

The process will then run in the background and update the log file as it goes.

—-continued next post due to file upload limits —-

[PointsWell]

--- Continued due to file upload limits on posts---

For Log records with the Comment "File contents imported" you can expand the view to show you the log entry and press the view button to see the results.

Import Results

Screen Shot 2019-02-07 at 13.10.05.png (123.68 KiB) Viewed 8314 times

[PointsWell]

Room for Improvements:
It would be good if I could search the BOs in AIM for Entities starting "Ref" and auto generate the RefObjectList

Issues:
If you malformed your CSV files it will crash gracelessly, I don't know how to trap those kinds of error.

Tips:
At the risk of stating the obvious, I have found that when generating my reference data CSVs it is best to build the columns from most regular to least regular length as this makes it easier to use block editing (or column editing).