Summary

Admin Users can access the Imports/Exports function to export and import batch data. For example, a User could export their organization's E911 data to a local PSAP (Public Safety Answering Point) in order to update critical service location information. Similarly, a User could import monthly billing details from their organization's cellular carrier.

For flexible ad hoc imports and exports, the PCR-360 application also supports an API (Application Programmer Interface) as documented in the ‘API' section of this document. The Imports/Exports function affords Users the ability to adapt to changes in batch file import and export formats and removes the need to seek customization from PCR whenever these changes occur.

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.

Exporting Data From the System

1. Navigate to the Export Files grid by opening the 'Admin' tab and clicking on the node labeled 'File Management'. Click on the node labeled 'Export Files'. Admin > Imports/Exports > Export Files
2. Click the row containing the information you want to export.
3. Click the Generate Export File button. 
4. You can choose to View Export or Download Export file.

Adding a File For Export

To Export a file, Users must first add the file as an item on the Export Files grid. Click the 'Add' button located on the Grid Toolbar above the Export Files grid.

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

First, enter a brief 'Description' to make the file easily identifiable. This 'Description' field is what will show in the Grid after the User saves the 'Add New Export File Format' form. Next, in the 'Source Data' field, the User must specify the 'Source Data' that they are exporting. Each type of 'Source Data' will alter the form's 'File Properties' section to require the desired information for the given Source.

Users can also define the name of the Export File to be created. The 'Prefix' field represents the file's basic name while the 'Extension' field defines the file type (such as csv, txt, xls, etc).

There are three types of 'Source Data': the 'SQL', 'E911', and 'Crystal Report'.

Source Data - Crystal Report

Crystal Report will draw from any currently created Crystal Reports that are available. Simply click the 'Crystal Report' picker in the 'Crystal RPT' tab. A Crystal Report can be Exported as one of several desired 'File Types'; 'PDF', 'Word Document', 'Excel File', 'Rich Text File (rtf)', or 'CSV'. The Filename 'Prefix' field and 'Extension' may also be optionally selected.

The User can also specify a triggered Export Event on the 'Schedule/Event' tab. To specify the Event, follow these steps:

1. Click the 'Add' button  on the Schedule/Event grid.
2. This will open the 'Add New Export Event' form.
   
3. Select either an Event or a Schedule as the trigger to Export the file on the 'Event or Scheduled' drop down.
4. If a 'Schedule' is desired, the User must then specify both the 'Frequency' of the Export and the 'Next Run Date'.
5. Finally the User must select 'Email', 'Outbound', or 'Both Email and Outbound' to determine what to do with the Export.

The Export will now be set up for the Crystal Report.

Source Data - SQL

To Export as a SQL Query, the User must specify a Delimiter and optionally specify the Enclosure. The Filename 'Prefix' field and 'Extension' may also be optionally selected. These will set the name of the file as after it is Exported. SQL must be entered manually as a SQL Query in the 'SQL Query' tab.

The User can also specify a triggered Export Event on the 'Schedule/Event' tab. To specify the, Event follow these steps:

1. Click the 'Add' button  on the Schedule/Event grid.
2. This will open the 'Add New Export Event' form.
   
3. Select either an Event or a Schedule as the trigger to Export the file on the 'Event or Scheduled' drop down.
4. If a 'Schedule' is desired, the User muse then specify both the 'Frequency' of the Export and the 'Next Run Date'.
5. Finally the User must select 'Email', 'Outbound', or 'Both Email and Outbound' to determine what to do with the Export.

The Export will now be set up as a SQL Query.

Source Data - E911

An E911file can be exported as one of three different 'File Types': Fixed Width, Delimited, or Crystal Report.

When Exporting an 'E911' file, there are three tabs of information to be considered: Columns, Header/Footer Lines, and Schedule/Event.

Columns

In the tab labeled 'Columns', the User can define which columns of data populate the file. To add a column of data, press the 'Add' button located on the Grid Toolbar above the Columns Grid.

In the form, Users can select the desired column of data in the field labeled 'Source Column'. The options listed in the 'Source Column' field depend on the User's selection of 'Source Data'. For example, if the User selected 'General Ledger' as the 'Source Data', the 'Source Column' field will list eachcolumn that exists on the General Ledger Grid. If your export file requires a static, repeating value in a particular column; select the Source Column option of “Constant Value”. You can then enter that repeating value in the Constant Value field.

If the User defined the File Type as Delimited, the User must define a numeric Delimited Index. This sequence number determines the order of each column of data in the file. The column of data designated as '1', for example, appears as the first column on the new file.

If the User defined the File Type as Fixed width, the User must define both Start and Width values to determine the column's position and width in the new file.

Header/Footer Lines

In the tab labeled 'Header/Footer Lines', the User can add either or both a 'Header' and a 'Footer' Line – representing the first and last records in the new file – to the file.

To add a header or a footer, click the 'Add' button  on the Grid Toolbar above the Header/Footer Lines grid. In the form, Users should first enter a 'Line Number' to determine the order of the header/footer in the case of multiple entries. If the User is adding a footer, check the box next to the 'Footer' field.

Next, the User can define the column of data that will populate the new header/footer. This is similar to the ones already set on the ''Columns'' tab. To do so, follow these steps:

1. Click the 'Add' button  on the Columns grid.
2. This will open the 'Add New Export File Column' form.
   
3. Select the Column Type from the drop down. These can be '<Constant Value>', 'Row Count', 'File Sequence', 'Column Sum', 'Current Date/Time', or 'Current Date'.
4. The Delimited Index field needs to be filled in as well with the

