Creating Templates from File in IB4B. CSV file format

CSV Import Overview The CSV import file format allows you to create templates for Direct Credit (including payroll) and Direct Debit transactions from files containing a simple list of payee(ers). This format allows nine different information fields to be imported with each payee(er) line into the new template. 1. Name* 2. Account Number* 3. Amount 4. Other Party Particulars 5. Other Party Code 6. Other Party Reference 7. Your Particulars 8. Your Code 9. Your Reference *A name and account number is required for each line to be imported; all of the other fields are optional.

Example The following is a short (5-payee) example file: Name,Account Number,Amount,Other Party Particulars,Other Party Code,Other Party Reference,Your Particulars,Your Code,Your Reference Alpha,02-0500-0123456-000,50.14,abc,def,ghi,jkl,mno,pqr Beta,02-0500-0123456-001,25,abc,def,ghi,jkl,mno,pqr Gamma,02-0500-0123456-002,12.23,abc,def,ghi,jkl,mno,pqr Delta,02-0500-0123456-003,23,abc,def,ghi,jkl,mno,pqr Epsilon,02-0500-0123456-004,10.01,abc,def,ghi,jkl,mno,pqr

Figure 1 - example CSV file CSV files can also be viewed in a spreadsheet tool like Excel:

Figure 2 - example file shown in Excel The remainder of this guide: 1.  Sets out in more detail the CSV file format that Internet Banking for Business (IB4B) expects for template imports; and 2. Offers some basic advice on creating and working with these files.

Glossary of Terms Term

Description

CSV

A Comma Separated Value (CSV) file is a text file containing a table of information. Each line in the file is a row in the table and commas are used to separate each row into fields. CSV files can be easily created and edited using a spreadsheet tool like Excel or Numbers.

A

Alphanumeric – this field can include any of the following. Letters: a - z and A - Z Numbers: 0 - 9 and special characters: Space

N

Dash

-

Apostrophe

‘

Ampersand

&

Hash

#

Underscore

_

Full Stop

.

Question Mark

?

Forward Slash

/

Numeric – this field can only contain numbers 0 – 9 and a single decimal point

CSV File Format There can be up to 2,000 Payee(er) records in a file. Example: Acme Ltd,02-0985-0999088-025,50,The Electrician Corp,AZ100364C,Oz Corp Field

Field Name

Field Format Comments

1

Name

A(20)

Mandatory field. If more than 20 characters are included the field is truncated during the import. The following characters can be used a - z, A - Z, 0 - 9,space - ‘ & #_.? /

2

Account Number

A(15-19)

Mandatory field. The account or credit card into which the transaction amounts will be deposited (or collected from in the case of a Direct Debit template). The account number can be entered with or without dashes or spaces. New Zealand domestic account and credit card numbers can be 15 or 16 digits long depending on the suffix length, with the extra digit being a left hand zero i.e. an account ending in suffix 25 can also be represented as 025. The following examples are all valid representations of the same account number: • 020100012345625 • 0201000123456025 • 02-0100-0123456-25 • 02 0100 0123456 025 Scheme debit accounts and Diners club card accounts are not valid in templates. Credit card accounts (excluding Diners) can be included in Direct Credit (including payroll) templates but not in Direct Debit templates.

3

Amount

N(12)

Optional. The amount of the payment (collection) in dollars. Negative values cannot be imported. To specify cents include a decimal point.

4

Other Party Particulars

A(12)

Optional. If more than 12 characters are included the field is truncated during the import. The following characters can be used a - z, A - Z, 0 - 9, space, - ‘ & #_.? /

5

Other Party Code

A(12)

As above

6

Other Party Reference

A(12)

As above

7

Your Particulars

A(12)

As above

8

Your Code

A(12)

As above

9

Your Reference

A(12)

As above

Column Headers The first line in the file can be used for column headings. This can make it easier to work with in a spreadsheet tool.

If you choose to include a headings line the following headings (in order) must be used for each of the nine fields: 1. Name 2. Account Number 3. Amount 4. Other Party Particulars 5. Other Party Code 6. Other Party’s Reference 7. Your Particulars 8. Your Code 9. Your Reference So a heading record for a file using all nine columns would look like this: Name,Account Number,Amount,Other Party Particulars,Other Party Code,Other Party Reference,Your Particulars,Your Code,Your Reference Any additional columns (the 10th and above) will be ignored by the import and have no effect on the template created. It is valid to omit columns 3-9 if they’re not required. However it is not valid to omit columns from the middle of the list, change the order or insert other column types other than those listed above. Note: If you decide not to include a heading row in your file the first line in the file should contain the first Payee(er) record.

Generating a CSV file for IB4B using Excel You can easily prepare a CSV file for an IB4B template using Microsoft Excel or another spreadsheet tool. These notes outline how to set up a valid file using excel. The layout of the spreadsheet is shown below:

To create a CSV file use ‘save as’ in Excel and select the CSV format.

A note on Account numbers in Excel Account numbers can be problematic in Excel if entered as a single number without dashes or spaces e.g. 0205000123456000. Excel interprets numbers like this as whole numbers and can reformat them in an unhelpful way.

Figure 3 - Account number reformatted by Excel In the example above the leading zero has been removed and Excel has reformatted the account number into scientific format. Importing files that contain account numbers without leading zeros will cause the account number to load incorrectly. To ensure that valid account numbers are produced by Excel, enter all account numbers enclosed in quotes or use dashes/ spaces as separators. This will ensure Excel interprets account values as text.

Other Requirements To be successfully imported into IB4B to create templates, CSV files must adhere to the following standards: > The file extension must be .csv or .txt > There must be at least one payee(er) entry > The following characters are permitted in the filename: Alpha

a – z, A - Z

Numerical

0-9

(6993) 100989 04-16

Space Dash

-

Apostrophe

‘

Ampersand

&

Hash

#

Underscore

_

Full Stop

.

Question Mark

?

Forward Slash

/

Visit us at

0800 269 4242

bnz.co.nz

[email protected]