Service File Converters

Below is a full guide on how Billingbooth One can convert service files, along with all reference material. Some basic development knowledge is required (the converter uses Javascript with ECMA 5.1 compliance), if you are unsure then please contact us.

Concepts of Service File Converters

Suppliers often provide a list of monthly service charges via things called service files. These are typically Comma Separated Values (CSV) files that contain a full list of charges, along with unique references to customers.

Billingbooth is able to automate the task of applying these service charges onto customers by utilising a matching process to figure out what products and customers the service file information belongs to.

On a monthly basis your supplier will give you a copy of these service charges which you will in turn be able to feed into Billingbooth by uploading it via the portal or through our Auto Collection system.

Once this file gets uploaded, the file will need to be converted from its raw format into something Billingbooth can understand.

What is a file converter?

Whilst we have a service file, we need to know that it contains the information that we require in order to bill what we need and if it does, we need to make it easy for Billingbooth to look up this information.

To this end, we create a file converter, which parses the information in the file, checking it exists and its integrity before telling Billingbooth how to access it.

How a file converter works

The process will generally be, you upload your service file to Billingbooth and then the converter goes to work.

The converter will check the headings of the file (if headers are included), to make sure that the columns of information Billingbooth will need to look at exist. If they don't exist at this stage, Billingbooth can simply return an error telling us that a particular column doesn't exist, letting you know of the issue.

Lastly, it will parse each row in your service file and as it does, you will have told Billingbooth (through adding code to the parser) to map specific columns to specific properties. For example, telling Billingbooth which column contains the CLI which will be used to identify your customer(s).

Will I need to create a file converter?

Whilst we will have file converters listed for a variety of providers already, if you don't see your provider in the list, it's going to require a file converter to be created.

What will I need?

You'll want to have a service file from the provider you're looking to add, ideally one that has a some real-world data in it. Along with that, you'll want to have our Service File Converter reference section handy, which goes over all the code snippets you'll likely need.

How to parse a Service File

INFO

This guide requires at least a basic knowledge of Javascript, if you are unable to modify the parser yourself, please get in touch with us.

For the purpose of this article, we're going to use an anonymised service file and create a parser for it.

It should also be noted, whilst you should be able to copy and paste a lot of this example to help you with your parser, you will still need to adjust certain aspects to suit your provider or needs specifically, so having an understanding of what's going on will help you exponentially.

Getting prepared

Firstly you'll need a service file from your provider to work with, ideally you want it to also have some line items so you can ensure it's being processed as intended.

Next, it will be good to have our Service File Converter reference article handy, which gives you an overview of all the code you will likely need.

To get started, navigate to the Service File Converters screen by heading to Products in the left-hand main navigation and then selecting File Converters from under the Service Files sub-heading in the sub-menu. From the Suppliers listings, click the edit icon for your respective supplier in the Actions column.

WARNING

You don't want to open service files in Microsoft Excel, as Excel will truncate leading zeros and symbols (such as +) from numeric values as it doesn't understand them. This can lead to you accidentally corrupting the data within. We recommend downloading Notepad++ as a really good non-destructive text editor.

Options
On the left-hand side, you have some general options for how this parser will function:

Allow Service File converter
Checking this means that this supplier will appear in the provider lists when uploading service files etc.

Type of file converter
CSV File Converter is currently the only option available at present, which is fine as you will likely be working with a CSV format file.

Has header row
If your service file contains a row dedicated to headings (often on the very first row), make sure this option is checked.

Start at (Optional)
By default, having nothing entered into this field will have the parser start from the very first line, otherwise you can enter the line number of the service file you wish.

Required information

Before we dive into creating the actual parser, it's worth noting Billingbooth requires all the properties of the field method to function (except for the extras property which is optional).

Javascript

Now we're ready to write the parser itself, this will be done exclusively in Javascript.

Firstly, if your service file contains a header row we'll first need to validate the headings.

Header validation

TIP

