Skip to main content
All CollectionsData and ReportingData Integrations
CSV Format for SFTP Data Integrations
CSV Format for SFTP Data Integrations
Ryley White avatar
Written by Ryley White
Updated yesterday

Please refer to this guide when sending data required for a Solink data integration.

This guide outlines how to correctly format the CSV file(s) to ensure our team can interpret and integrate your data as efficiently as possible.


General Data Format Guidelines

Please take note of the following guidelines when sending data for a Solink data integration:

Files should be separated as follows:

  • 1 file for headers

  • 1 file for items

  • 1 file for payments

  • Each of these files should contain data from one day at a time. Please do not send one file containing multiple days of data.

  • All stores/locations can be contained in one file.

Each group of files should not exceed 50 MB.

  • If a single file in a group exceeds 50MB, please break up the file into multiple files and label accordingly (for example, 20240213-header-2.csv, 20240213-item-2.csv, etc)

  • Transactions should not overlap between multiple groups - file groups should be split by location/store, not by lines/size (each row should represent one transaction at that location).

File names should be written in the following format:

  • <datestamp>-<storeNumber>-<fileType>.csv

    • <fileType> is one of 'header', ‘item’ or ‘payment’

    • <datestamp> is a unique date stamp without separators ('YYYYMMDD', ‘YYYYMMDDTHHmm’) depending on the file drop frequency (ie. 20240213-123-header.csv, 20240213-123-item.csv, etc)

    • If all stores are in the same file, then <storeNumber> can be omitted (ie. 202402133-header.csv)


Retail and Restaurant Format Guidelines

Refer to the examples and tables below for information on formatting CSV if your business operates in the retail or restaurant industries:

Examples of data files from the retail industry:

Examples of data files from the restaurant industry:

"Header" File Format

CustColumn Name

Required *

Purpose, examples

dataType

*

String: Allowed values ‘retail’ or ‘restaurant’

transactionId

*

String that uniquely identifies the transaction within the sender’s data warehouse.

storeId

*

String that uniquely identifies the store that generated the data.

registerId

*

String that uniquely identifies the pos register that generated the data within the store

registerName

Optional name of the register. Useful when the registerId is a guid

transactionDate

*

Business date of the transaction in the store’s local time zone. Preferred date format “YYYY-MM-DD' but may also be expressed in ISO-8601

“2024-06-15T00:00:00.000-06:00”

storeName

*

String that provides a unique human readable description of the store

startTime

*

ISO-8601 datetime string

“2024-06-15T06:15:00.000-06:00”

endTime

ISO-8601 datetime string

“2024-06-15T08:39:30.000-06:00”

status

*

Transaction level status

VOID | REFUND | SALE

employeeName

Optional string corresponding to the employee who generated the data or transaction.

employeeID

*

String representing the employee id in the system that generated the data or transaction.

transactionDiscount

Number: Total Transaction level discounts applied. These are separate from the item level discounts that may have been applied. For example, the POS calculates a veterans discount after all item level discounts have been applied. The discount will appear in this column.

transactionDiscountReason

String of '|' separated list of transaction discount reasons

transactionTax

Total tax applied to the transaction

customerId

Customer specific identifier

receiptNumber

POS receipt number. Often used to match the transaction to the receipt generated in store.

licensePlate

License plate number

notes

Notes associated with the transaction

tableNumber

RestaurantV1

String: Label associated with the table that generated the transaction data

guestCount

RestaurantV1

Number: The total number of guests that were served during the course of this transaction.

"Items" File Format

Column Name

Required *

Purpose, Examples, etc

transactionId

*

String that uniquely identifies the transaction within the sender’s data warehouse.

storeId

String that uniquely identifies the store that generated the data.

registerId

String that uniquely identifies the pos register that generated the data within the store

transactionDate

Business date of the transaction in the store’s local time zone. Preferred date format “YYYY-MM-DD' but may also be expressed in ISO-8601

“2024-06-15T00:00:00.000-06:00”

itemTime

ISO-8601 datetime string

“2024-06-15T06:15:09.000-06:00”

itemStatus

*

String: Allowed values ‘Void’ | ‘Return’ | ‘Sale’ | ‘Override’

itemStatusReason

Optional String: Descriptive comment as to the reason this status was assigned. “Customer no longer wanted the item”

quantity

*

Number: The quantity of the item purchased. May be integer or float

itemCode

String: The unique code that identifies the item in the POS service or data warehouse

itemDescription

*

String: Descriptive comment associated with the item. May consist of a ‘|' separate collection of modifiers. Ex. “Levis 501 | 34 w x 40 L | Stone washed | Boot cut” . The additional modifiers will appear below the line item entry in the receipt.

unitPrice

*

