Login  |  Help      
    Tuesday, August 14, 2018 8:21:56 am MDT

Mixed Record Import File Specifications for SIP

(Updated 22-Dec-2017 )

Import files can have any name with a '.tsv', '.csv', or '.ach' suffix.

The tab-separated-values (tsv) or comma-separated-values (csv) file contains any number of the following records with header records or other comment lines beginning with the '#' character. Blank lines are also ignored.  The fields in each record are separated by tabs and optionally enclosed in double-quotes (").  The file is transferred to SIP via the File Transfer page available as a link from the merchant home pages of merchants enabled to import these files.  The various record fields are described below. Each file can contain any mix of these records.

NACHA formatted files must be named with the suffix .ach and conform to the National Automated Clearing House Association's rules.


CreateUser/UpdateUser

Use these records (.tsv or .csv) to create a new user or to update the information in a user's profile:

# Field Name Format Comment
1 RecordType Fixed string CreateUser or UpdateUser
2 MerchantID numeric Merchant ID defined by SIP (if left blank it will default to current merchant)
3 UserAccount 50 char Merchant defined user/customer/employee account ID
May be unique within the merchant database but not required to be
This allows multiple users' payments to be applied to the same account
4 UserID 50 char Merchant defined user/customer/employee personal ID
Must be unique within the merchant database
5 UserFirstName 50 char  
6 UserLastName 50 char  
7 UserAddress1 50 char  
8 UserAddress2 50 char (may be blank)
9 UserCity 50 char  
10 UserState 2 char 2 letter standard abbreviation
11 UserZip 5 digits  
12 UserPhone 10 digits Non-numeric characters ignored
13 UserEmail 50 char  
14 UserLogin 50 char Required to provide user login capability
Must be unique within the SIP database and can only contain letters, numbers periods or '@'
If not unique, numeric suffix will be added to make it unique - see log file for altered UserLogin
15 UserPassword 50 char Required to provide user login capability - must include both letters and numbers and be at least 10 characters long
(opt)16 (reserved) reserved Ignored
(opt)17 EmailNotification boolean Indicates that the user wishes to have email notifications sent to the UserEmail address . (default to 1)

CCDebit/CCCredit

Use these records (.tsv or .csv) to schedule payments from or to credit/debit cards:

# Field Name Format Comment
1 RecordType Fixed string CCDebit or CCCredit
2 MerchantID numeric Merchant ID defined by SIP (if left blank it will default to current merchant)
3 UserAccount 50 char Merchant defined user/customer/employee account ID
Required to associate payment with predefined user (default: none)
4 UserID 50 char Merchant defined user/customer/employee personal ID
Required to associate payment with predefined user (default: none)
5 Amount #####.## Dollar.cents amount, $ and commas optional
6 Fee #####.## Dollar.cents amount (if non-zero, it overrides Merchant Fee), $ and commas optional
7 TransactionID 50 char Transaction ID defined by merchant (may be blank)
8 Category 50 char Default "Imported"
9 Memo 100 char (may be blank)
10 TransactionDate MM/DD/YYYY Default to today
11 CreditCardName 50 char Name shown on card
12 CreditCardNumber 13 to 16 digits Card number with or without spaces
13 CreditCardExpYear YYYY Year of expiration
14 CreditCardExpMonth MM Month of expiration
15 CreditCardCVV 3 or 4 digits Security Code from back of card (required by some merchants)
16 CreditCardStreet 50 char Street portion on billing address
17 CreditCardZIP 5 digits ZIP code of billing address

BankDebit/BankCredit

Use these records (.tsv or .csv) to schedule payments from or to bank accounts. An offsetting charge to the merchant bank account will be created as a total of all credits within the file. This offset will be scheduled for processing the day the file is imported or on the next banking day and credit payments will not be scheduled for any day sooner than after that charge has cleared.

# Field Name Format Comment
1 RecordType Fixed string BankDebit or BankCredit
Credits with a TransactionDate earlier than clearing hold time from the date of import will be delayed till the offsetting charge payment clears
2 MerchantID numeric Merchant ID defined by SIP (if left blank it will default to current merchant)
3 UserAccount 50 char Merchant defined user/customer/employee account ID
Required to associate payment with predefined user (default: none)
If the user account does not exist (can be created with CreateUser) one will be automatically created with minimal information
4 UserID 50 char Merchant defined user/customer/employee personal ID
Required to associate payment with predefined user (default: none)
5 Amount #####.## Dollar.cents amount, $ and commas optional
6 Fee #####.## Dollar.cents amount (if non-zero, it overrides Merchant Fee), $ and commas optional
7 TransactionID 50 char Transaction ID defined by merchant (may be blank)
8 Category 50 char Default: "Imported"
9 Memo 100 char (may be blank)
10 TransactionDate M/D/YYYY Default to today for debits and the day the offsetting charge payment clears for credits
11 BankAccountName 50 char Name shown on bank account
12 BankRouting 9 digits ABA for US banking institutions only
13 BankAccount 22 digit string May have leading zeros
14 BankAccountType 1 fixed char (C)hecking, (S)avings, (B)usiness checking

AccountBalance

Use this record (.tsv or .csv) to define account balance values:

# Field Name Format Comment
1 RecordType Fixed string AccountBalance
2 MerchantID numeric Merchant ID defined by SIP (Defaults to ID of importing merchant)
3 UserAccount 50 char Merchant defined user account ID as defined in same field of CreateUser
4 Date MM/DD/YYYY Date balance value was updated (Default to current date)
5 Balance $#####.## Balance available as of Date - dollar.cents amount, may be negative, $ and commas optional

Bill/BillDetail

Note: This format is proposed but not yet implemented. Contact Instant Payments to request implementation.

Use this record (.tsv or .csv) to define bills.

# Field Name Format Comment
1 RecordType Fixed string Bill
2 MerchantID numeric Merchant ID defined by SIP (if left blank it will default to current merchant)
3 UserAccount 50 char Merchant defined user account ID as defined in same field of CreateUser
4 Date MM/DD/YYYY Billing Date (Default to current date)
5 Balance $#####.##
-#####.##
$####.##CR
Amount due - numeric dollar.cents amount
May be negative to indicate credit
Inclusion of 'CR' indicates credit.
6 DueDate MM/DD/YYYY Due Date
7 BillName 50 char Name on bill
8 ServiceAddress 100 char  

Detail records (.tsv or .csv) immediately following the definition of a Bill are used to define line items on the bill. Use as many of these detail records as you need to define your line items:

# Field Name Format Comment
1 RecordType Fixed string BillDetail
2 Description 50 char Description of line entry on bill - will be displayed as is
3 ItemAmount 10 char
Item amount as a string - will be displayed as is

Payroll/PayrollDeposit/PayrollDetail

Note: This format is proposed but not yet implemented. Contact Instant Payments to request implementation.

Use this record (.tsv or .csv) to define payroll transactions and/or pay stubs. Employee or User Accounts should be setup, using a CreateUser record defined above, prior to uploading payroll records for employees. This defines the UserAccount that you then use as the EmployeeID in the payroll record. The user's mobile phone number and email address are specified in the CreateUser record or updated in the UpdateUser record along with two flags indicating whether the employee wishes to have their pay stub sent to them via an SMS text message on their phone or to their email address.

# Field Name Format Comment
1 RecordType Fixed string Payroll or PayrollDeposit
2 MerchantID numeric Merchant ID defined by SIP (if left blank it will default to current merchant)
3 EmployeeID 50 char Merchant defined UserAccount (see CreateUser record above) - this is used to connect a payroll record to a user profile, if one exists.
4 UserFirstName 50 char Employee first name
5 UserLastName 50 char Employee last name
6 StartDate MM/DD/YYYY Start of pay period
7 EndDate MM/DD/YYYY End of pay period
8 PayDate MM/DD/YYYY Pay date
9 Hours 20 char Hours paid or other string, i.e. 'Salaried'
10 Currency 5 char Default to USD
11 GrossPay 12 char Positive amount of gross pay
12 TaxesWithheld 12 char Positive amount of taxes withheld from pay
13 OtherWithholding 12 char Positive amount of other non-tax withholding
14 NetPay 12 char Positive amount of net pay (gross pay minus all withholding)
15 Category 50 char Category of payment (default: 'Payroll')
16 BankAccountName 50 char PayrollDeposit records only - Name shown on bank account
17 BankRouting 9 digits PayrollDeposit records only - ABA for US banking institutions only
18 BankAccount 22 digit string PayrollDeposit records only - Must include any leading zeros
19 BankAccountType 1 fixed char PayrollDeposit records only - (C)hecking, (S)avings, (B)usiness checking

Detail records (.tsv or .csv) immediately following the definition of a payroll or payroll deposit entry are used to define line items on the pay stub. Use as many of these detail records as you need to define your withholding line items however, be aware, that these are purely display items and are not used in any way to alter the GrossPay, TaxesWithheld, OtherWithholding, or NetPay above.

# Field Name Format Comment
1 RecordType Fixed string PayrollDetail
2 Description 20 char Description of line entry on pay stub (displayed as is)
3 CurrentAmount 12 char Current amount of line entry as a string (displayed as is)
4 YearToDate 12 char Year-to-date amount of line entry as a string (displayed as is)

A standard payroll should include the following (check with your local regulatory requirements for details):

  • Employer information - these are all predefined in the employer/merchant profile
    • Name
    • Address
    • Telephone number
  • Employee information - these are all predefined in the employee/user profile with the ID connecting the payroll record with the corresponding user profile
    • Name - UserFirstName & UserLastName are found in both the user profile and payroll record but not compared
    • ID (ie. last 4 digits of SS#) - see EmployeeID above, this is used to find the user profile for a given payroll record
    • Email - UserEmail from user profile
    • Mobile number - UserPhone from user profile
  • Earnings
    • Rate of pay - this can be specified in a PayrollDetail record, leaving the YearToDate field blank
    • Hours worked (straight time and overtime) - Hours field in Payroll record
    • Piece-rate units earned - this can be specified in a PayrollDetail record, leaving the YearToDate field blank
    • Days worked - this can be specified in a PayrollDetail record, leaving the YearToDate field blank
    • Bonus and incentive - this can be specified in a PayrollDetail record, leaving the YearToDate field blank
    • Gross pay/Salary - GrossPay field in Payroll record
    • Net pay - NetPay field in Payroll record
  • Deductions - these can be specified in PayrollDetail records
    • Amount of each deduction
    • Purpose of each deduction
    • Withholdings/allowances
    • Total amount of deductions

SimplePaymentLogin

Note: This format is proposed but not yet implemented. Contact Instant Payments to request implementation.

In order to predefine a set of Simple Payments (see mixed-interface) to be made using predefined logins, a file may be imported to provide the amount of the payment and the account the payment is to be connected with. Each time a file of this type is imported, any and all prior imports of similar data are erased. The file format is tab-delimited with one payment per record and no header record.  The fields in the file are as follows.

One special record (.tsv or .csv) must be included in the import file to define some of the default parameters in the simple payment call. This record is identified its SimplePaymentLogin record type with exactly 10 fields. The only two fields required to be non-blank are the Title and Return_Page fields. The data in this record, if not blank, will override any prior record of this type and the defaults shown above. On this record, the fields are defined as follows:

# Field Name Req Comment
1 RecordType Y SimplePaymentLogin
2 Title Y

Text to place at top of registration form

3 Description   Description of payment amount
4 Return_Page Y Full URL of page to jump to with results
5 Pay_Method
  Method of payment (1 char: Checking, Savings, Business_checking, Visa, Mastercard, Discover, American_express)
6 Graphic
  URL link to graphic to be placed above the registration form
7 Color1   Background color of registration form (RRGGBB using hexadecimal digits)
8 Color2   Background color of registration form headers (RRGGBB)

All the other records (.tsv or .csv) in the file define logins that will use the defined Simple Payment. These records must have a record type of SimplePaymentLogin and must contain 16 fields but optional fields may be blank as shown below:

# Field Name Format Req Comment
1 RecordType Fixed string Y SimplePaymentLogin
2 UserAccount 50 char   Merchant defined user/customer/employee account ID
Required to associate payment with predefined user (default: none)
3 UserID 50 char   Merchant defined user/customer/employee personal ID
Required to associate payment with predefined user (default: none)
4 Login 50 char Y Assigned by merchant to identify customer - must be unique within file
5 Password 6-50 char Y

Assigned by merchant

6 Amount numeric Y Dollar amount of payment due
7 Category 50 char   Category to identify transaction type (default: "Registration")
8 Firstname 50 char   Optional customer information to pre-fill in the registration form
9 Lastname
50 char    
10 Address1
50 char    
11 Address2 50 char    
12 City 50 char    
13 State 2 char   Standard state abbreviations
14 Zip 5 digits    
15 Phone 15 char   10 digits
16 Email 50 char    


Bank ACH Payments or Deposits

Two types of NACHA files are used for defining transactions. The payment file is a NACHA file that contains one or more payments (type 27 or 37) optionally followed by one offsetting deposit (type 22 or 32). The deposit file contains one or more deposits (type 22 or 32) optionally followed by one offsetting payment (type 27 or 37).

NACHA payment files can have more than one batch in them provided that the merchant account in SIP is defined with sub-merchants that have names that match the batches within the file. In this case, the payments will be created in the appropriate sub-merchant account.

Multi-batch NACHA file can be created manually or by third-party software. One example of this is the TOPSTM software using this simple recipe where you can create an ACH file as follows:

  • Click Global
  • Click Direct Deposit
  • Enter create date, i.e. 02-01-2012
  • Select charge frequency to include, i.e. include monthly charges
  • Click Process ACH
  • Click Post & Deposit
  • Select the properties to include
  • Click Process ACH
  • Print
  • Save to: P:\ACH\120201.ach (file name should be the date the report was run)
  • Save

The batch date(s) within the NACHA file is ignored and payments are created and processed on the first banking day after the file is imported except credits will not be processed until after the offsetting debit clears.


Batch Results Files

If a merchant has the SFTP dropbox credentials defined in its merchant profile, a batch results file will be generated for each batch uploaded using either the comma or tab delimited field files or the NACHA format files. The results file will be named with the same name as the batch file itself (including suffix) with the suffix ".results.tsv" appended. For example, the batch file batches/payments_2012-07-05.ach will have a results file named results/payments_2012-07-05.ach.results.tsv. The results file will be copied to the results sub-directory of the SFTP dropbox. The file will contain a single line error statement if for some reason the associated batch could not be parsed, otherwise it will contain one record per payment parsed from the batch file. Each record has the following tab-delimited format:

1
MerchantID Defined by SIP to identify merchant
2
PaymentID Defined by SIP to identify transaction
3
TransactionID Defined by merchant to identify transaction
4
UserAccount Defined by merchant to identify users
5
TransactionDate  
6
Amount  
7
Fee  
8
Name  
9
Status 'Accepted' or 'Error'
10
Reason Reason for error

Results files are retained for at least 3 months in the SFTP dropbox after which time they may be removed by SIP.


Returns Files

If a merchant has the SFTP dropbox credentials defined in its merchant profile, a daily returns file will be automatically generated each banking day. This file will be updated periodically during the day as returns are received until a new file is opened for the new day so to these returns files are not static until the day is passed and a new file is opened. The returns files will be found in the results sub-directory of the SFTP dropbox and will be named YYYY-MM-DD.returns.tsv where the date of the returns is part of the filename itself. All returns for that day will be recorded in the file, one return per record. Any notification of corrections (NOC) will received during the day will also be appended to the file, one NOC per record. Each record has the following tab-delimited format:

1
MerchantID Defined by SIP to identify merchant
2
PaymentID Defined by SIP to identify transaction
3
TransactionID Defined by merchant to identify transaction
4
UserAccount Defined by merchant to identify users
5
ReturnDate  
6
Amount  
7
Fee  
8
Name  
9
Status Return, Correction, or Error Code from this table
10
Reason Reason code given
11 Addenda Correction Addenda

A payment can have at most one return but it may have multiple corrections regardless of whether it has been returned or not. Returns files are retained for at least 3 months in the SFTP dropbox after which time they may be removed by SIP.

Use Instant Payments to accept payments for your business.
SIP Program & Data © 2006-2018 Instant Payments All rights reserved.
~ Terms & Conditions ~ Privacy Policy ~ Contact Us ~
SIP master Version 2.17; API: 2.4
Get Help RSI Security Instant Payments, Payment Processing Service, Clinton, UT