Skip to main content

Data for Kolleno CSV Connector via SFTP

This document outlines CSV formats for SFTP data transfer to Kolleno, ensuring AR data completeness.

Kolleno Support avatar
Written by Kolleno Support
Updated over a week ago

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:

  1. Required: you must send the field and a value.

  2. Optional (value): you must send the field but the value is optional.

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

Did this answer your question?