Import of paper and price information

The basis of every calculation and well functioning business is correct information, especially regarding consumables such as paper. As there are now more paper types than cheese in France, a manual import of this data is out of the question. That's why we've designed a file format that contains paper information and prices that can be imported into Keyline.

File format

For an automatic import Keyline expects a CSV file with a structure as described below.

The data exchange through the import of files is an emergency solution and does not correspond to the technical state of the 21st century. In the medium term, the data import should be done through our REST interfaces. If you are interested in our API as a paper supplier or manufacturer, please contact us.

In addition, the following requirements apply to the format of the CSV file:

  • The file must be saved with charset UTF-8.
  • The file must contain a header line. However, the exact column names are not specified by Keyline.
  • The column separators must be semicolon (;).
  • If the type of Decimal specified for a column in the description below, the values of this column must use a period (.) as the decimal separator. Commas are not allowed in these columns as decimal separator nor as thousands separator.
  • If the type of Integer specified for a column in the description below, the values of this column must not contain any decimal separators or thousands separators, but must contain only numbers.
  • If a column is declared optional in the description below, this means that this column may contain empty values. However, columns marked as optional must always be included in the file to be imported.
    This means that a file to be imported must always and in any case contain at least 17 columns.

Download example CSV file

Structure of the table

Each row of the table is divided into two parts:

  • Columns containing paper information (columns 1 to 14)
  • Columns containing order information (columns 15 and onward)

If there are several price scales for a paper, then these can be indicated in two ways in the CSV file:

  1. By specifying columns 16-23 (sales quantity / SKU, price per sales quantity, pre-cut, unit of sales quantity, scale quantity, unit of scale quantity, ream opening allowed, jump quantity) several times.
    OR
  2. By creating several rows for the same paper, which do not differ in your entries in columns 1-15, but the different prices and sales quantities are defined in the different values in columns 16-23.

Example

If, for example, different prices exist for a paper from an order quantity of 500 sheets (scale quantity: 500, unit of scale quantity: 'sheet') and from an order quantity of 1000 sheets (scale quantity: 1000, unit of scale quantity: 'sheet'), the price scales can be mapped in the following two ways in the CSV file.
Both display modes are equivalent and transport the same information:

All price scales in one row

for the scale quantity 500 sheets, the information is given in columns 16-23, and the information for the scale quantity 1000 sheets and more is specified in columns 24-31.

Row 1
Column 16
Row 1
Column 17
Row 1
Column 18
Row 1
Column 19
Row 1
Column 20
Row 1
Column 21
Row 1
Column 22
Row 1
Column 23
Sales quantity / SKU Price of sales quantity Pre-cut Unit of sales quantity Scale quantity Unit of scale quantity Ream opening allowed Jump quantity
250 37.4 n sheet 500 sheet n 250
Row 1
Column 24
Row 1
Column 25
Row 1
Column 26
Row 1
Column 27
Row 1
Column 28
Row 1
Column 29
Row 1
Column 30
Row 1
Column 31
Sales quantity / SKU Price of sales quantity Pre-cut Unit of sales quantity Scale quantity Unit of scale quantity Ream opening allowed Jump quantity
250 33.4 n sheet 1000 sheet n 250

OR

One row per price scale

Two lines are created for the paper in the CSV file. Columns 1 to 15 are filled with the identical paper data for both lines, while columns 16 to 23 (sales quantity / SKU, price per sales quantity, pre-cut, unit of sales quantity, scale quantity, unit of scale quantity, ream opening allowed, jump quantity) are entered differently for each sales quantity.

Row 1
Column 16
Row 1
Column 17
Row 1
Column 18
Row 1
Column 19
Row 1
Column 20
Row 1
Column 21
Row 1
Column 22
Row 1
Column 23
Sales quantity / SKU Price of sales quantity Pre-cut Unit of sales quantity Scale quantity Unit of scale quantity Ream opening allowed Jump quantity
250 37.4 n sheet 500 sheet n 250
Row 2
Column 16
Row 2
Column 17
Row 2
Column 18
Row 2
Column 19
Row 2
Column 20
Row 2
Column 21
Row 2
Column 22
Row 2
Column 23
Sales quantity / SKU Price of sales quantity Pre-cut Unit of sales quantity Scale quantity Unit of scale quantity Ream opening allowed Jump quantity
250 33.4 n sheet 1000 sheet n 250

File format

As already mentioned, Keyline processes only CSV files (comma-separated values).

Information on order quantities

The central unit, when it is decided in Keyline which paper to order and how, is the sales quantity / SKU. From this, it is primarily derived in which quantities a paper can be ordered.
If, for example, a paper can only be ordered with 500 sheets per ream, the value 500 should be entered in the column sales quantity / SKU. The price per ream must then be entered in the column price of sales quantity.

If a system can only output the prices per 1000 sheets, it is still possible to specify the exact order quantities in the CSV file. In the column sales quantity / SKU the value 1000 is deposited and in the column price per sales quantity according to the price per 1000 sheets.
The minimum quantity for which the price specified under price per sales quantity applies must then be specified in the scale quantity column. This is effectively the minimum order quantity. The valid order intervals can then be specified in the jump quantity column.

If, for example, a price only applies from an order quantity of 2000 sheets, but can be ordered from this quantity in steps of 250 sheets, the value 2000 must be entered as the scale quantity and the value 250 as the jump quantity.
In this way, the price below the price per sales quantity can always be specified as the price per 1000 sheets. The valid minimum quantities and order quantities are defined as scales with jump quantities.