As you go through adding code, be sure to click the Save Changes button every now and then so you don't accidentally close your browser window or tab and lose everything you've added.

The reason we want to validate the header is to spare us from numerous errors should something we expect to exist on the service file format, doesn't actually exist on the file we upload.

if (!record.hasNamed('Customer Identifier')) {
  // the record doesn't contain a field called 'Customer Identifier', so we mark it as missing
  mapper.missing(field.cli, 'Customer Identifier');
}

The above looks for the heading Customer Identifier and if it doesn't exist, calls the mapper object's missing method to return a debug friendly error to tell us the field is missing.

Going through the example service file, the bulk of the columns we need are there, we simply need to copy paste the above code and tell Billingbooth what the corresponding heading names are and what their corresponding field property is.

So with the last few fields added in, our header validation comes out looking like this:

if (!record.hasNamed('Customer Identifier')) {
  mapper.missing(field.cli, 'Customer Identifier');
}
if (!record.hasNamed('Description')) {
  mapper.missing(field.description, 'Description');
}
if (!record.hasNamed('From Date')) {
  mapper.missing(field.from, 'From Date');
}
if (!record.hasNamed('To Date')) {
  mapper.missing(field.to, 'To Date');
}
if (!record.hasNamed('Quantity')) {
  mapper.missing(field.quantity, 'Quantity');
}
if (!record.hasNamed('Unit Cost')) {
  mapper.missing(field.cost, 'Unit Cost');
}

We want to have a check for any column we need when it comes to grabbing information for the parser, because if we use something in our parser and it turns out the column doesn't even exist, we're gonna generate an error every time Billingbooth goes looking for a cell from that column.

If your service file doesn't contain headers, Billingbooth will at least mitigate the amount of errors returned by only returning the first 100 errors generated.

Before continuing, if you've made use of the Header validation, now would be a good time to click the Save Changes button and then down in the Test parser section, upload the service file you wish to test and click the Run Test button. Whilst nothing will be mapped yet, it will still tell you if any errors have been found, which could mean you need to tweak your heading validation.

Once you're error-free, continue on to Row parsing.

Row parsing

Now we know our columns exist, it's time to put them to use.

cli
The mapping of the cli field property is straightforward as we simply map it to the Customer Identifier column. The cli field is used in service files to match the charge to a customer within Billingbooth.

mapper.map(field.cli, record.named('Customer Identifier'));

description
Another simple field property to map as we can assign the Description column. The description field is used in service files to match against products within Billingbooth, defining the sell pricing of the charge.

mapper.map(field.description, record.named('Description'));

from and to
The from and to properties define the start and end period of a specific charge, typically a recurring one. In instances where the charge is a single or one-off charge, the to field is likely to be blank. For this reason, the to field is optional, but if available should be mapped so that pro-rata charges can be calculated correctly.

mapper.map(field.from, record.named('From Date'));
mapper.map(field.to, record.named('To Date'));

quantity
A single charge may have a quantity bigger than one, resulting in a charge whose total price is unit cost x quantity.

mapper.map(field.quantity, record.named('Quantity'));

cost
In order to calculate margins, Billingbooth looks at the cost or buy price found in a service file.

mapper.map(field.cost, record.named('Unit Cost'));

The cost property should be in major currency (£x.xx, $x.xx, €x.xx etc). If the value of our Unit Cost was in Pence, rather than Pounds, we would need to divide it by 100 to get the major currency equivalent, e.g.

mapper.map(field.cost, record.named('Unit Cost') / 100);

It's also worth highlighting that the cost property should be set per quantity. In instances where the individual unit cost is not available, you could divide the total charge by the quantity, e.g.

mapper.map(field.cost, record.named('Total Cost') / record.named('Quantity'));

Putting all of our mapping together, we end up with the following:

