Files format release 01.26

Review of 01/Oct/2005

 

Summary

 

Summary............................................................................................................................................ 1

1.     Introduction................................................................................................................................ 2

1.1 Conventions used in the ISS export files.......................................................................... 2

1.2 Description of the kinds of data used in the fields........................................................... 3

2. Company file................................................................................................................................. 4

3. Product file..................................................................................................................................... 5

4. Specification file........................................................................................................................... 6

5. Formula file................................................................................................................................... 8

6. Codified fields............................................................................................................................... 9

7. Files denomination and their sending................................................................................... 16

9. Examples..................................................................................................................................... 17

10. Notes.......................................................................................................................................... 18

 

1.    Introduction

 

This document contains the instructions for the creation of an export file in ASCII COMMA DELIMITED format  in alternative to the use of ISS Formula software provided by the Istituto Superiore di Sanità.

 

1.1 Conventions used in the ISS export file

 

o       You have to use the point (.) as decimal separator. The separator for thousands is not permitted.

o       The sign, if present, must precede the number immediately. The positive sign is optional, the negative one is compulsory.

o       If a numerical field begins with one or several zeros, these are ignored in the reading phase of the field.

 

Examples:

Correct:

0001.230 

+0001.230 

-0001.230

Wrong:

1,23 

1,000.23 

1.23-

 

 

 

 

 

1.2 Description of the data types used in the fields

 

Type

Description

Boolean

A string containing “1” for TRUE and  “0” for FALSE

Date

A string of ten characters in format (DD/MM/YYYY). Its VERY important that the date is in this format otherwise it will be rejected by the database.

String

A sequence of MS Windows ANSI characters (Code Page 1252).  The  double speech marks (“) have to be converted in to the sequence \” or in a different character; The product and customer code must exclusively be alphanumeric.

Long

An integer long number, included in the range

-2,147,483,648...+2,147,483,647

Integer

An integer short number, included in the range

-32,768...+32,767

Single

3.4x10-38…3.4x10+38

Double

A decimal number with 15 precision figures, included in the range

1.7 x 10-308... 1.7 x 10+308

 

ATTENTION:  The field names in boldface show the compulsory fields; these fields cannot be  left blank. The fields indicated by an asterisk *  are the primary keys of the table.

The possible contents of the coded fields are listed in a table at the end of this document.

 

ATTENTION: When creating preparations/substances the use of the prefix “ISS” for codes is not permitted. This prefix is reserved for official substances only. If you create preparations/substances using this prefix in the codes, the preparations/substances will be rejected by the database during the load procedure.

 

2. Company file

General information about the company. Only one company record must be included.

 

#

Field

Description

Type

Lent

0

CODE*

Company code

String

16

1

NAME

Company name

String

100

2

ADDRESS

Address

String

50

3

TOWN

Town

String

30

4

POSTAL_CODE

Postal code

String

10

5

PROVINCE

Region/County

String

30

6

COUNTRY

Country

String

20

7

PERSON

Person to contact

String

30

8

PHONE

Phone/fax

String

40

9

EMAIL

E-mail address

String

50

10

LANGUAGE

Preferred language for messages received via email. 1 for English, 0 for Italian.

Boolean

1

 

NB: If the LANGUAGE field is not present in the file then the language defaults to Italian.

3. Products file

 

General details of the declared preparations and of the substances contained in the above-mentioned preparations.

The use of preparation codes or substance codes beginning with the prefix ISS is not allowed.

 

 

Field

Description

Type

Lent

0
CODE*

Company code

String

16

1

PCODE*

Preparation/ substance code

String

20

2
NAME

Preparation/substance name

String

255

3

TYPE

Preparation or substance ?                              *** CODED FIELD ***

String

2

4
CAS

CAS Number (free format).

Compulsory in case of substances;

use ND if the CAS number is not available.

String

20

5

EC[1]

EC number in 999-999-9 format

String

20

6

INDEX

Index number in  999-999-99-9 format

String

20

7

SUPP_COMPANY_CODE

Supplier company code

(compulsory just in case of purchase from supplier)

