Create an Delimited ACH Map

These are the step-by-step instructions for creating then importing a Delimited ACH text file. Instructions for importing Standard ACH files may be found in the Help menu.

The Delimited ACH Map allows you to define the file layout so the Import process can identify the structure and intent of the file being imported. Import file layout definitions are stored and maintained as ACH Import Maps.

The Delimited Map function allows you to define the delimiter and where each piece of the participant’s data is located in the file.

• The Map Type allows you to define a map that will Add, Update or Delete participants in the participant database.

• Map Name allows you to describe the file being imported and will be distinguish it from previous imports. Because the created maps are saved and are re-useable, you should create a map name that can be used to identify the type of map that was created.   

• The Delimiter drop down menu lets you select the appropriate delimiter used in the file you are importing, i.e. comma separated values (CSV) choose the comma (‘,’). The allowed delimiters are as follows:

• Comma (,)

• Colon (:)

• Semi-Colon (;)

• Backslash (\)

• Forward Slash (/)

• Pipe (|)

• End of Record ID – Select the appropriate record/line terminator from the drop down menu. For example, in comma delimited or CSV files the record/line terminator is a “New Line” character which is automatically created when being imported.

• Skip First Header Record – If the file you are importing contains a header, select Yes; if the file only contains data records and a header is not in the file, select No.

• Total Number of Fields in Record/line – Enter the number of columns/fields in the file. For Example, if you file contains a column for Name, Address, Routing Number, Account Number, Transaction Code and Employee ID, the number of columns/fields would be 6.

• Strip Quotes (“”) around Fields – Select Yes if the fields/columns in the file being imported contain quotes at the beginning and end of the fields/columns, select No if they do not.

• Amount Field Contains Punctuation(s) – Select Yes if the file being imported contain punctuations ($,.) in the amount field/column; Select No if the amount field does not contain punctuation(s).

Click on the Map Type option drop down menu and select the appropriate action for the file being imported:

• Add Only – Maps with this option will allow the user to add participant records to the participant table and assign participant records to the user selected existing batch template

• Update Only – Based on matching criteria, maps with this option will change any participant records that exist in the Business Banking participant table with fields from the imported file.

• Add and Update – This Map is a combination of the ‘Add Only’ and ‘Update Only’ processing modes.

• The ‘Add’ process will add participant records to the participant table and assign participant records to the user selected batch template.  

• The ‘Update’ process will employ a user selected matching criteria to locate participant records that exist in the selected batch template. The records will be changed using the update fields from the imported file.  

• Delete and Update – This Map is a combination of the ‘Delete’ and ‘Update Only’ processing modes.

• The ‘Delete’ process will employ a user selected matching criteria to locate participant records that exist in the Business Banking participant table based on the selected batch template but do not exist in the file. This map type will remove association of unmatched participants from the selected batch template.  

• The ‘Update’ process will use a user selected matching criteria to locate participant records that exist in the selected batch template. Matched participants will be changed using the update fields from the imported file.

• Add, Update, and Delete – Maps with this option add participants to the participant table, update existing participant's within the ACH participant table and delete participants from a selected batch template.  

NOTE:  For Add, Update and Delete Maps you must perform the import twice:

1. Import Delimited or Fixed file as an Add Only and select the appropriate Batch Template the import should affect.

2. Import Delimited or Fixed file as a Delete and Update and select the appropriate Batch Template the import should affect.

When importing a Delimited file, for each record/field that can be imported in a file, there is a Field Name.

The fields available for Import are:

• Tran Code (Numeric - 2 digits)

• FI Routing Number (Numeric - check digit calculation)

• Account Number (Alphameric - 17 maximum digits - see table below)

• Amount (Numeric - 10 maximum digits, 14 maximum digits if punctuations are in the file)

• Participant ID (Alphameric - 15 maximum digits – see table below)

• Participant Name (Alphameric  – 22 maximum digits – see table below)

• Addenda Data (Alphameric – 80 maximum digits – see table below)

The following are allowed characters in the fields that are designated as Alphameric, all else, are numeric fields:

• a-z (Alpha – lower case)

• A-Z (Alpha – upper case)

• 0-9 (Numeric)

• SPACE (space or blank can be used, but cannot be all spaces or blanks on selected fields)

• “.” (period)

• “(“ (left parenthesis)

• “)” (right parenthesis)

• “$” (dollar sign)

• “-” (hyphen)

• “?” (question mark)

• “@” (at sign)

• “#” (pound sign)

• “!” (exclamation mark)

• “%” (percent sign)

• “_” (under score)

• “~” (tilde)

• “=” (equals sign)

• “+" (plus sign)

