CSV guidelines for CSV integration [Internal]

This article is a guide on how clients should format CSV files before using the CSV integration

Updated over a week ago

Introduction

CSV is a raw text file format that is used to store a spreadsheet or database data.

A CSV file, with hourly data, where a semicolon is used to distinguish each column, and has decimal separator as comma, and period as thousand separator looks like this:

NB: All the values in the file must have the same structure in terms of decimal and thousand separators.

NB: We don’t accept null values. In case there is no data for a specific column, please send 0.

Data fields supported in the CSV file

Our recommendation would be to include the following supported fields in the CSV file.

  • Department Id

  • Register Id

  • Date

  • Hour

  • Net Sales

  • Gross Sales

  • Discounts

  • Returns

  • Transactions

  • Number of goods sold

  • Number of returned goods

  • Visitors

The imported files data format requirements

When importing data via CSV files the formating requirements is as follows:

  • Department Id: Department/shop identification number – this column is the key-pairing value column which should be in the alphanumeric format and have a unique value in SameSystem. 2 departments cannot have the same number, even if one is inactive.

  • Register Id: Cash register identification number should be in numeric format.

NB: The Register ID data should be only delivered if there are more cash registers per shop, or if you desire to have an overview per cash register in SameSystem. Cash registers can take these values:1,2,3 etc.

  • Date: The default date format is -YYYY-MM-DD-however, you can choose from the date formats below.

  • Hour: hh:mm:ss (10 o'clock should be delivered as 10:00:00 and it will be interpreted as the totals between 10:00:00-10:59:59).

  • Net Sales : Total or summarized value of sales including or excluding VAT per shop (cash register); numeric format.

  • Gross Sales: Total or summarized value of sales including or excluding VAT per shop (cash register); numeric format.

NB: We expect the hourly sales data to be in the shop’s local time and include DST (Daylight Savings Time).

  • Discounts: Total or summarized data column of the amount in the currency of the discounts that were applied to the products (should have the minus sign in front the number; -300 for example).

  • Returns: Total or summary column of the amount in the currency of the returns (should have the minus sign in front the number; -200 for example)

  • Transactions: Total or summarized data column with the number of customers the shop (cash register) had in a timeframe; numeric format.

  • Number of goods sold: Total or summarized data column with the number of products the shop (cash register) sold; numeric format. It usually includes returned items, and if returned items are higher than the sold items; this number can be negative.

  • Returned items: Total or summary field of the numbers of products that were returned (should have the minus sign in front of the number; -1 for example)

  • Visitors: Total or summary of the number of visitors a shop has in each time frame

Please read the rules of how data should be formatted in a CSV file that we would expect to receive from you. We strictly encourage you to follow this format, but not necessarily the naming of the columns or their order: Each record (row of data) should be located on a separate line, delimited by a line break. If there are no

values for certain columns, put zero. Our importing tool does not process null values or empty columns.

NB: If data is provided per cash register, SameSystem we'll calculate the totals per shop by summing the data for each cash register. Cash registers would have to have 1,2,3 as register ids. There isn't a possibility to map the external cash register id with the internal one.

Accepted data separators and date formats

Column separator:

  • ; [semicolon]

  • , [comma]

Decimal separator:

  • . [period]

  • , [comma]

Thousand separator:

  • [without any separator]

  • . [period]

  • , [comma]

Date format:

  • YYYY-MM-DD

  • YYYYMMDD

  • DD-MM-YYYY

  • DD.MM.YYYY

  • DD/MM/YYYY

  • MM-DD-YYYY

  • YY-MM-DD

  • YYMMDD

  • DD-MM-YY

  • DD.MM.YY

  • DD/MM/YY

  • MM-DD-YY

NB: When using a comma as a separator for decimals or thousand, use a semicolon to separate columns. The same goes for the thousand and decimal separator, they can't be identical.

File encoding

The character encoding in the CSV file should be UTF-8(Unicode). We only accept simple UTF-8 encoding is accepted, and no other variations like UTF-8 BOM. The document that uses such encoding can be used for English and many other languages, including support for Asian characters.

Did this answer your question?