CSV import of general data

JustRelate CRM offers a special CSV format you can use to conveniently import up to 15,000 records. This format is adapted to Microsoft Excel’s CSV export format, so that post-processing tasks such as reformatting can be easily performed and system logic can be easily mapped.

The CSV format requires an empty first column (without data) in the CSV file (management column). The first column can contain one symbol per cell to specify the row type:

Comments are ignored during the import.

The second field in the row has the following format: FAT_DTO#CHD_DTO#REL_DTO FAT_DTO: The parent data object (e.g.: PSA _PRS)
CHD_DTO: The child data object (e.g.: PSA _ADR)
REL_DTO: The relation data object (e.g.: PSA_CON_ACT_CLI_AGG)

If a class name plus a method name is found there (psa.csv.PsaBasMap#mapDate), this class method is used for the mapping. The method must accept three parameters (DTO, field and data) and return a string.

It is not sufficient to specify just the class name in the mapping row. Instead, classname#methodname is expected in the row. The class name can specify a class either directly or in dynamic Java, e.g., de.pisa.cst.csv. CsvMap for a class, or $CST_CSV_MAP for dynamic Java.

Procedure

The mapping class needs to implement a method that has the following parameters and return type:

public String map(PscDto dto, Integer row, PscFld fld, String dat)

map serves as an example, here. The actual method name is specified in the mapping row of the CSV file. Accordingly, a class can have several mapping methods.

The previous abstract CSVmap class has received a new role. It is no longer the class defining the required interface, but offers a few service methods. To be able to use these methods, the mapping class must inherit from CSVmap.

1. public void wriMsg(String msg) writes text to the console and to the log file.
2. public String getErrPos() generates a string specifying the current position during the import.
3. public String getMapPar() returns a parameter that will be set in the CSV file.

The third point represents a small extension of the mapping definition in the CSV file. After the actual declaration of the method in the mapping row, you can specify, separated by a space character, text that can then be retrieved in the mapping method using getMapPar(). If you have a complex method for determining various values, and you want to specify the query conditions per column, you can use this option for it.

JustRelate CRM does not deliver any standard mapping functions!

Specifies for every column the respective data object and field to which the data is added.

Indicates the number of fields, starting at the second column (the index of column 2 is 1). This allows you to specify which data to import and thus to not import the data in columns beyond the given index (data not needed, or calculation columns, etc.).

Indicates the row in the CSV document where the import starts. Headers and commented-out rows count. All preceding records are omitted.

Is the absolute row (inclusive) where the import ends. All rows are considered. All data that appears after the determined final row is ignored and not imported. You influence the quantity of the imported records that way.

Advantage: S and L are row specifications that can be found on the left-hand side in Excel. Nothing needs to be sub­tracted. The numbering of the records also refers to the rows (1st record in the 8th row of a total of 100 records is counted as 8 of 100). Rows that are commented out in the defined row range are not counted as records, but the import definitely stops at the last specified row.

Specifies after how many records a message should be displayed on the monitor.

"n" opens no transaction boundary. All the other values use a boundary. The opening of the transac­tion boundary can be wanted and helpful, but all data is written back if an error occurs and the import must be carried out again. Check the quality of the data before the import to avoid this.

Specifies the absolute path including the name of a file to which all output is to be written. If no destina­tion path was given, the path of the CSV file to be imported is used (write permission must have been granted for this path).

Additional options for the import. Several options can be specified, separated by a comma. An exclamation mark (“!”) preceding the char­acter negates the statement.

C: Use relation cache.
E: Abort on errors
L: Observe locking
A: Check access
X: Activate userexits
D: Perform duplicate check
T: Open transaction
MI: Insert mode (cannot be negated)
MO: Overwrite mode (cannot be negated)
MU: Update mode (cannot be negated)

This allows you to automatically capture all records generated or changed by the CSV import in an import pack­age.

At least Data_object.Field_name must be specified (e.g. PSA _PRS.NAM).

The import starts at the second column. The first data object found is the data object of the main record. The columns containing further fields for the main data object follow. If the data object changes, the previous data object is saved. If further data objects follow the main data object, they are related to the main record. The relation is determined by the relation column (R).

Example 1:

This example creates an external person, Mr. Manfred Wachtel (composite name), and saves the record because a new data object starts (PSA_TSK) that creates a task for the person. This task is an activity and relates to the person through PSA_CON_AC_CLI_AGG, meaning that there is a relation between the contact and the activity.

Example 2:

This example creates a person whose last name is “Braunmüller” and first name is “Harry”. The record is saved, a new data object starts (a task). The task and additionally the relation record are created. The task relates to the person via PSA_CON_ACT_CLI_AGG.

DTO1.FLD1=DTO2.FLD2#FLD3#NUM

DTO1.FLD1 is the destination, DTO2.FLD2 is the field that is adopted, FLD3 is the field searched for in DTO2. If NUM is displayed in this column, it is the number of the following columns that also refer to the search in DTO2. If NUM is 0, NUM and the # character preceding it can be omitted.

Example:

This example creates a person whose name is “Braunmüller”. The CRE_PRS_GID field is the value resulting from the search for “Henry Miller” in PSA_CON. The PSC_GID field is placed into CRE_PRS_GID.

The search in other data objects is not limited to the main data object. Should the search find no or several records, the import will be canceled for this row and continue at the next row, unless a default value is used (see below). The field would then be set to this value.

The first character in a header cell can be a modifier ($ / ! / +).

$ means that the default value of a field should be used, if no data is present (cell is empty), or the search in a different data object has found no record.

Example:

The default value is used for OPR_IDN because the cell is empty. The contact XYZ will probably not be found, so the default value is adopted for CRE_PRS_GID.

This modifier means that this field is a key field that is included in the search. If no field is stated as a key field in a data object, no search takes place, but a new record is inserted. During the search, “!” can precede FLD3 as well as all other columns of the other data object. If no field of the other data object has a “!”, all fields are used for the search.

This modifier indicates that the field data to be imported, should be appended to the existing data of the defined field.

The CSV file for the BLOB import consists of only two columns and no headers. No regulations for the row type apply here. There is only the BLOB name and the path.

The first column contains the name of the BLOB, the second column the file name of the BLOB to import. If the BLOB name is already in use, the existing BLOB is replaced (update). Other­wise, a new BLOB is created (INSERT). BLOBs are primarily used in articles (technical descriptions with illustrations). The reference is created in the product. Therefore, you can find a BLOB template in the Excel template for articles.

• The full path must be provided.
• If no path is given, the system tries to import from the path of the CSV file.
• Avoid relative paths.

Example:

If contact records are created via a CSV import in the CRM, they are always linked to a process (lead, cus­tomer, prospective customer ...) with which, in turn, standard activities are connected. The automatic creation of standard activities during the CSV Import can be avoided by means of the PSA_CPY_ACT_OPR_STD environ­ment variable (y/n).