Extended Example

Here you will find an extended example of the possibilities offered by complex price scales and order quantities in Keyline CSV format.

Semantic correctness

Keyline needs a solid database as the foundation of automation. This data base must be semantic, meaning that it must be technically possible which data has which significance. That's why we need all the information clean and separate. Sizes and weights, for example, must be in separate columns, we can not extract information contained in the paper name and are a hindrance, as this filtering on pure paper name is impossible.

Column definitions

Column index
1 Substrate form Text Form of the printable substrate Allowed values: "sheet", "roll", "envelope", "piece"
2 Substrate type Text Type of the printable substrate Allowed values: "paper", "carton", "plastic", "foil", "metal", "wood", "cloth"
3 Name Text Name of the printable substrate Example: "ArtMulti Silk"
4 Category Text The product category of the substrate Beispiele: "Photographic paper", "High gloss", "Matte"
5 Width in mm Integer [mm] The width of the substrate, with changes of the direction of rotation do not replace (!) with the substrate's height Example: 1000
6 Width in mm Integer [mm] The height of the substrate, with changes of the direction of rotation do not replace (!) with the substrate's width Example: 700
7 Thickness in µm Integer [µm] Thickness of the substrate in micrometer Example: 150
8 Grammage in g/m² Integer [g/m²] Grammage of the substrate specified as grams per square meter Example: 150
9 Grain Text Grain of the substrate Allowed values: "short", "long"
10 Surface color Text Color of the substrates surface Example: "white", "light grey"
11 Surface finish Text The type of surface finish of the substrate Example: "matte", "painted", "glossy", "elephant skin"
12 Environmental certificatin Text The type of environmental certification of the substrate Allowed values: "FSC 30%", "FSC 50%", "FSC 70%", "FSC 80%", "FSC 100%", "FSC Mix Credit", "FSC Mix 30%", "FSC Mix 50%", "FSC Mix 70%", "FSC Mix 80%", "FSC Recycled Credit", "FSC Recycled 30%", "FSC Recycled 50%", "FSC Recycled 70%", "FSC Recycled 80%", "FSC Recycled 100%", "PEFC 70%", "PEFC 100%" or empty
13 Color saturation Decimal [0..1] The color saturation as a percentage between 0 and 1 Example: 0.24 (for 24%)
14 Coated Boolean Yes, if the paper is coated Allowed values: y, n, 0, 1, coated, uncoated
15 Order number Text The order number of the substrate / sales unit Example: "ABD938832"
16 Sales quantity / SKU Integer The sales quantity to which the price of this line refers Example: 1, 1000
17 Price per sales quantity Decimal The price of the sales amount of the preceding column in the currency of the printing company Example: 0.3232, 1932.21
18 Precut Boolean Yes, if the paper is pre-cut before delivery, otherwise no Allowed values: y, n, 0, 1
19 Unit of sales quantity Text Specifies the unit, the sales quantity. If this column does not contain an entry, it results implicitly from the entry in the Substrate Form column as follows:
Value in column Substrate Form Unit of sales quantity
sheet Sheet
roll Meter
envelope Piece
piece Piece
Allowed values: "sheet", "m", "sqm", "piece", "kg"
20 Scale Quantity Integer Specifies the minimum order quantity for which the price specified under price per sales quantity applies.
For example, for a scale quantity of 100, at least 100 of the unit of the scale quantity must be ordered for the price to apply.
Example: 10, 500, 10499
21 Unit of scale quantity Text Specifies the unit in which the scale quantity is specified. For example, it is possible to specify sheet as the unit of the scale quantity, while the price is specified in a weight unit. If this column is empty, the implicit unit of the sales quantity is assumed to be this value (see specifications for column unit of sales quantity).
column for these scales. If this is not the case, this means that the paper is delivered and packaged in reams.
This also means that if a paper is available both reamed and unreamed, at least two scales must be specified for it.
Allowed values: "sheet", "m", "sqm", "piece", "kg", "sheet_noream", "kg_noream", "sqm_noream"
22 Ream opening allowed Boolean If this column is set to , y or 1, this means that less than one ream (i.e. less than one packaging unit) of this paper can be ordered. Allowed values: j, y, n, 0, 1
23 Jump quantity Integer Specifies the interval between two valid purchase order quantities. If the jump quantity is 1000, for example, the paper can only be ordered in quantities of 1000, but not in quantities of 1500.
If no jump quantity is specified, it is implicitly identical to the value specified in the sales quantity column. Unless the value in the unit of sales quantity column specifies a unit that allows unreamed order quantities, that is, order quantities in steps of 1.
However, valid order quantities only result from the combination of scale quantity and jump quantity. The scale quantity specifies the minimum order quantity. With the scale quantity and the jump quantity, the next ordering intervals result from addition (scale quantity + (n * jump quantity) for n as an integer).
The unit of the jump quantity is always identical to the unit of the scale quantity.
Example: 10, 500, 10499

As described already under structure of the table columns 16-23 can be repeated multiple times in order to specify multiple sales units or differential pricing.

Allowed values for the column environmental certification:
FSC 30% FSC 50% FSC 70% FSC 80%
FSC 100% FSC Mix Credit FSC Mix 50% FSC Mix 70%
FSC Mix 80% FSC Recycled Credit FSC Recycled 50% FSC Recycled 70%
FSC Recycled 80% FSC Recycled 95% FSC Recycled 100% PEFC 70%
PEFC 75% PEFC 80% PEFC 94% PEFC 100%

Version 3 // 15. October 2018