(Included with RoAccess)
Validating Spreadsheet Loader
For Rochade 7.x and 6.x
With NameSpaces and Extended Naming

General Introduction

RoLoader comes packaged two ways:

• Stand Alone - for organizations that use ASG's Web Access or have have written their WEB system custom.
• As a RoAccess PlugIn.  You merely add it to your existing RoAccess installation.

Basically:

• Create a spreadsheet or tab separated data file
• Run RoLoader
• Select which columns you want to load
• Select loading options on a per-Attribute basis (like overwrite, append, etc.)
• Select which of the many validations to run
• It Loads into Rochade
  • Text attributes
  • Value attributes
  • Blob attributes
  • Link Attributes (both S and L-Type)
  • NameSpace attributes
  • It takes advantages of Rochade Namespaces and Extended Naming .  This make it also a perfect tool to load data if you are using ASG's Web Access or other custom created software.

What could be easier? 

RoLoader as a Loading Management System

Ever have the situation where you are looking at loaded data, but see a problem and want to know where that data came from?  Of course.  The next question asked is usually, "does anyone know where the data load file is?"  Here may be a problem.  Most times, data that is loaded gets lost or deleted.  Not with RoLoader.

Every file that you load stays available to you, as it is also stored in Rochade.  You can later retrieve the file, even modify it, and then reload it.  When you introduce a data load file into RoAccess, you also can specify where the data came from, what processing steps were done to it in the process, and what are the mappings to the Rochade attributes.  That is RoLoader can hold a complete history of what what was loaded, when, why with complete documentation.  If you want to analyze the load file a year later, no problem, with RoLoader!

To view the RoLoader Product Sheet, click here.

Most companies have a great deal of information, but they reside in a large number of different formats.  These may range from text files, word processing files,  and a variety of other databases or applications.   It is usually impractical to write a custom loader that handles all versions of all possible formats.  We were asked to provide a Spreadsheet output/input interface because even the non-technical user can handle Spreadsheets.  Below we will mention Excel, but the same applies to other Spreadsheet programs.

RoLoader, on the other hand, has a very simple input format: tab separated.  This file can be moved between many different applications. Being simple means you can create the load file from a Excel, which everyone can master in minutes.    Also, you can create programs to generate tab separated files to migrate your current application data to spreadsheet format.  You can either then load these generated tab separated files or read them first into Excel.

While being viewed in Excel,  you can sort by columns, look for misspelled words, make global changes, and in general to data cleanup and quality assurance.   Excel can also produce a number of wonderful reports that can be useful for presenting the data for your inspection before you finally decide to load the data.

Usage Details

RoLoader can load just about any type of data into a Rochade database.   The data format most people are familiar with is Spreadsheets.  Therefore, RoAccess allows you to load your spreadsheet data and gives you a lot of flexibility at the same time.

You load one ItemType at a time.  You can load the entire ItemType or just part of it.  For instance, if you data file has 500 rows, you can load all 500 rows or a subset, loading the rest later.

RoLoader has both data analysis data validation.  In addition you can test load your data without writing the specified actual Items.  It also can provide internal information, such as the actual loader statements it will be using for your inspection.

Creating your ROA_RO_LOADER Job Step

To load your data into Rochade using RoLoader, you first have to upload your data file to Rochade and store it in a special Item.  This Item of ROA_RO_LOADER can be thought of a loading "Job Step" because it contains a great deal of loading information.  This job step can be re-executed later.

The basic steps are:

• Run RoAccess or Stand Alone RoLoader.
• Log in with a user ID such as ROLOADER
• Select the Subject Area/Project you want to load
• List the ItemTypes
• Select the ROA_RO_LOADER ItemType by clicking its radio button
• This lists the Items of ROA_RO_LOADER.  Initially there will be none.
• Switch to Edit Mode by clicking the green "Display Mode" button.  It is a toggle between Display and Edit modes.
• Click the "New Item" blue oval button at the top
• Enter an Item name, such as "ORA/TABLE_STEP_1", and a descriptive DEFINTION. Click the create button.

The RoLoader Screens

RoLoader is a "Loading System", not just a loader.

First you create an Item of ItemType RoLoader to defined what the file is to be loaded.  The data file itself is also uploaded into that Item so the data file can be retrieved at a later time, if you need to re-load it or correct errors and re-load.  Below is such a screen snapshot. You then document everything you know about the data file so anyone at a later time can understand where the data came from, how it was processed, why it is being loaded, and what mappings you might have changed.

