Overview
This document outlines the format of the CSV files to establish data transfer between on-premise systems and Kolleno through SFTP. The objective of the data transfer is to provide Kolleno with all the necessary data such that Kolleno can manage AR effectively.
Please do NOT insert any NULL values or characters.
Make sure that all column names are lowercase and do not contain any spaces in the name.
There are 3 possible requirements for each field:
Required: you must send the field and a value.
Optional (value): you must send the field but the value is optional.
Optional (value and field): you can decide if you want to send it. If you send, it will always overwrite any value entered by the user.
Customers
File name should be “customers” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “customers_2023-11-16T15:45:30.csv”. Any extra data that would be beneficial on Kolleno can be provided as additional columns in the CSV.
Column | Requirement | Information |
id | Required | Unique among all customers. |
name | Required |
|
ref | Optional (value) | This will be displayed on the platform. |
currency_iso | Optional (value) | 3 letters ISO code such as “USD”. |
address_line_1 | Optional (value) |
|
address_line_2 | Optional (value) |
|
postal_code | Optional (value) |
|
city | Optional (value) |
|
state | Optional (value) |
|
country_iso | Optional (value) | 2 letters ISO code such as “US”. |
credit_limit_amount | Optional (value and field) | Decimal number |
Contacts
File name should be “contacts” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “contacts_2023-11-16T15:45:30.csv”. Any extra data that would be beneficial on Kolleno can be provided as additional columns in the CSV.
Column | Requirement | Information |
id | Required | Unique among all contacts. |
customer_id | Required | This is the “id” column in the “CUSTOMERS” file. |
first_name | Optional (value) |
|
last_name | Optional (value) |
|
email_address | Optional (value) | Max. 254 characters |
telephone_number | Optional (value) |
|
mobile_number | Optional (value) |
|
position | Optional (value and field) |
|
primary | Optional (value and field) | Set “true” or “false”. |
Invoices
File name should be “invoices” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “invoices_2023-11-16T15:45:30.csv”. Any extra data that would be beneficial on Kolleno can be provided as additional columns in the CSV.
Column | Requirement | Information |
id | Required | Unique among all invoices |
customer_id | Required | This is the “id” column in the “CUSTOMERS” file. |
ref | Required |
|
currency_iso | Required | 3 letters ISO code such as “USD”. |
amount | Required |
|
balance | Required |
|
date_invoice | Required | Format: YYYY-MM-DD |
date_due | Required | Format: YYYY-MM-DD |
Credit Notes
File name should be “credit_notes” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “credit_notes_2023-11-16T15:45:30.csv”. Any extra data that would be beneficial on Kolleno can be provided as additional columns in the CSV.
Column | Requirement | Information |
id | Required | Unique among all credit notes. |
customer_id | Required | This is the “id” column in the “CUSTOMERS” file. |
ref | Required |
|
currency_iso | Required | 3 letters ISO code such as “USD”. |
amount | Required |
|
balance | Required |
|
date_issued | Required | Format: YYYY-MM-DD |
Payments
File name should be “payments” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “payments_2023-11-16T15:45:30.csv”. Any extra data that would be beneficial on Kolleno can be provided as additional columns in the CSV.
Column | Requirement | Information |
id | Required | Unique among all credit notes. |
customer_id | Required | This is the “id” column in the “CUSTOMERS” file. |
ref | Required |
|
currency_iso | Required | 3 letters ISO code such as “USD”. |
amount | Required |
|
balance | Required |
|
date_paid | Required | Format: YYYY-MM-DD |
is_prepayment | Optional (value) | true/false |
is_overpayment | Optional (value) | true/false |
Credit Notes <> Invoices
File name should be “credit_notes_invoices” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “credit_notes_invoices_2023-11-16T15:45:30.csv”.
Column | Requirement | Information |
credit_note_id | Required | This is the “id” column in the “CREDIT NOTES” file. |
invoice_id | Required | This is the “id” column in the “INVOICES” file. |
amount_applied | Required |
|
Payments <> Invoices
File name should be “payments_invoices” followed by the date-time of upload in the format YYYY-MM-DDTHH:MM:SS separated by an underscore. Example: “payments_invoices_2023-11-16T15:45:30.csv”.
Column | Requirement | Information |
payment_id | Required | This is the “id” column in the “PAYMENTS” file. |
invoice_id | Required | This is the “id” column in the “INVOICES” file. |
amount_applied | Required |
|
SFTP Connection
Kolleno will provide the SFTP server. To facilitate access to the SFTP server, Kolleno requires an OpenSSH public key. Upon receipt, Kolleno will provide the credentials necessary (username and host) for connection, which can be used alongside the matching private key to access the SFTP server.
CSV data uploads can be uploaded to a folder within the SFTP server titled “csv_data_uploads”
Invoice PDFs can be uploaded to the "pdf" folder of the SFTP server with the naming convention “invoice_ref.pdf”. For example: “INV-123.pdf”.