Import File Formats

Import File Formats

To Import a file, Users must first define the nature of the data to be Imported. Since a single file often contains multiple types of information, the User must define the format of the file itself, allowing the PCR-360 application to process the data using the same format as that implemented by the external file. These Import Formats all related to specific things that can be imported. A list of all the definitions that can be utilized can be found in the 'Importing a File' wiki.

Creating Import File Formats

Navigate to the Import File Formats grid by opening the 'Admin' tab and clicking on the node labeled 'File Management'. Click on the node labeled 'Import Files'. Then click on the node labeled 'Import File Formats'. To add a new import file format, click the 'Add' button on the Grid Toolbar above the Import File Formats Grid.

In the data entry form, the User must define a number of required fields.

First, give the new import file format a Name. Next, define the File Type as either Delimited or Fixed width. If the User defines the File Type as Delimited, the User must also define a Delimiter (generally a comma but not necessarily). Next, specify the number of 'Header Lines' and 'Footer Lines' that exist on the file to be imported, if any.

A User can also specify the optional 'Retention' value. This is a number of days to keep the Import Files and Imported Records that are imported with this Import Format. This does not affect the data that was imported, it just cleans up the logs and original raw files of the import job. If this is blank, then the old logs are never deleted.

Users can also define the fields under the header 'Record Volume Notifications'. When a 'Low' and 'High' threshold is specified, the PCR-360 application will automatically send an email to the 'Recipient' if the file is above or below a certain size.

The 'Record Format Flag' values allow a User to specify a position or delimited index where there might be a value that will determine if the input line should be processed. This is described more below.

The Import Process has the ability to automatically process files that are put into a directory on the server. For this to work, the filenames must match the values provided in the Filename Prefix and Extension fields. The Filename will match if it begins with the same characters provided in the Prefix field. The Extension must exactly match. If no value is provided for the Extension, then the Filename extension must be blank. More information about uploading a file can be found here.

If the 'Allow Duplicate File' checkbox is set, then it will allow you to upload and process the same file content twice. This does not check the filename, it is checking the file content. For most import types this can be turned on. It is turned off by default as a precaution against accidentally uploading the same GLA Charge or Service Charge file twice, which will create duplicate charges. If this checkbox is turned off, then an error will happen on the upload of a duplicate file.

Import File Record Format

In the tab labeled 'Record Format', the User can define all of the unique record types and what columns are contained in each one.

The Sequence value is a way to number the Record Formats if there are multiple of them, so a User can control the processing order. Each line of the input file will be processed by each Record Format, in the Sequence number order. It is recommended to increment these values by 10s, so it is easier to insert one into the series later if needed.

The Flag Value is used with the Record Format Flag fields from the parent form. On the parent form, the location of the Record Format Flag was identified. In this form a value can be provided that might exist in that position. If the provided value is in that position, then the line will be processed. If there is a different value in that position, then the line will be ignored. This can be used to prevent non-data lines from being processed. For example, in a file with Calls and other data, if the Call records always have a 'C' in a certain column, then the Flag Value could look for that, and ignore all other lines. Or, a User might have a file with Calls and Charges in one file. If there is a column that contains a value to identify which is which, such as the keywords Call or Charge, then a User could have two Import Formats. One with Flag Value 'Call', and one with Flag Value 'Charge'.

If the Flag Value is left blank, then all lines will be processed by this Import Format.

The Record Type indicates what is being imported by this Record Format. Selecting different values for this field will change what columns are available to be mapped, and what tables the imported data will go into. Changing this field may also show or hide other fields on this form.

Calls and Usage Imports 

If Calls or Usage are selected as the Import Record Type, then the User will see these additional fields on the Import File Record Format form:

When the Usage or Call Record Types are selected there are some fields that become required. Below are what those required fields are and how they operate.

Duplicates

The Duplicates flag allows a User to control what happens when two calls have the same From Number, To Number, Date, Time, and Duration.

The File Format can be set to do the following upon reading in another copy of the Call:

1. Allow
2. Drop
3. Keep with an error status 

Usage Type