String

16

8
SUPP_PCODE

Product code used by supplier

(compulsory just in case of purchase from supplier)

String

20

9
SUPP_NAME

Supplier name

(compulsory just in case of purchase from supplier)

String

100

10
DANGER

1 if dangerous, 0 if not dangerous

Boolean

1

11
DETERGENT[2]

1 if it is a detergent, 0 if not a detergent. If this field is empty, it is assumed as not a detergent.

Boolean

1

12
INCI

INCI name (applies only to substances)

String

200

13
IUPAC

IUPAC name (applies only to substances)

String

200

14
FARMACOPEA

FARMACOPEA name (applies only to substances)

String

200

 

 

4. Specifications file

 

Preparations declaration. For every specification record a corresponding product record with the same key (CODE, PCODE) must exist.

 

#

Field

Description

Type

Lent

0

CODE*

Company code

String

16

1

PCODE *

Preparation code

String

20

2

SYNONYM1

Other possible commercial name

String

200

3

SYNONYM2

Other possible commercial name

String

200

4

SYNONYM3

Other possible commercial name

String

200

5

MFG_TYPE

 Producer, importer or distributor ?                   

   ***CODED FIELD  ***

String

2

6

PHISYCAL

Physical state  *** CODED FIELD  ***

String

2

7

ODOR

Odor

String

200

8

COLOR

Colour

String

200

9

PH

PH

Single

5

10

CONCENTRATION

Concentration

Single

6

11

PH_USAGE

PH in use

Single

5

12

PACKAGE

Package

String

200

13

DATE

Compilation date (DD/MM/YYYY)

Date

10

14

COMPANY_LABEL

Manufacturer name (communitary, extracommunitary + product original name)

String

255

15
ON_SALE

1 if on sale in the market place,  0 if not

Boolean

1

16

TYPE (1)

Preparation type      *** CODED FIELD  ***

String

255

17

USAGE (1)

Usage / Intended use  *** CODED FIELD  ***

String

20

18

FATHER (2)

Reference code to another preparation

String

20

19

OTHER_TYPE

Preparation type if not included in the TYPE field list codes

String

50

20

OTHER_PHISYCAL

Physical state if not included in the PHISYCAL field list codes

String

20

21

SOLD_OUT_DATE

Last date of product manufacturing (DD/MM/YYYY)

Date

10

22

RELEASE_TO_MARKET_DATE

Date product is due for release onto the market place

(DD/MM/YYYY)

Date

10

23

HAZARD

European hazard labeling *** CODED FIELD ***

String

255

 

(1) The TYPE, USAGE, and HAZARD fields of the SPECIFICATIONS table can contain more than one value in a string. They have to be separated by character “ ; “ (semicolon) . Also, the same separation character must be used at the beginning and at the end of the string.

Examples:

                   “;DO;”

                   “;DO;IN;”

                   “;H03;A01;G04;”

                   “;Xn;R65;”

 

(2) It's possible to make a preparation declaration by reference to another declaration  using the FATHER field. In case the preparation has the same characteristics of another preparation already present in the database, then the Father field could contain the code of the latter, so it avoids having to reinsert the same data. In this case the only fields to fill in are:

  

                   In the specifications table

                                      FATHER

                                      CODE       ( company code)

                                      PCODE     ( preparation code )

                                      DATE        ( compilation date )

                                      SOLD_OUT_DATE ( optional )

 

                   In the products table

                                      CODE       ( company code )

                                      PCODE     ( preparation code )

                                      NAME       ( product name )

                   In the formula table

                                      No record

 

 

5. Formula file

 

Composition of the declared preparations. Every record represents a component of a formula. All the components of a formula must be moved together, ordered by the preparation PCODE code.

For every CODE and PCODE value in the  formula record, a corresponding  product and specification record with the same key (CODE, PCODE) must exist.

For every  CODE and COMP_CODE value in the  formula record a corresponding product record with the same key (CODE, COMP_CODE) must exist.

 

#

Field

Description

Type

Len

1

CODE*

Company code

String

16

