CDR File Converters

Below is a full guide on how Billingbooth One can convert CDR 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 CDR File Converters

In order to process Call Data Record (CDR) files, Billingbooth requires them to contain certain information and to know how that information is formatted.

CDR files are generated by your telecoms provider usually on a monthly basis, sometimes daily, containing a full list of all the voice, data and SMS usage lines rented by you have used since the previous CDR file.

They will often be provided in a Comma Separated Values (CSV) format. This can be in the form of an actual .CSV file, or even a .TXT file that contains CSV formatted data.

When you upload a CDR file to Billingbooth, you'll select a provider from the list of available providers. The list of providers is a list of all the providers Billingbooth currently has a parser in place for, a parser being a piece of code that checks to make sure everything Billingbooth needs is included in the CDR file and points Billingbooth towards the information it needs from the file.

This leads us on to file converting itself.

What is a file converter?

Whilst we have a CDR 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 goes through 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 CDR 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 will 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 CDR 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 CDR 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 CDR File Converter reference section handy, which goes over all the code snippets you'll likely need.

How to parse a CDR 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 CDR file from VoiceHost and create a parser for it. However, VoiceHost is already in our provider lists so you won't need to create a parser for it unless you want to do anything specific with VoiceHost CDR files.

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 CDR 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 CDR File Converter reference article handy, which gives you an overview of all the code you will likely need.

To get started, navigate to the CDR Parser screen by heading to Products in the left-hand main navigation and then selecting Suppliers from under the Groupings sub-heading in the sub-menu. From the Suppliers listings, click the CDR Parser icon for your respective provider in the Actions column.

INFO

You don't want to open CDR 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 CDR converter
Checking this means that this supplier will appear in the provider lists when uploading CDR 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.

Charge code matching
Select how Billingbooth will match the charge code of the record. With Exact matching, the charge code value on the record will need to match the charge code contained in the associated destination. With Recursive matching, the charge code value on the record will attempt an exact match, and if nothing is found will then trim off the last character and try again. It will repeat this process until either a match is found or there are no more characters left to trim.

As an example of a recursive match, if we were trying to match a destination with the charge code +441234567890, the following matches will be attempted until something is found:

+441234567890
+44123456789
+4412345678
+441234567
+44123456
+4412345
+441234
+44123
+4412
+441
+44
+4
+

Has header row
If your CDR 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 CDR 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 CDR 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 CDR not actually exist on the file we upload.

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

The above looks for the heading Call Type 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.

You can copy and paste the above code, replacing the Call Type wording with the name of other headings you wish to make sure are in your file and you'll also want to switch is the field object's property which is currently set to unitType if your code is looking for something that doesn't correspond to the data type of the line item.

Going through the example VoiceHost CDR, 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.

However, we don't have a singular column for dateTime, instead we have a column that handles the date of the line item and another that handles the time, so for our purposes we can just point both of those columns towards the dateTime field property as below:

if (!record.hasNamed('Call Date')) {
  mapper.missing(field.dateTime, 'Call Date');
}
if (!record.hasNamed('Call Time')) {
  mapper.missing(field.dateTime, 'Call Time');
}

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

if (!record.hasNamed('Call Type')) {
  mapper.missing(field.unitType, 'Call Type');
}
if (!record.hasNamed('Customer CLI')) {
  mapper.missing(field.cli, 'Customer CLI');
}
if (!record.hasNamed('Telephone Number Called')) {
  mapper.missing(field.destination, 'Telephone Number Called');
}
if (!record.hasNamed('Call Date')) {
  mapper.missing(field.dateTime, 'Call Date');
}
if (!record.hasNamed('Call Time')) {
  mapper.missing(field.dateTime, 'Call Time');
}
if (!record.hasNamed('Duration')) {
  mapper.missing(field.billableSeconds, 'Duration');
}
if (!record.hasNamed('ChargeCode')) {
  mapper.missing(field.chargeCode, 'ChargeCode');
}
if (!record.hasNamed('Description')) {
  mapper.missing(field.description, 'Description');
}
if (!record.hasNamed('Salesprice')) {
  mapper.missing(field.cost, 'Salesprice');
}

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 CDR 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 CDR 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.

unitType
The VoiceHost CDR we're using as an example, doesn't actually have a column to define what unit type the line item is, but we can hard-code the unitType value as the CDRs from VoiceHost only contain VoIP calls.

mapper.map(field.unitType, 'voice');

cli and source
The mapping of the cli field property is straightforward as we simply map it to the Customer CLI column. For the source field property, however, we don't have a column specifically for the source so we simply use the Customer CLI column for that too as in this instance, the CLI is the source.

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

destination
Another simple field property to map as we can assign the Telephone Number Called column.