The Usage Type Field is an informational field that inserts a Value as a Column into all the records for that Record Format. 

When a value is selected from the drop-down then this value will get applied to the resulting Usage Records.

The name of a Usage Type can be changed as well as new Usage types added by going to Admin > System Tables > List Values. 

The PCR-360 default values for Usage Type are Data Storage and Bandwidth. However, the User can add as many new values as desired through the List Value USAGE_TYPE.

Service Type

If a value for this is selected, then only that type of Service will be searched for when looking up the Service that the Usage will be billed to.

Rating Method

A Rating Method is a method used to assign Charges to a call record format. For a Usage Record Type the Rating Method is required. The three types or Rating Methods are listed below.

• Rate Tables

Maps to given Rate Table defined in Admin.

The rate must be found in the Call Rates Table.

• Pass through cost in file

The costs for the call will come from the file that is being imported.

• Flat Rate

Activates additional fields to set flat rate. 

The additional information comes from the fields in the Initial and Additional sections.

Column Mapping

Now that we know that call records all have a C in position 1, we need to say what and where the rest of the data is in this record type - that is what the Column Mapping is for.

To add entries into the Column Mapping table, simply select the Add button in the Grid. The data entry form looks like this:

The Map To field is required and the list of values is displayed is linked to the type of data that will be imported in this record type. In our example, because the “Call” Map Type was selected, the Map To fields are all related to Call data.

Note: the import file may not have all of the columns in the Map To field. This is OK.

Once the column is selected, the User must enter the Start and Width information for Fixed Width files or the Delimited Index for Delimited files to define where this column of data is in the record. The Start Field defines where the column start in the Import File. The Width Column defines where the Column starts in the Import File.

The Format Mask field will be enabled if the data type of the field is Duration, Date, Time, or Date/Time. For any of these types, you should provide a format string to tell the import logic how the value will look in the input file. If you click on this field, a small list of values will display to let you know what the legal values are for this field.

For Duration, these values are available:

• H - Hours with separator
• hh - 2 digit hours
• h - 1 digit hours
• I - Minutes with separator
• ii - 2 digit minutes
• i - 1 digit minutes
• S - Seconds with separator 
• ss - 2 digit seconds
• s - 1 digit seconds
• f# 1/# - fractions of a minute
  
  

Common Examples:
Hour, minute, seconds, separated by colon: "H:I:S"
2 digit hour, 2 digit minute, 2 digit second: "hhiiss"
2 digit zero padded hour, minute, seconds, separated by colon: "H:I:S" or "hh:ii:ss"
1 digit hour, 2 digit minutes, then 1 digit of 10ths of minutes (Avaya call duration): "hiif10"

For Date, Time, and Date/Time fields, these values are available:

• yyyy - 4 digit year
• yy - 2 digit year
• mm - 2 digit month
• m - 1 or 2 digit month
• MMM - 3 letter month abbreviation
• Month - Full month name
• dd - 2 digit day
• d - 1 or 2 digit day
• j - Flag indicating a Julian date
• o - Flag indicating an ordinal date (day of year)
• HH - 2 digit hours in 24-hour format
• H -1 or 2 digit hours in 24-hour format
•  hh - 2 digit hours in 12-hour format
• h - 1 or 2 digit hours in 12-hour format
• ii - 2 digit minutes
• i - 1 or 2 digit minutes
• ss - 2 digit seconds
• s - 1 or 2 digit seconds
• u - Fractional seconds
• AP - Ante meridiem and post meridiam

Common Examples:

Month, day, year with separators: "m/d/yyyy"
Time with AM/PM: "hh:ii:ss A" or "hh:ii:ss P"
Date and Time with fractional seconds: "mmddyyyy HHiiss.u"

Conditional Logic

The Conditional Logic form allows a User to attach conditional logic to the import. This functionality is very flexible but also highly technical and will require the skills of technical personnel. Due to the complex nature of the capabilities of this functionality, this portion of the import will be reviewed by the customer and a PCR-360 programmer.

For more information on Conditional Logic, please see this article.

Header Lines Tab