2

PCODE*

Preparation code

String

20

3
COUNTER*

Sequential number of the components

Integer

6

4
COMP_CODE

Component code

String

20

5

QTY_RANGE(1)

Percentage range*** CODED FIELD ***

Integer

1

6

QTY(1)

Exact percentage

Double

6

 

The QTY_RANGE and QTY fields of the formula table must not be filled in together. If QTY_RANGE contains a value, then QTY must be left empty, and vice versa.

 

Detergents and other products must be declared using different ranges: see the QTY_RANGE coded fields for more information.

 

6. Coded fields

 

Field

Code (rule)

Description

Formula table (generic)

 

 

QTY_RANGE

0

0%-1%

QTY_RANGE

1

1% - 5%

QTY_RANGE

2

5% - 10%

QTY_RANGE

3

10% - 20%

QTY_RANGE

4

20% - 30%

QTY_RANGE

5

30% - 50%

QTY_RANGE

6

50% - 75%

QTY_RANGE

7

75% - 100%

Formula table (detergents)

 

 

QTY_RANGE

A

0%-0.1%

QTY_RANGE

B

0.1%-1%

QTY_RANGE

C

1%-5%

QTY_RANGE

D

5%-10%

QTY_RANGE

E

10%-20%

QTY_RANGE

F

20%-30%

QTY_RANGE

G

30%-50%

QTY_RANGE

H

50%-75%

QTY_RANGE

I

75%-100%

Product table

 

 

TYPE

PR

Preparation

TYPE

SU

Substance

Specification table

 

 

MFG_TYPE

DI

Distributor

MFG_TYPE

IM

Importer

MFG_TYPE

PR

Manufacturer

PHYSICAL

AE

Aerosol

PHYSICAL

GA

Gas

PHYSICAL

LI

Liquid

PHYSICAL

SO

Solid

TYPE

A01

Chemical

TYPE

A02

Pharmaceutical

TYPE

A03

Leather / Skin

TYPE

A04

Metallurgy

TYPE

A05

Electrotechnic/electronic

TYPE

A06

Food

TYPE

A07

Cosmeti

TYPE

A08

Paper manufacturing

TYPE

A09

Textile

TYPE

A10

Metal and mechanic

TYPE

A11

Galvanic

TYPE

A12

Ceramic

TYPE

A13

Plastic

TYPE

A14

Diagnostic in vitro

TYPE

A15

Lubricant

TYPE

B00

Cleaning/maintenance agent

TYPE

B01

Product for shoes/leathers

TYPE

B02

Cleaner for glasses and mirrors

TYPE

B03

Cleaner for WC and sanitary

TYPE

B04

Oven cleaner

TYPE

B05

Stain remover for fabrics

TYPE

B06

Dressing size

TYPE

B07

Cleaner for cars

TYPE

B08

Cleaner for metal

TYPE

B09

Pavement wax

TYPE

B10

Cleaner for furniture

TYPE

B11

Household deodorant

TYPE

B12

Pipe unblocker

TYPE

B13

Domestic dye

TYPE

B14

Cleaner for carpets/textiles

TYPE

B15

For house hold plants

TYPE

C00

Fertilizer

TYPE

C01

Mineral fertilize

TYPE

C02

Specialized

TYPE

C03

Organic-mineral and organic

TYPE

D00

Adhesive and sealant

TYPE

D01

For footwear

TYPE

D02

For furniture and wood

TYPE

D03

For transport means

TYPE

D04

For paper and package

TYPE

D05

For sealants and building industry

TYPE

E00

For paints and varnishes

TYPE

E01

For car bodies

TYPE

E02

For wood furniture

TYPE

E03

For marine / sea

TYPE

E04

For road signals/signs

TYPE

E05

Paint/varnish remover

TYPE

E06

For industry/metal/plastic

TYPE

E07

Anticorrosion

TYPE

E08

For building industry

TYPE

E09

Diluent, solvent

TYPE

F00

Detergent

TYPE

F01

Hand detergent for laundry

TYPE

F02

Detergent for hand washing crockery

