CSV file requirements

Attention

Attention: Please never open the CSV files prepared with Excel for making adjustments with Excel. For example, Excel gives line breaks more weight than quotes and brackets, and uses various import filters that have undesired effects. Unfortunately, Excel cannot read its own CSV format, and a template, that can barely be used, would be the result. Always save the template for the CSV file to make changes and corrections in it, and to export it once more as a CSV file.

Also, please avoid copying text files containing data via drag and drop to an Excel sheet, because then you would risk the document to be autoformatted. Always open a new Excel sheet and "import" the data via Data / Import External Data. Here, you can specify how the data should be formatted (column by column, separated with a semicolon, and providing formatting for every column as text).

Please note the following requirements when outputting the data:

  1. A semicolon (;) must be used as the separator of the columns.
  2. A line break must be used as the separator of the rows.
  3. Character strings must be enclosed in quotation marks (e.g., "string") and may contain line breaks and semicolons. Blanks at the beginning or end should be avoided.
  4. Quotation marks within character strings need to be doubled.
  5. Floating point values must have a dot as separator between the integer and the fractional part. Do not use separators to indicate thousands (no in the integer part). The number of decimal places must not exceed the maximum number of decimal places the CRM data type permits (not even if they are zeros).
  6. Date values must have the neutral Oracle format, i.e. YYYYMMTTHH24MISS (e.g. 20050318101302).
  7. Integers must not contain dots or other separators (no decimal places). Do not use separators to indicate thousands (i.e., 1 000 instead of 1,000).
  8. Binary values must be either y for TRUE, n for FALSE or "" for undefined.
  9. Key or reference values should contain only characters from this set:
    #()*._:[]$0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
Attention

Attention: Characters such as ?<">`|´&'% cannot be used. Also, umlauts cannot be evaluated correctly if the code page is incorrect.

CRM templates for the CSV import

We recommend using the import files provided by JustRelate as templates when editing the import data.

Import of company data

For importing company data containing contact persons and addresses, we initially suggest six import files. These must be imported in the specified order.

Note: For a frictionless import, the CRM always requires suitable keys for the com­ponents and particularly foreign key attributes to provide unique identificators and assignments. Please always provide the foreign keys under the first header (…EXT_KEY).

  1. Import of the company data (customer, prospective customers, suppliers, etc.)
    All typical company data found on the General and Additional data/Financial...  tabs should be transferred here. The addresses are not transferred. You need to specify suitable keys that iden­tify the selection unambiguously in the selection table for the assignment of selection objects, like Industries, Regions or the assignment of internal persons with specific responsibilities.
    The Account managers for the records should also be stated because the import is usually carried out by an administrator or customizer to whom the records would then belong. The account manag­ers exist as the owners of the record (PSA _ORG_EXT.PSC_OWN) and as agents (PSA _ORG_EXT.CRE_PRS_GID).
    Thus, the account managers must be registered twice to ensure that the logged-in user has access to the record, and to assign the internal staff member as an account manager of the record in the CRM.
  2. Import of the PO Box addresses of the companies
    Specification of the address fields and the foreign key attributes for the designation of the company and the country.
  3. Import of the other (non-P.O. Box) addresses of the companies
    Specification of the address fields and the foreign key attributes for the designation of the company and the country, and, additionally, specification of the address type.
  4. Import of the person contact data
    This is done just like selecting the company attributes. The foreign key of the company (if available) needs to be stated as well. The account managers for the records should also be specified.
  5. Import of the P.O. Box addresses of persons
    Specification of the address fields and the foreign key attributes for the designation of the person and the country.
  6. Import of the other (non-P.O. Box) addresses of persons
    Specification of the address fields and the foreign key attributes for the designation of the person and the country and, in addition, specification of the address type.

Import of internal companies and persons

Internal persons mostly don't have an address. The system nevertheless expects a main address in the record during a CSV import. For this reason, please always specify the address of the internal com­pany in the record of the internal person.

Working with very large data sets

Very large data sets (more than 15,000 records) should be split up to avoid affecting the perfor­mance of the system. If needed, split the CSV file into smaller import files with 10,000 or fewer record rows each, adjust the header for each of them, and import the files individually. This way, you will also quickly receive a protocol indicating if the import works as it should and can be continued.

CRM mapping list

The mapping list in the templates provided by JustRelate is not related to the mapping function during the import. The former provides an overview of the keys (ID, identifier, etc.) the CRM uses. You should provide your own respective keys as Customer entries here. This is helpful when matching the keys.