mapper.map(field.cli, record.named('Customer Identifier'));
mapper.map(field.description, record.named('Description'));
mapper.map(field.from, record.named('From Date'));
mapper.map(field.to, record.named('To Date'));
mapper.map(field.quantity, record.named('Quantity'));
mapper.map(field.cost, record.named('Unit Cost'));

Remember to click the Save Changes button if you haven't done so already.

Once saved, in the Test parser section, upload the service file you wish to test if you haven't done so already and click the Run Test button to see if your service file is being processed correctly.

To show this, you have a general stats panel and then the first 100 rows are given to you in the Sample result section.

Check for any errors in the Errors section of the general stats panel if the sample isn't coming up or if things aren't quite right with it. You may need to fix any issues found in either the Header validation or Row parsing sections respectively.

Wrapping up

Your end goal, is to have the line items display the information needed by the required information table above, ideally using CLIs you may already have set up if possible. Likewise, you will need to have products configured with the correct product descriptions to match the charges in the service file.

Sample file

Click here to download the sample file used in this tutorial

Service File Converter Reference

The following helper objects are available in Javascript:

record

This object is the context for the current row being processed when parsing service files.

Methods:

named(columnName)
Returns the value of the current row for the column named columnName

// returns the value of the column with heading 'cli'
var cli = record.named('cli');

index(columnIndex)
Returns the value of the current row for the column index specified in columnIndex. Indexes are zero-based.

// returns the value of the third column (indexes start at zero, so 0, 1, 2)
var cli = record.index(2);

hasNamed(columnName)
Returns true if the file contains a column with the heading columnName.

if (record.hasNamed('cli')) {
  // this code will execute if the file contains a column named 'cli'
}

hasIndex(index)
Returns true i the file contains a column at the specified index.

if (record.hasIndex(2)) {
  // this code will execute if the file contains at least three columns
  // indexes are zero-based, so 0, 1, 2
}

raw()
Returns the raw line of the current record.

// returns the raw value of the current row
var raw = record.raw();

headerRaw()
Returns the raw header line of the current file. If the file has no header, then the first line of the file will be returned.

// returns the raw header value of the current file
var header = record.headerRaw();

filename()
Returns the filename of the current file.

// returns the filename of the current file
var filename = record.filename();

field

This object contains a series of pre-defined fields that match the call detail record properties we match when processing service files.

Property	Description
cli	Field utilises to match the charge to a customer. This is typically a phone number or account code.
description	The charge description, typically a product or service name. This field is used to match against products found in Billingbooth.
quantity	The number of licenses or charges for that specific row
cost	The cost of the charge, per quantity, expressed in major currency. So a charge that costs 10p would be parsed as 0.10.
from	If the charge is a recurring subscription of some kind, this would be the start date of the period covered, e.g. 01/01/2020.
to	If the charge is a recurring subscription of some kind, this would be the end date of the period covered, e.g. 31/01/2020.
sellPrice (Optional)	If the matching product has Use Service File Sell Price enabled, this maps to the sell price of the row.

mapper

The object used for mapping one of the fields listed above to a specific value. Use this object in conjunction with record to match the row's values to the eventual value.

Methods:

map(field, value)
Maps the field to a specific value.

// maps the cli field to the current row's `cli` column
mapper.map(field.cli, record.named('cli'));

missing(field)
missing(field, columnName)
Marks a specific field as missing, returning friendly errors for debugging. You can optionally specify the columnName for more verbose output.

if (!record.hasNamed('Description')) {
  // the record doesn't contain a field called 'Description', so we mark it as missing
  mapper.missing(field.cli, 'Description');
}

error(text)
Add an error for the processing of the current row. This method is used for adding errors about the current row that relate to anything other than a missing field.

if (record.named('quantity') < 1) {
  // the row's 'quantity' column is less than 1 second, so we mark this row as an invalid charge
  mapper.error('The record has no quantity');
}

skip()
Skip the current row from being processed.

if (record.named('status') != 'active') {
  // The row's status is not active, so we won't bill this charge
  mapper.skip();
}