CSV/Delimited Importer

Introduction

A delimited file is made up of elements that are separated (or delimited) by a particular character. In theory the delimiter character can be any character but common delimiters are the comma, semicolon and tab. Usage of the comma as a delimiter is so common that files using a comma are usually known as CSV files. CSV stands for Comma Separated Values.

A CSV/Delimited importer is used to read in a CSV/Delimited file into paygate ready for payment processing. The CSV import conforms to the rfc4180 specification. The rfc4180 text can be found online here

In creating an importer we not only read the file, we also give the file meaning and context. For example we attempt to define in an importer, that a certain field is a sortcode, an amount or a transaction code. We make use of this contextualisation throughout paygate with features such as rules, and auto-mapping.

Creating a CSV/Delimited Importer

Title

Give the importer a meaningful title that makes the purpose or use of the importer obvious. For example ‘Oracle payment file’ would be a good example of a tile.

Description

The description is an optional field that you can use to describe the importer in more detail.

File Location

An optional field to set the default file location of the imported file. This can be modified as the importer is used and so can be left blank if you don’t know it or if the location or filename will change over time.

Sample File

The next step in creating a CSV/Delimited importer is to configure how the file is structured. The quickest and easiest way to do this is to present a sample file. The importer will analyse the file and build most of the configuration automatically.

If you don’t have a sample file or you want to configure the importer manually - you can click skip.

The sample file you use should be representative of the file you will use when you run live mappings. The sample file should:

• Use the same delimiter character
• Use exactly the same columns
• Use the same data type in each cell
• Use the same header
• Use the same quote character
• Contain as least 2 data rows. A File with around 10 rows is recommended

When you drop a file into the sample file box the importer will scan the file and analyse the structure. It will then configure the importer accordingly. Finally the importer will make a guess at what the various columns are.

File Cleaner

Often a payment file will contain hidden control characters that cause import problems. Or sometimes, if the file originates on a Unix system or mainframe, the row delimiters may cause import problems.

Another common problem is that a file uses an incorrect character that is incompatible with, say, BACS. Incompatible characters can cause payments to fail and so need to be dealt with.

These common problems can easily be dealt with by using the File Cleaning capabilities that are built into paygate importers.

Cleaner Rules

Cleaner Rules are a set of common rules that, when selected, modify the incoming file before it is imported into paygate. They are designed to make importing easier by normalising the files ready for importing.

Convert to Upper Case - Converts all lower case characters to upper case.

Remove ASCII Nulls - This rule strips ASCII Nulls (ASCII Code 0) from the file.

Convert LF to CRLF - Files that use LF to terminate individual rows have the LF replace with CRLF

Convert CR to CRLF - Files that use CR to terminate individual rows have the LF replace with CRLF

Convert CRLF to CR - Files that use CRLF to terminate individual rows have the LF replace with CR

Convert CRLF to LF - Files that use CRLF to terminate individual rows have the LF replace with LF

Convert CR to LF - Files that use CR to terminate individual rows have the LF replace with LF

Convert LF to CR - Files that use LF to terminate individual rows have the LF replace with CR

Convert To UTF-8 - Converts the entire file to UTF-8 format

Character Set

When File Cleaning is enabled, the importer will inspect the characters in the file and substitute any characters that are not in the selected character set, to a whitespace. For example, if the BACS character set is selected then all non-BACS characters are replaced with a whitespace. This is a good way to ensure that the imported file only contains legal characters without causing the format or structure of the file to be altered.

Substitution

Substitution replaces single characters in an imported file with a substitute character. You can create multiple rules in an importer that substitute one or more characters.

Click ‘Add Rule’. Enter the single character that you wish to replace in the ‘From’ box. Enter the substitute character in the ‘To’ box. Click ‘Add Rule’ again to add more substitution rules.

Remove an existing rule by clicking the red ‘trashcan’ icon.

You can enable or disable individual rules using the check boxes in the ‘Enabled’ column.

File Parameters

If you used a sample file to build the importer this section will have been automatically configured. You can, however, make changes to the configuration if you need to.

Delimiter

The special character used to separate (or delimit) each cell of the file. Common delimiters are a comma, semicolon and tab. To enter a tab character copy and paste a tab from a text editor such as notepad++.

Header

CSV files often include a header in the first row. You should enable this setting if the file includes a header. This will prevent the header from incorrectly being read as payment data. The analyser will normally detect the presence of a header and automatically enable the setting.

Ignore First Rows

Sometimes a file has a non-standard header that you wish to ignore when importing the data. These files can be used as long at the non-standard header is a fixed number of rows. Enter the number of rows that you wish to skip.

One thing to be aware of is if you ignore some rows and also enable Header. This will probably mean you will end up ignoring the header - which is probably not what you intended.

Ignore Last rows.

Enter the number of rows that you wish to be ignored when the file is imported. This is useful if the file includes a non-standard section at the end of the file.

Quote Character

Quotes are used in CSV files when cell text itself includes the same character that is being used to delimit the file. An example of this would be the inclusion of a comma in an address. The importer analyser will normally detect if quotes are used and automatically set the correct character. If not, manually enter the quote character.

Trim Whitespaces

Enable to trim (remove) white spaces from the start and end of data in the file.

Comments Character

If comments are used in the CSV file, enter the comment character in this box. The importer will ignore any rows that begin with the comments character. # is a common comment character and is used as a default.

Ignore Empty Rows

If set the importer will ignore any blank or empty rows within the file.

Columns

If you dropped a sample file into the importer then the analyser will have hopefully made a decent job of automatically configuring the files columns.

In the example above the analyser has correctly identified the Amount, Account Type and Transaction Code.

It has detected a sortcode and account number but it cannot determine whether these are the payer’s or the payee’s account details. It is making a suggestion to you about what it thinks the data type is.

The analyser was unable to make any suggestions for the first two columns.

Using your knowledge of the file you should now make any final configuration changes to the columns.

Manually adding a column

To add a new column click the Add Column button in the bottom right of the column section.

Changing the order of columns

The order of columns in a CSV file is important. To change the order of columns click and hold the icon that looks like three horizontal lines to the left of the column section. While holding the mouse down, drag the column to a new position.

Removing a column

Click the red Trash Can icon on the column you wish to remove.

Cloning an Importer

You can make a clone of the importer by clicking the Clone button at the foot of the page. Cloning an importer creates a new importer with the same configuration as the donor importer. The new cloned importer is now independent of the donor importer and configuration changes can be made without affecting the donor. Both the donor and clone importer now have separate logging records and can be used in different mappings.

Other Importer Rules

During import the CSV importer will attempt to detect and correct data that may cause problems when storing the data. The following rules are followed

Amount

Any amount that cannot be expressed as a valid decimal number will be converted to a zero (0) amount.

Negative amount will have the negative sign removed. For example a value of -123.45 will be imported as 123.45.