Delimited ACH Map

Creating a Delimited ACH Map allows the user to define the file layout so the Import process can identify the structure and intent of the file being imported.  

To display the Delimited ACH Map screen:

• Select the ACH option on the Funds Transfer menu and then click Import ACH Data.  The Import ACH Data screen appears, then click on the Create New Delimited ACH Map hyperlink, the Delimited ACH Map screen appears.

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

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: If the imported file contains participant records that don't exist in the template or the record for update is not found based on matching criteria, then those participants are added to the participant table and assigned to the selected batch template.

Note: The 'Delete' process will disassociate the participant from the selected batch template, the participant is not deleted from the participant table.

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

2. The Map Name allows you to describe the file being imported and will distinguish it from previous imports. Because the created maps are saved and are re-useable, it is suggested that you create a map name that can be used to identify the type of map that was created.  Note: that special characters are not allowed in the map name.

3. The Delimiter drop down menu lets you select the appropriate delimiter used in the file you are importing.

4. End of Record ID – Select the appropriate record/line terminator from the drop down menu. Note: The End of Record Id and the Delimiter cannot be the same.

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

6. Total Number of Fields in Record/line – Enter the number of columns/fields in the file.

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

8. 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).

Note:  Excel files will typically wrap text fields in quotes. If you are unsure whether the fields contain quotes, select Yes and the Import process will detect and remove if needed.

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

The following fields are available for import:  

• Transaction Code

• FI Routing Number

• Account Number

• Amount

• Participant ID

• Participant Name

• Addenda Data

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

• The Match column is used to locate the participant record that exists in the participant table and match with the participant data in the import file.

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

See Section 'Creating an ACH Map' under 'File Formats' on detailed information how to create an ACH Map.