mapper.map(field.destination, record.named('Telephone Number Called'));

dateTime
You may remember that on our example VoiceHost CDR file, the date and time are split over two columns, so we're going to have to join them together into one for our purposes. To do that, we simply append a space to the Call Date, followed by appending the Call Time.

mapper.map(field.dateTime, record.named('Call Date') + " " + record.named('Call Time'));

billableSeconds
For the Duration column, we need to do a few things to it to get it fit for purpose. If we check our reference article for what billableSeconds requires, it's the duration in whole seconds, whereas the VoiceHost CDR file's Duration column uses a H : MM : SS format.

Looking at the reference article we have a util (utility) object that contains a helpful method for converting a duration to whole seconds, so let's use that to give us something easier to work with:

var seconds = util.durationToSeconds(record.named('Duration'));

Next up, we need to ensure we only map the call if its duration is at least 1 second, to do this we use a conditional if statement, where if the seconds variable is less than a second we return an error, otherwise we map the seconds variable to the billableSeconds field property.

if (seconds < 1) {
  // the row 'Duration' column is less than 1 second, so we mark this row as an invalid call
  mapper.error('The record has no duration');
} else {
mapper.map(field.billableSeconds, seconds);
}

kilobytes and smsCount
As mentioned earlier, VoiceHost only deals with VoIP calls at the time of writing this article, so we don't need to worry about mapping these two field properties. However, for other providers, you'll may need to utilise conditionals like we did with billableSeconds here to ensure you're not processing a data line item that has used 0 kilobytes or an SMS line item that has sent 0 messages.

chargeCode, description and cost
The last three field properties are all straight forward maps, where we can simply assign the ChargeCode, Description and Salesprice columns respectively.

mapper.map(field.chargeCode, record.named('ChargeCode'));
mapper.map(field.description, record.named('Description'));
mapper.map(field.cost, record.named('Salesprice'));

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

mapper.map(field.unitType, "voice");
mapper.map(field.cli, record.named('Customer CLI'));
mapper.map(field.source, record.named('Customer CLI'));
mapper.map(field.destination, record.named('Telephone Number Called'));
mapper.map(field.dateTime, record.named('Call Date') + " " + record.named('Call Time'));
var seconds = util.durationToSeconds(record.named('Duration'));
if (seconds < 1) {
  mapper.error('The record has no duration');
} else {
mapper.map(field.billableSeconds, seconds);
}
mapper.map(field.chargeCode, record.named('ChargeCode'));
mapper.map(field.description, record.named('Description'));
mapper.map(field.cost, record.named('Salesprice'));

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

Once saved, in the Test parser section, upload the CDR file you wish to test if you haven't done so already and click the Run Test button to see if your CDR 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'll want a unique charge code for destinations called so that you can set up Destinations, Price Adjustments and Bundles for this new provider accordingly.

Sample file

Click here to download the sample file used in this tutorial

CDR 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 CDR 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 CDR files.

Property	Description
unitType (Required)	The type of record, the values of this can be either voice, data or sms.
cli (Required)	Field utilises to match the call to a customer. This is typically a phone number or account code.
source	In calls this is typically the "From" number that we use to display in the itemised billing.
destination	In calls this is typically the "To" number that we use to display in the itemised billing.
chargeCode (Required)	The identifier utilised for matching against a destination. This can sometimes be alphanumerical codes such as UKN, UKFM1, etc, or if the provider only supplies telephone numbers then we choose the "To" destination as a dialling prefix.
extra	Any additional information you may want to include as part of the call. This field is typically optional.
description	Textual representation of what the record was for, e.g. "UK Mobile", "UK Geographic", etc.
dateTime (Required)	The date and time of the record. If the month/day is getting reversed due to the files using a non-British date format, try using ISO 8601 format (YYYY-MM-DD HH:mm:ss).
billableSeconds	If the record is a call, the number of whole seconds required for billing.
kilobytes	If the record is data, the number of whole kilobytes required for billing.
smsCount	If the record is SMS, the number of SMS messages required for billing.
cost	The cost of the record expressed in major currency. So a call that cost 10p would be passed as 0.10.

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('Customer Number')) {
  // the record doesn't contain a field called 'Customer Number', so we mark it as missing
  mapper.missing(field.cli, 'Customer Number');
}

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('duration') < 1) {
  // the row's 'duration' column is less than 1 second, so we mark this row as an invalid call
  mapper.error('The record has no duration');
}

skip()
Skip the current row from being processed.

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

util

A utility object containing some useful methods for conversion of data.

Methods:

durationToSeconds(value)
Converts a timespan to a numeric amount of seconds.

var timespan = '00:04:43';

// seconds will equal 283
var seconds = util.durationToSeconds(timespan);