TYPE

F03

Delicate laundry detergent (both hand or by machine)

TYPE

F04

Abrasive

TYPE

F05

Laundry softener

TYPE

F06

Bleach for laundry

TYPE

F07

Detergent for laundry in machine

TYPE

F08

Detergent for dishwasher

TYPE

F09

Detergent for hard  surfaces

TYPE

F10

Anticalcareous/softener for water

TYPE

F12

Gloss, buffing agent

TYPE

F13

Soap for clothes

TYPE

F14

Soft clothes hand washing detergent

TYPE

F15

Soft clothes laundry machine detergent

TYPE

F16

Detergent for glasses

TYPE

F17

Detergent for floors

TYPE

F18

Oven cleaning agent

TYPE

F19

Multiuse detergent

TYPE

F20

Dry cleaning agent

TYPE

F21

Wet stain remover

TYPE

F22

Dry stain remover (spotting agent)

TYPE

F23

Pretreatment of stain removing

TYPE

F24

Deterging wax

TYPE

F25

Wax removing product

TYPE

F26

Varnish remover (paint stripper)

TYPE

F27

Pickling agent

TYPE

F28

Metal cleaning agent

TYPE

G00

Disinfectant

TYPE

G01

General environment use

TYPE

G02

Detergent not for personal use

TYPE

G03

Specific not personal use

TYPE

G04

For small wounds

TYPE

H00

Ink

TYPE

H01

Offset

TYPE

H02

Rotogravure product

TYPE

H03

UV

TYPE

H04

Tool for printing

TYPE

H05

Roto-flexographic

TYPE

H06

Black ink for newspapers

TYPE

H07

Silk-screen process

TYPE

H08

Diluent and solvent

TYPE

I00

Biocide

TYPE

I01

Human hygiene biocidal product

TYPE

I02

Private area and public health area disinfectants and other biocidal product

TYPE

I03

Veterinary hygiene biocidal product

TYPE

I04

Food and feed area disinfectant

TYPE

I05

Drinking water Disinfectant

TYPE

I06

In-can-preservative

TYPE

I07

Film preservative

TYPE

I08

Wood preservative

TYPE

I09

Fibre, leather, rubber and polymerised materials preservative

TYPE

I10

Masonry biocide

TYPE

I11

Preservative for liquid-cooling and processing systems

TYPE

I12

Slimicide

TYPE

I13

Metalworking fluid preservative

TYPE

I14

Rodenticide

TYPE

I15

Avicide

TYPE

I16

Molluscicide

TYPE

I17

Piscicide

TYPE

I18

Insecticide, acaricide, and product to control other arthropods

TYPE

I19

Repellent and attractant

TYPE

I20

Preservative for food or feedstocks

TYPE

I21

Antifouling product

TYPE

I22

Embalming and taxidermist fluid

TYPE

I23

Control of other vertebrates

USAGE

DO

Domestic

USAGE

IN

Professional

Main hazard categories

 

(use in field HAZARD of the specification file)

HAZARD.CATEGORY

A0

Not classified

HAZARD.CATEGORY

C

Corrosive

HAZARD.CATEGORY

CANC CAT 1

Carcinogenic category 1

HAZARD.CATEGORY

CANC CAT 2

Carcinogenic category 2

HAZARD.CATEGORY

CANC CAT 3

Carcinogenic category 3

HAZARD.CATEGORY

E

Explosive

HAZARD.CATEGORY

F

Highly flammable

HAZARD.CATEGORY

F+

Extremely flammable

HAZARD.CATEGORY

MUTA CAT 1

Mutagenic category 1

HAZARD.CATEGORY

MUTA CAT 2

Mutagenic category 2

HAZARD.CATEGORY

MUTA CAT 3

Mutagenic category 3

HAZARD.CATEGORY

N

Dangerous for the environment

HAZARD.CATEGORY

O

Oxidising

HAZARD.CATEGORY

R10

Flammable

HAZARD.CATEGORY

RIPR CAT 1

Toxic for reproduction category 1

HAZARD.CATEGORY

RIPR CAT 2