RoLoader stores all data loading files internally in Rochade so they can be extracted an re-loaded or modified at a later time.  The data file to be loaded is uploaded using RoAccess simple, automated file upload, storage and download system. Loading is usually done in steps. Suppose you will be loading 100 different files into a particular ItemType.  The Name of the loading step might contain the ItemType to be loaded plus the step number, where the steps number might be a guide to the order you want to load it.  In this case, the data file will create Item in the "APPLICATION" ItemType. 

The attribute ROA_SPREADSHEET_ITEM_TYPE says that ItemType APPLICATION is to  be loaded from this file.

The blob attribute ROA_SPREADSHEET_DATA holds the data file.

The attribute DESCRIPTION it to allow you to input as much background information as you can about this file and its history.

Note, that two attributes are being loaded from this file" PROCESS_AREA and SUPPORT_MANAGER.  There are other attributes that could be loaded, but this data source only had these few attributes.

Below is the same Item in "Edit Mode".

Once you create this "Load Step", you can proceed to run RoLoader by clicking the RoLoader button at the top of the screen.

Below are three parts to RoLoader screen: Summary Information, Loader Controls, Column Loading Options.  Afterwards is a more detailed description.   They are presented first so you can get an overall understanding by first looking at them.  Then they will be discussed individually in more detail.
 
Summary Statistics: 

Column/Attribute information:

Execution Time Options:

RoLoader accepts data in basically columns that are tab separated, just the way you can write out spreadsheets.  You can load:

• Text attributes (can contain hyperlinks, HTML, etc. Can contain multiple lines per cell/attribute)
• Link Attributes (T/S Links and L-Type Links)
• Value Attributes
• Blob Attributes (you can batch load thousands of images or files)

 You are given a number of options to give you the flexibility you need to load the first time for new Items (that do not currently exist), or for loads after Items already exist, where you are either replacing or adding to the content of the current attributes.

RoLoader was developed to directly load spreadsheet data, or any data that is basically in columns separated by a tab or comma.  So the data can be created by SQL database queries, Access queries and even word processors.

It improves data quality because it helps make sure the data you are loaded is correct by a number of validations you can specify.

For instance, it can enforce validations, like:

• A column is an integer or floating point number and in the range 15 to 135.
• A column is a phrase of a maximum of 45 characters to match a PIC of "C45"
• The Item pointed to by a link attribute must actually exist.  While this is optional, it will find spelling errors in Item Names or missing Items.  If you first create the items to be referenced, and then load the link attributes that link to them, you cal validate your links.  Misspelled Item Names otherwise would consume time to find.
• A link attribute may be required to match one a list of the only allowable link qualifiers.
• A value attribute may be required to match a list of the only allowable values.
• If a value or text attribute has an initial/default value, you can elect to have that value automatically loaded or not loaded.

• Load the entire file now.
• Only load a portion of the file.
• Don't load, but just validate the data file or just a part.
• Don't load, but write the data to be loaded to an import file instead.

The extended metadata is how RoAccess can declare attributes Integers, with display PIC's like I4, and validation ranges, like between 10 and 25, specify which values an attribute can have, and which link qualifiers that are allowable..
 

Column Loading Options

This screen was generated automatically presenting only the options that are appropriate based on your RIM, Item Type declarations and RoAccess extended metadata.

Columns

• Load Column?
Whether to load this column on this run
• Col Seq
The column number in the data file
• Attribute
• The attribute name to be loaded by this column
• The word "IGNORE", if this column is not used for loading
• Type
• TEXT - Rochade text attribute
• LINK - Rochade text attribute that is a link
• VALUE - Rochade value attribute
• Ext Type
• Extended metadata type as defined in Item Type ROA_AP-ITEMTYPE
• FLOAT - can be either a text or value attribute, but is to be treated as a floating point number
• INT - can be either a text or value attribute, but is to be treated as integer
• CHAR - treated as character. C40 defines a single line of 4 characters, max.
• Validate Type
• LINK_QUALIFIER
This link attribute has a list of the only allowable Link Qualifiers defined in the extended metadata
• RANGE
This data type is FLOAT or INT and has a validation range defined, such as between 10 and 25.
• WIDTH
This data type has a PIC, which declares a maximum width.  A PIC of I4 says the integer has at most 4 digits.  A PIC of C40 says the value cannot exceed 40 characters.  You determine if f data to be loaded exceeding this width should be allowed
• LIST
This value attribute has a list of the only allowable values defined.
• Validate?
If this column has a constraint, like a RANGE, WIDTH, etc., should validation be enforced or load the data anyway.
• If Attribute Exists
If a text or link attribute already exists, you can specify if you want the current contents of the data to be loaded to completely erase and overwrite the current contents, or be appended to the end.  For an initial load, you would probably specify Overwrite.  However, for subsequent loads, you might specify Append to add the text or new link data.
• Require Link Target Exist?
Rochade allows a link to be defined to an Item that does not exist.  However, as a consistency check, you might want to require that those items already exist, then select Yes.  This can be used as a spelling check on Item names, or to make sure you have already loaded/created those Items before.

