Import format CSV

It is commonly used format for data transfers. Each record is in one or more rows and particular columns are separated by a separator.

You can set various parameters of the import file in the import definition, these will be described hereinafter.

File coding

The import file can be in whatever code page. In order the import works well you have to choose the code page correctly. Otherwise, there can be an error caused by the import.

File header

In some cases there is a header with names of particular columns in the first row. If your import file contains this header, check in the import definition field Includes column header.

Column count in the import file

One file line does not always represent one import record. ObjectGears system supports import of texts (e.g. comments), that contain character of the end of line. Such a text needs to be enclosed in quotation marks in order that the end of the text in next lines is recognized. However, if your text contains also quotation marks, you have to double them in the import file (see the following chapter).

In order this works well you have to define exact number of columns in the import file. If an incorrect number of columns is defined, there will be an error in the import course or the import will seem to finish well, but with erroneous data imported.

Mapping of quotation marks and separator

In some case separator, quotation mark or sign of the line end can be included in text data. In this case it is necessary to enclose the text in quotation marks and double possible quotation marks inside the text.

In general this has to performed in these cases:

1. if the first sign of the text is quotation mark
2. if there is separator sign in the text
3. if there is sign of the line end in the text

Following table shows, how the columns with quotation mark will be imported: 

Value in CSV file	Value stored in the database
"c	an error will be raised
"c"	c
"b""b"	b"b
b"b	b"b
d"	d"

Do not insert quotation marks everywhere, only for columns or texts, that can contain dangerous text. Quotation marks can be inserted to some texts only. It is not necessary to apply them to all the values in the column. This will not slow down the own import needlessly.

Column separator

In the default settings semicolon is used as column separator. However, you can use whatever character (e.g. comma, # etc.), that is not contained in your data.

Mapping of columns

Mapping of columns from the class, into which you import, to columns from the import file is performed in the import definition. Mapping is done by entering the column name into the field Import column at the appropriate class column. One column from the import file may be mapped to more columns from the class.

Order of the columns in the class and in the file does not matter. You can have more columns in the impotrt file, that will be ignored at the actual import. This enables to use one import file for more imports. E.g. you can import values into a referenced class by using import property Import unique records and then own data into another class that refers to the first class.

If there is no header with column names contained in the import file, then you cannot use the names in the fields Import column. In this case you can use colu,mn numbering, where the first column has number 1, second 2 etc. Also when column numbering is used, one column from the import file can be mapped to more columns in the class.