If the User specified either Header or Footer lines in the Manage Import File Format in Tenant form, the Header Lines Tab will appear. These headers and footers may be defined by pressing the Header Lines tab. The data entry form is displayed.

The User should define the total number of lines that were specified in the Manage Import File Format in Tenant form.

For each line, the User should define the columns that have pertinent information relating to the file, for example, bill date. If the header or footer is simply memo data from the originating application (description of file, record counts, etc.), then those columns don’t need to be defined, just the line itself. In this case, the line needs to be defined so that PCR-360 can recognize that it is a record that can be ignored since it doesn’t contain importable information.

If columns need to be defined, add or edit the line via the Grid. The data entry form will be displayed:

Select the valid Map To field from the displayed list and for Fixed Width file types, specify the Start position and width of the column. For Delimited file types, select the Delimited Index (the column number for this field). 

Remarks Tab

The Remarks tab provides the User the ability to add comments and Remarks to this Service. By default, a Remark cannot be edited or deleted once it is added.

Note: Remember that depending on the System Configuration Remarks may not be editable once added.

Add Remark

Remarks are useful for making notes about a Return Order that might need to be recalled in the future. To add a Remark, follow these steps:

1. Click the button.
2. This will open the 'Add New Remark' form.
   
3. Enter the Remarks desired into the field.
4. Click the 'Save New'   button.

The Remark will be added to the list along with a User ID and time stamp for tracking.

View/Edit Remarks

Double-click or select the  button to open the Remark.

1. The form will open so the User can read all the content of a given Remark.
2. Click the button to commit any changes made to the Remark.

Delete Selected

Clicking the button will delete any existing Remark.

Use of HTML Tags

Some fields within PCR-360 allow Users to input HTML tags to make displaying that information a richer experience for those that are expected to consume it. For more information on HTML tag usage, please see the wiki entry for it.

Attachments Tab

Uploading a File

To attach one or more files, navigate to the 'Attachments' tab and click the button located on the Grid Toolbar above the 'Attachments' Grid. The User is prompted to select file(s) from their hard drive to upload. Upon selection of a file(s), the upload begins automatically.

Download File

Allows a User to download a file to their local machine. Select the file that is desired to download. Click the  button.  This can be useful to make modifications to a file which can be re-uploaded after edits are completed.

Open File

This opens the file, if possible, for viewing by the User. Select the file that is desired to be opened. Click the  button. This opens the file for viewing on the local machine. No modifications of the original file can be made when opened in this manner.

Edit Selected

Allows the name of the file to be modified within PCR-360. If a User modifies the name of the file, that is the new name used when downloading the file. To edit the name of the file, first select the file. Next click  button. This opens the 'Manage Attachment' form.

Edit the 'Filename' field and click the button. 

Delete Selected

This removes the file from PCR-360. To delete an item, select it and click the  button.

Setting up a Batch Import

A User can combine multiple Import Types to create more powerful Import File Formats. This allows for the creation of one file, to run a batch of Imports at once.

For example, say a User wants to Import a new Payment, by a new GLA Charge for a GLA that does not yet exist. With Batch Importing, a File Format can be setup to perform all intended operations at once, rather than requiring one Import File for each Record Type.

To set this up, A User would create one Import File Format that has three Import Record Formats. Each Import Record Format would have Mappings and Conditional Logic that referred to one csv File Format, but using only the columns that were needed by each Import Type.

It is important for a User to be careful to have each Import Record Format have the correct Sequence value. Lower Sequence numbers are imported before higher values. In this example, the GLA would need to be first, since it is needed for the GLA Charges and the Payments.

The Import Process will run each line of the csv file on all of the Record Formats before moving on to the next csv line. If any part of the processing of a line fails, then the remainder of that line will abort, and all changes made by that line will roll back out of the database. So in this example, if the GLA succeeds, then an error happens while creating the GLA Charges, the Contact and Payment steps will not get run, and the successful GLA operation from that line will get undone. The GLA Charges error will get reported in the Imported Records grid. Then the process will move on to the next csv line. What this means is that each line of the Import will succeed or fail on it's own and not affect the rest of the records in the Import File.