The Field Name actions are defined as Match, Update, and Replacement Value.  

• The Match column is used to indicate what data fields in the file being imported will be used to match to an existing participant record in the participant table.

• The Update column is used to update the information in the participant table with the data in the import file.

• The Replacement Value is information either not in the file being imported or is a change that will affect all matched participants in the import file.  

• The Position Field allows the user to define the location of the Field Names within the imported file.

• Add Only Map Type:

1. Define the first 6 Field Positions in the Position column (Addenda is optional).

2. Please note that the Match and Update columns are not editable, all positions needs to be defined for a Add Only map.  

3. Do not define a Replacement Values.

• Add and Update Map Type:

1. Define the first 6 Field Positions in the Position column (Addenda is optional)

2. Check at least one of the field boxes in the Update column, i.e. if you are updating the Amount for each employee in a Direct Deposit file, you would check the Update box for the Amount field.

3. Select the field(s) to Match based on the following criteria:  

• If matching with the Participant ID, Participant Name, or Account Number only one Match is required, criteria's to ensure the correct participant(s) are updated.   

• If matching with the FI Routing Number you must select an additional field to Match.

Note:  If you assign a Replacement Value, that value will apply to all matched participants within the import file including adding participants.

• Update Only Map Type:

1. Check at least one of the boxes in the Update column and define the Field Position for that field.

2. Select the field(s) to Match based on the following criteria:

• If matching with the Participant ID, Participant Name, or Account Number only one Match is required, however it is recommended that you select a minimum of two Match criteria's to ensure the correct participant(s) are updated.   

• If matching with the FI Routing Number you must select an additional field to Match.

Note:  If you select Replacement Value, that value will apply to all matched participants within the import file including adding participants

• Delete and Update Map Type:

1. Check at least one of the boxes in the Update column and define the Field Position for that field.

2. Select the field(s) to Match based on the following criteria:  

• If matching with the Participant ID, Participant Name, or Account Number only one Match is required, however it is recommended that you select a minimum of two Match criteria's to ensure the correct participant(s) are updated.  

• If matching with the FI Routing Number you must select an additional field to Match.

Note:  If you select Replacement Value, that value will apply to all matched participants within the import file.  Participants are removed from the selected batch template; they will not be deleted from the participant table.

• Add, Update, and Delete Map Type:

This Map Type requires that you import the file twice, once to Add only and a second time to Delete and Update.

1. Define the first 6 Field Positions in the Position column (Addenda is optional).

2. Check at least one of the boxes in the Update column and define the Field Position for that field.

3. Optional:  Assign Replacement Values if applicable.  Replacement Values cannot be used where an Update is being performed.

4. Select the field(s) to Match based on the following criteria:

• If matching with the Participant ID, Participant Name, or Account Number only one Match is required, however it is recommended that you select a minimum of two Match criteria' to ensure the correct participant(s) are updated.  

• If matching with the FI Routing Number you must select an additional field to Match.

Note:  If you select Replacement Value, that value will apply to all participants within the import file

Importing Delimited or Fixed Length ACH Map:

1. Select the Map you have just submitted from the Map Selection screen.

2. Under File Selection click Browse and locate the Delimited or Fixed text file for import.

3. Under Batch/Process Mode Selection section the appropriate Map Type option.

4. Under Batch/Process Mode Selection section the appropriate Batch Template the import should affect.

5. Click Import.

Note:  For Add, Update and Delete Maps you must perform the import twice:

1. Import Delimited or Fixed text file as an Add Only under Batch/Process Mode Selection section and select the appropriate Batch Template the import should affect.

2. Import Delimited or Fixed text file as an Delete and Update under Batch/Process Mode Selection section and select the appropriate Batch Template the import should affect.

Please access the ACH Activity or ACH Admin Approval Activity screen to view your pending import job.

A confirmation screen will be displayed with the Reference number of the Import.

Navigate to the ACH Activity or ACH Admin/Approval Activity screen:

You will be able to preview, approve or cancel the Pending Delimited/Fixed Import Jobs.  Cancel will delete pending activity and removes the job from the preview.  Approval will commit the changes to the participants and selected batch templates.

Note:  Pending Jobs will be deleted after 24 hours.

Select the ACH option on the Funds Transfer menu and then click ACH Activity and verify the delimited import file is available for approval. The imported file can be located using the reference number and appears in the Pending Delimited/Fixed Import Jobs section with a status of Waiting for Approval.

Once you have previewed and verified the changes, select the check box for the Import job and then click the Approve button.  The completion of the Approval process will store the changes to the participant table and move the Pending Delimited/Fixed Import Job to the Complete Delimited or Fixed Import Jobs section with an approved status.