Once these required fields have been satisfied, click the 'Save New' button  . The new header/footer will appear on the 'Headers/Footers Line' grid.

Editing a File For Export

Users can edit existing files for export by double-clicking on an item in the File Exports grid.

This opens the data entry form. Once all required fields have been fulfilled, click 'Save' to make changes permanent.

Deleting a File For Export

To delete one or more existing files for export, highlight each item to be removed and click the 'Delete Selected' button on the Grid Toolbar above the Export Files Grid.

Importing Data to the System

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.

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. Then, decide how to handle duplicate input records (allow, drop, or keep with an error status).

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 fields under the 'Split Record Flag' header are addressed in the PCR-360 set-up process.

Import File Record File 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 Record Format section builds on the Record Format Flag from mentioned above. On the previous screen, we identified the location of Record Format Flag. In this section, we use that location to identify each unique record type. For Example, if we said the Record Format Flag started in 1 and was 1 position long, we can now say that calls in this file are Flag Value “C”, Taxes are “T”, etc. Each one of these unique record types is defined here. Therefore, in the Record Format Field, you select the type of data in the record type you are identifying. In the example above, this is 'Calls'. Users can enter a Flag Value. In our example, it was the letter “C” but it can be any value that the system generating the data chooses. 

Calls and Usage Import 

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

Usage Import Fields

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

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 from the 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, you can add as many new values as you like.

Service Type:

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

Rating Method

A Rating Method is the 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 you will be importing in this record type. In our example, because we selected the “Call” Map Type, the Map To fields are all related to Call data. Note: Your import file may not have all of the columns in the Map To field. This is OK.

Once the column is selected, you 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. Certain types of data, like dates, can be in any number of formats in the import file such as MM-DD-YY or MM-DD-YYYY, etc. For these fields, we need to know the format. If the Format Mask field is enabled, please select the appropriate Format Mask from the values displayed.

Conditional Logic

The Conditional Logic form allows you 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.

Conditional Logic Flags

Sometimes an import process needs to behave in a non-standard way due to special circumstances. To control this behavior, you can set flags in the import conditional logic. Usually the flags are boolean, but they could be other types.

Syntax to set the flag in the conditional logic:

[FLAGS.IGNORE] = true;

The following is a list of the import conditional logic flags that are available in all import types:

[FLAGS.IGNORE] default false

If set to true, this line of the input file will be ignored (skipped) by the import process. 

[FLAGS.FILENAME]

This is a read only field that contains the filename of the import file being imported.

[FLAGS.FILELINE]

This is a read only field that contains the line number from the import file of the record currently being imported.

There are other flags available in specific import types that are described in the specific import wiki pages.

User Defined Fields

User Defined Fields (UDFs) are handled through the Conditional Logic tab. Any UDFs have to be included in Conditional Logic in order to Import them into PCR-360.

The process of assigning User Defined Fields in Conditional Logic can be found in this link:

Header Lines Tab

If you 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.

You should define the total number of lines that you specified in the Manage Import File Format in Tenant form. For example, if you entered 2 Header Lines and 1 Footer Line, you should define 3 lines in the form shown above.

For each line, you 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.), the 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). When you are finished, close the form.

When you have finished adding/editing your Import File Formats, please be sure and hit the Save or Save New button to be sure your edits are recorded.

Uploading Files

Now that the format of the file to be imported has been defined, the User can upload the file into the PCR-360 application.

Open the 'Admin' tab and click on the node labeled 'File Management'. Then, click on the node labeled 'Import Files'. Next, click on the node labeled 'Upload Import Files'.

In the form, the User must select an 'Import File Format' from a list of items on the Import File Formats grid.

Before uploading, it is recommended that the user test the upload by clicking the 'Test Import File' button and selecting the appropriate file to be imported from the user's disk.

The User can also choose to upload the file by clicking on the 'Upload Import File' button. The User is then prompted to select the appropriate file to be imported from their disk. When a file is selected for upload, the upload process begins immediately.

Importing Files with FTP

When a file is FTPed to the import directory specified by the IMPORT_FILES_PATH configuration option, the following steps are done to that file:

1. The system compares the filename to the File Import Formats table to see if it matches any of the imports.
   1. If it does not match, the file will be left in the directory. There will be no entry in the Import Files grid.
   2. If it does match, an entry into the Import Files grid will be created, keyed by the filename and saved with a hash calculated from the file content and a status of Incomplete.
2. Within another minute, the hash will be re-calculated and compared with the previous value to see if it has changed. This might happen if a large file was not finished uploading on the previous check. If the hash changes, the new hash is saved and the file is checked in another minute. When the hash stays the same value three checks in a row, the file is updated to a status of Complete.
3. When the file is Complete, the final hash value is checked in the Import Files grid to see if the file is a duplicate of a previous file. The File Import Format has an option to specify if duplicate files are allowed. If the file is a duplicate and the toggle is not set to allow duplicates, the status is marked with an error, and the file is not processed further.
4. The file is then compressed with gzip and moved to the archive directory. Within a minute, the actual import process event will find and process the file from the archive directory.

Deleting Imported Files

The following imported files can be deleted:

• Files With Error Descriptions
• Test Files
• Unbilled:
  • Call Records
  • Usage Records
  • Service Charge Records
  • GLA Charges Records

To deleted Files that have been imported, go to Admin > Imports / Exports / Import Files > Import Files.

Once there, select the desired files to be deleted, and click 'Delete Selected' from the Import Files Grid.

A confirmation dialogue will appear, alerting the user what types of files can be deleted.