Execution Options

Some files can be quite large.  Being able to validate and load sections of the file is a real convenience.

The Delete these Items option can help when you might have accidentally loaded bad data in a large number of Items and you want to just delete them all and start from scratch.  This is a real life-saver.

Diagnostic Options

These are not necessary for the normal usage of RoLoader.  They are provided for those who want a detailed understanding and display of exactly RoLoader is doing or who is trying to understand a problem.

• Display RPL Statements - This will instruct RoLoader to display all the Rochade Procedure Language (RPL) statements that are being generated to be displayed.
• Add Line and Attribute Comments - Normally, the only RPL statements that are generated are those absolutely necessary to load the data.  Sometimes if the data that is not loaded is either incorrect or other than what you expected, by turning on this options, it will add a number of comment lines to the RPL so you can see the beginning of each line in the data file and the beginning of each attribute processing on that line.   This can help you to easily find the line that that contained the data to be loaded, and you can better understand why the unexpected data was loaded.
• ItemName Usage -  When initially learning RoLoader, most people will load small portions of test data to better understand how RoLoader works.  This option causes RoLoader to load all data into one Item by the name of DUMMY_ITEM.  Then you can then view the data loaded with RoAccess for this Item.  When you are comfortable, you can then turn off this option and the actual Items will be loaded or deleted.

BLOBS and Documents

The directory on the WEB server where the document and files are stored.  You specify this once in the "Execution Options" section labeled Document/File Directory.

The data in the spreadsheet cell contains the name of the file (located in that directory) plus an optional comment.  If the file is an image, such as a .jpg or .gif, then the comment can also contain the AUTODISPLAY directive just

filename

filename|comment

The filename does not have any path portion.  Just the base file name.  The comment, which is separated by from the file name by a | vertical bar, is the same as if you were uploading the file directly into RoAccess using the Blob Upload Screen.

Note: when you upload a single file via RoLoader, the file is sent from your PC to the WEB server.  However, when bulk loading files via RoLoader, these files, which could be thousands, must be already on the WEB server.  If you are using a shared or NFS mounted drive, the files could be already available. Otherwise, you should copy or ftp the files to a temporary directory on the WEB server.  After being loaded into Rochade, they can be deleted.

Data File Format

• Tab Separated Columns.  Each value is separated by a TAB. Note: If you have a .CSV (comma separated variables) file, use Excel to convert it to .TSV (tab separated values) format.
• First line contains the Attribute names for that columns, or "IGNORE" for columns that will not be used.  IGNORE is used where the column was in the spreadsheet, but is not to be used for loading.
• The first column must have the name "ITEM_NAME".  This defines the Item that will be loaded by that row of data.
• Lines beginning with "#" are considered comments are ignored.
• Multiples lines can load the same Item.  If a link attribute has 5 links, then it could need 5 lines, each with the link information in its column, input in "append mode" .  Or if an Item has 5 attributes, different lines can load different attributes if it is more convenient for you.
• Text Attributes that are one long line, will be wrapped either at the PIC specification or the default text width.  If you wish to load multi-line text, place "\n" at the end of each line.
• New Person
• This is a single line of text
• Hello\nThere
• this text represents two lines.  This is the only way to represent multiple lines in a spreadsheet, because text lines just wrap around and cannot be made separate lines in a single spreadsheet cell.
• Lines longer than 60 characters will be wrapped, so no information is lost
• Excel "soft returns" in a cell allow you to put "soft breaks" in the text so you can have multiple lines in a cell.  Otherwise, the text will just wrap in the cell and break at different places.  Entering a ALT-ENTER will insert a "soft return" after text in a cell.
• Link Attribute format.  The basic format of a Link Attribute data (for S/T Links and L-Type links) cell is:

->ItemType ItemName, or

->ItemType ItemName (optional link qualifier)

