Creating a Custom Account Export Template

ScotiaConnect has a powerful tool that allows you to customize multiple elements of your Account Export file by creating an Export Template.

Using this function you can save an Export Template as an Export ID from the custom Transaction Export Template or Balance Export Template screens.

After you save an Export Template with your desired settings. You will be able to quickly reuse those settings each time you select your saved template. To do this, select the template name you saved from the Export ID drop-down on the Transaction Export from Template and/or the Balance Export from Template screens.

Note: The Template does not save your date selection so each time you use a Template, you will be required to select a new query date from the Date drop-down menu.

The Export Template screen allows you to define:

a. Export File Format

§ Fixed Length Text (.TXT)

§ Comma Separated Text (.CSV)

§ Tab Separated Text (.TTX)

§ Excel (.XLS)

b. The accounts that are queried.

§ USA Accounts (WIA or OPR)

§ Canadian (DDA, OLL)

§ Global (Swift)

c. The transactions types that are to be exported using filter criteria.

d. The record sort order.

e.g. Transaction Date ascending, Account Id ascending and Account Currency descending.

e. Which fields are included in your export file and what order they are displayed.

f. Define the date format using a date mask

e.g. yyyyMMdd, MM/dd/yyyy, yyyyjjj (Julian date) etc.

g. The field definitions such as “Substring Start”, “Adjusted String Length” and “Field Format” e.g. Zero padded, Right Justified, Left Justified.

Selecting Accounts for a Custom Account Export

To create a custom Transaction Export Template:

(Follow same process for Balance Exporting)

1. From the Account Information menu, select Transaction Search then select Transactions - From Template menu to access the Transaction Export Templates screens.

2. Click on the create link to access the Create a Custom Transaction Export Template screen.

3. In the Export ID field, enter a name of the new Export ID. Use a descriptive name. For example a good name would allow you to remember what the query is going to do. Your name can be up to 20 digits in length.

e.g. ChequesXLS-CAN to indicate the Query is setup to query yesterdays transactions and output an excel file or ALL-CSV-USA to indicate the query is setup to query ALL transactions CSV format on your USA domiciled accounts.

FixedALL-Global-Julian to indicate you have setup Template that queries your SWIFT accounts, uses a fixed file layout, and you have customized the date format using a Julian Date.

4. From the Transaction Type drop-down select a Transaction Type, to narrow your search query. You can select one of the filters below:

§ All Transactions

§ All Debit Transactions

§ All Credit Transactions

§ Cheques

§ Deposits (CCP)

§ Sundry Debits

§ Sundry Credits

§ Chargebacks

§ Credit Line Transactions

§ Investment Transactions

§ Merchant & POS Transactions

§ Service Fees

§ Interest

5. From the Export Format dropdown menu, specify the file format that is to be used to Export the data.

§ Fixed Length Text (.TXT)

§ Comma Separated Text (.CSV)

§ Tab Separated Text (.TTX)

§ Excel (.XLS)

6. Check the Include Headings check-box if you require your file to have a header row.

(Note for Fixed length if you column is smaller than the header row name the name will be truncated. Usually you don’t export a header for fixed length text exports)

7. From the Record Layout dropdown menu, choose the source of the data that you would like to Export.

· Canada (Accounts domiciled in Canada)

· USA (Accounts domiciled in the US)

· Global (Accounts reported via the SWIFT Global network)

8. To change your date format. Click the select link beside Data Format.

9. The fields associated with the selected source file will be listed on the lower half of the screen. To assign fields to the Export ID, highlight the field in the Available Fields list and click on the greater than arrow button. To move all fields use the double arrow button.

To remove a Field from the Export ID, highlight the Field in the Export Fields do the opposite.

If you would like to change the column order, use the up arrow or down arrow buttons to order the fields how you want them.

(This is especially useful if you are using the fixed width file format since the application you are trying to export data into may require the columns to be in a specific order)

10. To Select the accounts to be included in the Transaction Export Template:

a. Click on the Select Accounts button to access the Accounts for Custom Account Export screen.

b. Highlight the desired account or accounts and click on the greater than arrow or the double greater than button to move the account from the Accounts Available the Accounts Assigned window.

c. To remove an assigned account, do the reverse.

d. Click on the Save button to save your selections and return to the Create a Custom Account Export screen.

You can click the Save button to save your new template at this point or if you are an Advanced User you can use the advanced field definition capabilities of ScotiaConnect to define each field in your export file using the Field Definition button.

11. To define each field click the Field Definition button.

You will see a list of all the fields you have selected and the options you can change for each one.

Substring Start:

You can or what part of the field gets exported click the size is assigned to each balance and transaction download field, however, the size can be increased or decreased to suit your needs.

Adjusted Length:

You can increase or decrease the adjusted length of a field by typing in a value.

(if you are using the fixed length export and the length of the field is shorter than the header row the header will be truncated. To allow yourself to see the header fully you can increase the row size to a larger value.)

For example if you have a 10 digit field containing 1234567890 and:

· you want to export an entire field, the Substring Start should be set to 1 and Adjusted Length of 10.

· you want to export only the first 5 characters of the field, the field should have Substring Start of 1 with an Adjusted Length of 5. You will get 12345

· you want to export only the last five characters of the field the field should have a Substring start of 6 and adjusted length of 10. You will get 67890

Sort Order:

You can define up to three primary sort fields in ascending or descending order.

Field Format:

You can define if the field is Left Justified, Right Justified or Right Zero filled.

For example:

Left Justified:

123456789012345

00012345

Right Justified

123456789012345

00012345

Zero Padded

123456789012345

000000000012345

Once you are done formatting each column click the Save button to save you configuration and return to the

Creating a Custom Account Export Template

Related Topics

Account Export – Balance & Transactions

Getting Additional Help