From the last post we may recall that we wanted to explore a way to make CSV loaders do more than just load. In this post we look at completing a simple CSV loader that can end date an asset on a route. To keep the loader easier to follow, error checking, data validation, and dealing with linear assets that overlap only part of the input will be ignored. This post does go over the basics of setting up a CSV loader in Exor but, assumes that the user has the correct rights to do so. If more information is required, please refer to the user and administration guides (these can be found on Bentley Communities, product ALIM |Exor then click on files.)
A custom CSV Loader consists of 3 parts:
This blog entry will focus on creating the items needed for the CSV Loader Destination Tables and putting it all together. Our goal is to create a loader that can end date an Asset type over a section of the route network. To so, when the CSV Loader is run, the input file needs to provide the loader with the following information: Asset Type, Route Unique, Begin Offset and end offset. Of course, additional information can be added to enhance the usefulness of the loader.
Now that we know what items we need to make this loader work, it is necessary to create a table for the software to use as a basis for the CSV Loader. If another database object has the necessary columns we could create a view from that object. Create the following table, the data lengths reflect the data lengths used by Exor for that item:
CREATE TABLE Z_CSV_END_DATE (INV_TYPE VARCHAR2 (4), ROUTE_NAME VARCHAR2(50), START_OFFSET NUMBER, END_OFFSET NUMBER);
The table that was created is used by Exor to define our input row type, it is necessary to develop a procedure or package to utilize this input. When loading data, validation is normally necessary, validation is an optional step and for this simple custom loader the validation step is not necessary. Our procedure should do the following:
This example assumes that the network that contains the asset is named RTE, this value should be changed to match the system and network that this loader is run on. Notice that the input is the rowtype of the table that was created above.
create or replace procedure z_blog_enddate (p_row Z_CSV_END_DATE%rowtype) is -- get all the assets on the input route of the input type, -- for this example, we are assuming the route type abbreviation is "RTE" -- To keep this example easy to follow, the query does have the potential to grab -- a bit too much or too little dependent on how the asset happens to be located. cursor c_inv_list is select inv.nm_ne_id_in iit_ne_id from nm_members mem, --route locations nm_members inv --inventory on that route where inv.nm_ne_id_of = mem.nm_ne_id_of and mem.nm_obj_type = 'RTE' -- network name, change RTE to meet your network and mem.nm_ne_id_in = nm3net.get_ne_id(p_row.ROUTE_NAME, 'RTE') and mem.nm_slk >= p_row.START_OFFSET and mem.nm_end_slk <= p_row.END_OFFSET and inv.nm_obj_type = p_row.INV_TYPE; begin for r_list in c_inv_list loop NM3API_INV.end_date_item(r_list.iit_ne_id); -- Defaults to today as the end date end loop; commit; end;
Once the procedure is created the CSV loader can start to be put together. In the Exor forms application, from the launch pad (note if these choices are not available please see your Exor administrator), navigate to Administration Forms > CSV Loader > CSV Loader Destination Tables and fill in the following:
Table Name = Z_CSV_END_DATE
Abbrev = ZEND
Insert Procedure = Z_BLOG_ENDDATE
Then save the form.
Now navigate to Administration Forms > CSV Loader > CSV Loader File Definitions Tables. This form contains the mapping for our loaders input and output. First give the loader a name:
Unique Ref=Z_END_DATE_PROCESS
Description= Blog example to end date assets on a network location
Delimiter= (Pick the desired delimiter for the input file for many Exor users this is a bar “|” for others it is a comma “,” for this example we will leave the default of a comma.
Save the loader
The File Columns Tab is used to give an item form the input file a logical name. That name is called in the next step when defining the outputs.
Please fill in the following:
Seq=1
Column Name=INV_TYPE
Reqd = Checked, for this example all items are required
Datatype = varchar2
Char len = 4
Seq=2
Column Name= ROUTE_NAME
Reqd = Checked
Char len = 50
Seq=3
Column Name= START_OFFSET
Datatype = number
Seq=4
Column Name= END_OFFSET
Now click on the file destination tab, this area allows us to map the input from the file to a “loading” procedure.
Destination Table= Z_CSV_END_DATE
Click in the lower portion of the form and it should fill in. If it does not, save it, close the form, reopen it and search for Z_END_DATE_PROCESS. When the file destinations tab is once again clicked, it should now be filled in.
Use the drop downs to make each row read like the following:
1 INV_TYPE Z_END_DATE_PROCESS.INV_TYPE
2 ROUTE_NAME Z_END_DATE_PROCESS.ROUTE_NAME
3 START_OFFSET Z_END_DATE_PROCESS.START_OFFSET
4 END_OFFSET Z_END_DATE_PROCESS.END_OFFSET
Notice that the drop down lists the name of the loader and the column names defined in the file columns tab.
Click the create holding table button and the csv loader is now created and ready for testing.
Since systems vary greatly it is not possible to give a testing example in this post.
It is important to realize that this example was kept simple but serves as a basis for “loaders” that are more useful. We could add as imports the network type if the system had multiple types, we could add an end date column if we wanted more control over when the item was end dated.
If you have anything that you would like to see in a future post, please feel free to comment below.