• Multiple links can be entered with \n separating the link entries. Example

->PROJECT PROJ1\n->PROJECT PROJ2 (My Project)\n->CUSTOMER SMITH_JOHN (IES Inc.)

Legacy: If there is only one link target ItemType, there is an abbreviate format:
• The first word in the cell must be the ItemName that link points to.  You do not have to put the ItemType of that Item because RoAccess already knows that from the RIM.  Anything beyond that is the Link Qualifier (do not put parenthesis, because RoAccess will).   Example cell data:
• SMITH_SAM
• The Item pointed to is SMITH_SAM
• SMITH_SAM (Project Leader)
• The Item pointed to is SMITH_SAM and the link qualifier is "(Project Leader)".
• Null Data Handling
• The "Write Null Values" column allows you to define what happens when a cell contains no data.  No data could be two tabs in a row, with no data in-between them.  If you select "Yes, write null values", then if, or each time, an empty cell is found, it will write a NULL value, which will erase any existing contents for that attribute.  However, if you select "No, do not write NULL values", no action will be taken, and the current contents of the attribute for that Item will remain unchanged.
• Append/Overwrite Option
• Text and Link attributes give you the option of either completely overwriting the currently existing value, if any or you can specify that the data contained in this column is to be appended to the current contents of this attribute.
• Text attributes that are defined to be only one line, like a PIC of C40, to not allow appending, since there is only one line, so no append/overwrite buttons will appear
• You are allowed to Append to the DEFINITION attribute, but it is generally not good practice.
• Link Qualifier Treatment
• You can specify a list of the only allowable link qualifiers for each link attribute using the extended metadata.  If such a list is defined, then the link qualifier must in that column cell must be on this list.  However, if the last item on the list is a "*", an asterisk, then any or no link qualifier is permissible.
• Link qualifiers are stored case sensitive and can contain spaces.  They must not contain parenthesis.
• Load Default Value if Null?
• Text and Value attributes can have default values specified in the AP-INIT attribute.  If that cell contains no value, you can decide to treat that cell as a NULL, or you can have the initial value loaded into that cell before it is processed.
• The "INIT:" syntax in the AP-VALUE attribute is not supported, because the AP-INIT attribute is the proper place for initial/default values.
• Require Link Target Exist?
• Data Loaded from a spreadsheet can contain numerous errors.  This is especially true for the targets of link attributes.  Typically the name of the Item pointed to will be misspelled, or if the Item Type allows mixed case Item Names, the case of the Item names in your spreadsheet may not be correct.  In either case, the Item pointed to will not have the same name, and the link created will go "nowhere".
• RoLoader gives you the option of separately checking to see if the Items pointed to by the link attributed so actually exist.  This is done in a separate, standalone run.  No data is loaded or verified.  The only thing done to check to see if the Items pointed to already exist.  A WEB table will be created listing all these items.  The following screen capture gives a sample display:

 

 


 

• It gives you all the information you need to find the line in the data file that has the problem.  If you wish, you can load just that one line by specifying its line number as the line range to be loaded

 
• Validations in General
• There are a number of validations that can be performed.   However a validation error will prevent the entire data from being loaded.  If this is undesirable, you can either:
• Turn off validations for that attribute.  This can store incorrect or inconsistent data, but it will allow the other data to be loaded.
• Turn off data loading for that attribute all together.  You can edit your data, and sometime later re-use the spreadsheet, only loading that attribute's column.
Below is a sample Validation Failure output:


 

• Verify none of these Items current exist
If you are loading NEW items, you will want to make sure there is NOT already an Item by that name.  This will allow you to do that.
Below is a sample of the Failure Output:
 

• Verify no two lines in the data define the same Item Name
You may decide to load an entire Item with a single line in your data file.  So any lines that define the same Item would be a duplicate.  This option allow you to know in advance that no two lines in the data file write or update the same Item.
Below is a sample of the Failure Output:
 
• Verify any documents / files to be loaded into BINARY (blob) attributes exist and are readable
The document loading directory is searched to see if the file exists.  On Unix, the file name is case sensitive.
Then the file is tested to see if it can be read.  Sometimes a file will exist, but permissions at run-time , will not permit the file to be read by the effective WEB Server user.
Below is a sample of the Failure Output:

Special Processing Options

    #OPTION=ALL_LINK_ITS

Then RoLoader will allow link attributes to point to ItemTypes that are not in the Subject Area into which you are loading.  Normally Rochade only allows links to ItemTypes in that Subject Area.