Number: Price associated with the item.

extendedPrice

Number: Extended price paid for the quantity of items listed.

itemTaxTotal

Optional Number: Tax associated with the item purchased.

itemDiscount

Number: Total discounts for the line item

e.g. 15.50

itemDiscountReason

'|' separated list of transaction discount reasons. May optionally include discount contribution.

“Coupon 2For1 ($5) | Outdoor living promo ($10) | “Student ($0.50)”

originalTransactionId

if the item is being returned, the original Transaction ID of the purchase

originalBusinessDate

if the item is being returned, the original Business Date of the purchase

originalStoreId

if the item is being returned, the original Store ID of the purchase was made

originalRegisterId

if the item is being returned, the original Register ID where the purchase was processed

"Payment" File Format

Column Name

Required *

Purpose, Examples, etc

transactionId

*

String that uniquely identifies the transaction within the sender’s data warehouse.

storeId

String that uniquely identifies the store that generated the data.

registerId

String that uniquely identifies the pos register that generated the data within the store

transactionDate

Business date of the transaction in the store’s local time zone. Preferred date format “YYYY-MM-DD' but may also be expressed in ISO-8601

“2024-06-15T00:00:00.000-06:00”

tenderType

*

String: Examples ‘Credit’ | ‘Debit’ | ‘Cash’ | ‘Cheque’| ‘Gift Card’ | ‘Loyalty’ | …

Should include card issuer information (Visa, MasterCard, Amex, etc.) wherever possible

totalPaymentTendered

*

Number: The total amount tendered of the specified tenderType

changeDue

*

Number: Required for Cash transactions where the totalPaymentTendered > total purchase amount

customerId

String: A redacted or otherwise PII compliant customer identifier

cardLastFour

String: The last four digits associated with card based payments.

paymentStatus

'|' separated payment status fields to qualify the payment tendered e.g. “Approved | Swiped | Auth Code 123456”

paymentTime

ISO-8601 datetime string

“2024-06-15T06:15:00.000-06:00”


Banking Industry Format Guidelines

Refer to the example and tables below for information on formatting CSV if your business operates in the retail or restaurant industries:

Banking Standard CSV File Definitions

Column Name

Required *

Purpose, examples

dataType

*

‘atm’ or ‘teller’

branchID

* in teller files

Branch unique ID

stationID

* in teller files

Teller station unique ID

employeeID

* in teller files

Employee unique ID

atmID

* in atm files

Atm unique ID.

sequenceNumber

*

Record unique ID.

transactionType

*

Text description of the action taken by the teller or the customer at the atm

e.g. ‘Deposit’, ‘Withdrawal’, ‘Inquiry’

transactionDate

*

ISO-8601 datetime string

“2024-06-15T06:15:00.000-06:00”

transactionAmount

*

Transaction amount

transactionStatus

*

accountNumberLast4

*

Last four digits customer account number

cardNumberLast4

*

Last four digits ATM card number

fromAccountLast4

Source account information

toAccountLast4

Destination account information

transactionTypeCode

Optional transaction type code

transactionStatusCode

Optional transaction status code

tellerName

The employee name

branchName

The branch name

city

Geographic city name

state

Geographic state/province name

address

Geographic street address


Note on ISO-8601 Timestamps

Proper synchronization of video and transactional data relies on a common understanding of the timestamps that are used in the data files. ISO-8601 was created to address the issues of timezones and quirks of the Daylight Standard and Daylight Savings strategies worldwide.

Time stamps are in UTC (sometimes incorrectly referred to as GMT) and represent a common understanding of time throughout the world. When written in UTC format, local timestamps are translated to their UTC equivalent and will be expressed in a “YYYY-MM-DDTHH:mm:ss.SSSZ” string format:

  • ‘YYYY’ represents the year

  • ‘MM’ represents the month (01 - 12)

  • 'DD' represents the day (01 - 28,29,30,31) depending on the month and leap year

  • 'T' represent the start of the daytime string

  • ‘HH’ represents the hour (00-23)

  • 'mm' represents the minutes (00-59)

  • ‘ss’ represents the seconds (00-59)

  • ‘SSS’ represents the milliseconds (000-999)

  • 'Z' represents the UTC format designation

ISO-8601 may also be specified as a local datetime with an explicit offset to UTC according to the time zone designation. In these cases, the offset may vary depending on the local observances of Daylight Saving Time. ISO-8601 timestamps replace the “Z” suffix with a “-/+hhmm”:

  • ‘-/+’ indicates if the offset should be subtracted or added to the local time

  • ‘hh’ represents the hour offset (00-12)

  • 'mm' represents the minutes (00-59)

Additional information re: ISO-8601 can be found here.

Did this answer your question?