Toxic for reproduction category 2

HAZARD.CATEGORY

RIPR CAT 3

Toxic for reproduction category 3

HAZARD.CATEGORY

SENS

Sensitising

HAZARD.CATEGORY

T

Toxic

HAZARD.CATEGORY

T+

Very toxic

HAZARD.CATEGORY

Xi

Irritant

HAZARD.CATEGORY

Xn

Harmful

 

 

Risk phrase codes (use in field HAZARD of the specification file)

R1

R2

R3

R4

R5

R6

R7

R8

R9

R11

R12

R13

R14

R15

R15/29

R16

R17

R18

R19

R20

R20/21

R20/21/22

R20/22

R21

R21/22

R22

R23

R23/24

R23/24/25

R24

R24/25

R25

R26

R26/27

R26/27/28

R26/28

R27

R27/28

R28

R29

R30

R31

R32

R33

R34

R35

R36

R36/37

R36/37/38

R36/38

R37

R37/38

R38

R39

R39/23

R39/23/24

R39/24

R39/24/25

R39/25

R39/26

R39/26/27

R39/26/27/28

R39/26/28

R39/27

R39/27/28

R39/28

R40

R41

R42

R42/43

R43

R44

R45

R46

R47

R48

R48/20

R48/20/21

R48/20/21/22

R48/20/22

R48/21

R48/21/22

R48/22

R48/23

R48/23/24

R48/23/24/25

R48/23/25

R48/24

R48/24/25

R48/25

R49

R50

R50/53

R51

R51/53

R52

R52/53

R54

R55

R56

R57

R58

R59

R60

R61

R62

R63

R64

R65

R66

R67

R68

R68/20

R68/20/21

R68/20/21/22

R68/20/22

R68/21

R68/21/22

R68/22

 

7. File names and their sending

The four files to transmit have to be sent to ISS with a precise name and have to be transmitted as a single .ZIP file.

Every file name is composed by a letter depending on the file type, a sequential 7 figures number and the .CSV extension (X9999999.CSV). For every file sent, a sequential number must be used, starting from 0000001:

 

Company File :                  C9999999.CSV

Product File:                      P9999999.CSV

Specification File:             S9999999.CSV

Formula File:                     F9999999.CSV

 

The  .ZIP file will have to use the X letter and the same sequential number (X9999999.ZIP).

 

Example:

 

The first file sent will be named X0000001.ZIP, and will contain the following files:

C0000001.CSV

F0000001.CSV

P0000001.CSV

S0000001.CSV

 

The second file sent will be named X0000002.ZIP, and will contain the following files:

C0000002.CSV

F0000002.CSV

P0000002.CSV

S0000002.CSV

 

So, every time a zip file is sent, the number of the zip file (and the .CSV files contained within) must be incremented by 1.

 

First record of every file

 

The first record of every file must contain a header in the TYPE+VERSION format, where TYPE is a string specifying which table is present in the file and VERSION is a string containing an integer of 4 numbers specifying the database format version (at present  0101).

The possible TYPE values are:

 

Company File :          COMPANY

Product File :              PRODUCT

Specification File :     SPECIFICATION

Formula File :             FORMULA

 

Example of C9999999

.CSV company file:

 

“COMPANY”,”0101”

"UK20007355588","Gamma Delta LLC","Ness Park ","Bury","WA23 F1E","Lancashire","United Kingdom","Kevin Smith","02 1111",””

 

This shows that the file contains the COMPANY table with 01.01 format version database.

 

 

9. Examples

 

Examples of files in the correct format for sending can be produced through the ISS Formula program.

 

10. Remarks

 

The information given in this document reflects carefully the moment of its drafting. ISS reserves the right to add new tables , new fields in the future or to modify the meaning of  the existing ones to improve the characteristics and the functions of ISS Formula software. This document replaces any previous release.

 

 


[1] This field was once called EINECS. It maintains the data and meaning of previous versions of this format.

[2] Detergent was introduced in specification 1.26. If set to 1 means that the product has been exported conforming to the EC detergents regulation.\