File format for contacts

The generic file format is described in Generic file format (Extended). This documents describes all available import fields.

In general:

Field with empty data mean "ignore this field".
Put value '=' will remove the current value in system.
Field with data will be imported.

A contact can be Individual or Structure so for Contact creation case: At least FIRSTNAME, LASTNAME or STRUCT_NAME must have data.

If first name/ last name is filled and structure name is blank → Individual will be created. (1)
If structure name is filled and first name + last name is blank → Structure will be created. (2)
If structure name and first name/ last name are filled → Structure + Relay will be created. (3)
- Address applies to both structure and relay.
- Communication mean (Phone/ Email...) information on contact applies to both relay and structure.
- Authorization only applies to relay.
- Contact criterion: Depends on contact type. If contact type is Structure, then applies to Structure only. If contact type is Both/ Individual, then applies to Relay only.
  Comparing 2 records which have the official name filled (can be (2) or (3)), based on structure information (official_name, address_name1, address_name2) + Address information (line1, line2, line3, zipcode, town, town_complement, country) → Decide if the same structure or different structures.
  If relay has different address with structure, create a new data file with the operation "UPDATE" in order to update the address for relay.
  Same rule with Communication mean (Phone/ Email...)

In case you want to associate a contact to a criteria or structure type. Follow these links: Define criteria's for the contacts and Define type of structure to create the records.

Examples

	File	Modified
Labels No labels Preview View	Microsoft Excel Sheet contact criteria import template.xls	Jun 21, 2019 by Laurent BURGBACHER
Labels No labels Preview View	Microsoft Excel Sheet structure types template.xls	Jun 21, 2019 by Laurent BURGBACHER
Labels No labels Preview View	PNG File image2023-7-20_11-1-3.png	Jul 20, 2023 by buih [X]
Labels No labels Preview View	Microsoft Excel Spreadsheet Contact import template full.xlsx	Feb 06, 2024 by rks

Download All

Global fields

Field code Field type Mandatory Description

INSTITUTION

Text 15

Yes

Institution code owning the data to be imported.

ORGANIZATION

Text 8

Yes

Default: *

Organization owning the data to be imported.

A « * » indicates that all organizations of the given institution are concerned. This is the default value.

OPERATION

Enum

Yes

Operation to be executed

INSERT : insert the row as a new contact

UPDATE : update the contact. Can be used to link criterion to contact.

UPDATEACT: update the contact and reactivate it if needed.

DELETE: Applicable to all contacts.

Delete the contact address, communication mean
For authorization and criterion value. Only delete the value that specified in excel file.
The email will be empty and contact can't be login to ticketshop anymore.
This action is irreversible.

INVALIDATE: Applicable to contact which has no sales.

Just mark contact as invalid with reason: ACCOUNT_DELETED.
All other information are in tact (not removed).

ANONYMIZE: Applicable to all contacts (even if it has sales).

All contact information is deleted. Contact name is changed in to 'XXXXXXXX'.
Set contact as ACCOUNT_DELETED and remark contact with 'Contact anonymized'.
The contact's sales information are in tact (not removed).
This action is irreversible.

DROP : Should only be used for mis-import. Applicable to contact that imported and has no modified since then.

Drop the contact and all its information.
The contact is not dropped if it is used in existing sales.

CONFIG: Configuration line for admin numbers and criteria.

If the codes are defined on this row, then the fields can contain only the value instead of the CodeValue.
The configuration remains valid until a new configuration line
Initially, no codes are defined, forcing the CodeValue format.

Contact

Field code	Field type	Mandatory	Description
CONTACT_NR	Text 20	The field CONTACT_NR is mandatory for all OPERATION that is not INSERT.	Contact number. If your contact numbers have leading zeros, they must also be given, as they will be kept; the contact number is stored as a text. To be able to import existing contact numbers, the sequence for contact numbers must be adapted to generate numbers high than the highest imported contact number so that new contacts do not get an existing value. This is to be handled outside the import file.
TITLE	Text 200		Data will be imported if value is defined. Complete list for a specific institution can be found in screen Institution context → Initialization → List of Values → Individual Title. Standard values are: MR, MRS, MISS, MR_AND_MRS, UNDEFINED.
LETTER_SALUTATION	Text 200		If value is empty, these fields will be interpolated from the TITLE field, according to the language of the individual. Ex: if TITLE = 'MR' → ADDR_SALUTATION = 'Dear Sir' → LETTER_SALUTATION = 'Mr'.
ADDR_SALUTATION	Text 200
FUNCTION	Text 1000		The function of the contact (Head of operations, Chairman, ...)
REMARK	Text 2000		Free remark. Adding two pipes « `\|\|` » at the beginning will add the text to an existing remark instead of overwriting it.

Individual

Field code	Field type	Description
FIRSTNAME	Text 100	First name
LASTNAME	Text 100	Last name
BIRTHDATE	Date	Birth date
PREF_LANG	Code	Preferred language. Use the 2 lettre ISO code. Examples: en, fr, es, it, ...

Structure

Field code

Field type

Mandatory

Description

OFFICIAL_NAME

Text 100

Official name of the structure. The name will be truncated if it is too long.

ADDRESS_NAME1

Text 38

Name of the structure for the adresse

If it is empty, it is taken from the official name

If it is too long, it is truncated.

ADDRESS_NAME2

Text 38

STRUCT_TYPE

Code

The type of the structure. The code must be defined as described in Define type of structure

COMMUNITY_TYPE

Code

The code of the community type. If it is left empty, then the structure isn't member of any community.

ADMIN_NUMBER

CodeValue

Administrative number, for example a SIRET number. Only one number can be given here, but subsequent updates of the contact may help adding several numbers.

On the CONFIG row, the user can define the default code.

Example

Field value	Comment
SIRET=404 833 048 00022	The SIRET number is 404 833 048 00022
404 833 048 00022	SIRET must have been defined in a CONFIG row

NB_EMPLOYEES

Number

WEB_SITE

Text 255

URL to the structure's website. It must contain the http:// prefix.

Address

Field code	Field type	Description
ADDR_TYPE	Text 3	M: Main address S: Shipment address B: Billing address Ex: M or B or S or MB or MBS, etc... Leave empty --> that address use for 3 types.
LINE1	Text 100	Address lines.
LINE2	Text 100
LINE3	Text 100
ZIPCODE	Text 10
TOWN	Text 100	Some customer use TOWN_COMPLEMENT please note: length of TOWN + length of TOWN_COMPLEMENT < Text 100 characters
TOWN_COMPLEMENT	Text 100
COUNTRY	Code	Country code using the ISO codes Example: FR, ES, EN, GB (and not UK), ... Full name of the country can also be given. They should be written exactly (spaces,hyphens, ...) as they are defined in SecuTix. By default, the country of the institution is used.
DEPARTMENT	Code	List of departments can be provided by SecuTix (/wiki/spaces/DOCEN/pages/41625114)

Communication means

Field code

Field type

Mandatory

Description

TELEPHONE

Text 15

Landline phone number.

Following formats will be recognized. These examples assume country FR (country of the address, or by default the country of the institution).

The initial << + >> sign will be recognized as well as 00.

The country prefix can be forced if it entered in parenthesis at the beginning.

All other non digit signs are ignored.

The national prefix (0 for CH, FR, 1 for US, 9 for ES, etc. see COUNTRY table) will be automatically removed in the database if present in the input number.

Examples

Input country	Input number	Stored as	Remark
FR	01 40 15 80 00	33 / 140158000	The leading 0 (national prefix) has been removed for France
FR	1 40 15 80 00	33 / 140158000
FR	0041 21 613 21 11	33 / 041216132111	Country prefix (41 but looking for 33) could not be recognized One leading 0 (national prefix) has been removed for France
CH	0041 21 613 21 11 +41 21 613 21 11	41 / 216132111	Country prefix could be recognized
FR	(41)216132111 (41)0216132111	41 / 216132111	This format allows to force the country code. This is the preferred format for import, as it lets you choose the country prefix independently from the country of residence.
FR	01 SVP 77 77	017777

FAX

Text 15

Fax number. Same rules as for PHONE.

CELLPHONE

Text 15

Mobile phone. Same rules as for PHONE.

EMAIL1

Text 250

Will be used as username for ticket shop login.

If email does already exist in the database, the email is imported for the contact but can't be used to login.

There no possibility to import a list of passwords. Each new contact will need to request a password change (click on Have you forgotten your password ?)

The email validated by regular expression: ^[0-9a-z+\$_-]+(\.[0-9a-z+\$_-]+)*@[0-9a-z+_-]+(\.[0-9a-z+_-]+)+$

EMAIL2

Text 250

Alternative email address. Cannot be used for login.

EMAIL3

Text 250

Alternative email address. Cannot be used for login.

Authorization

Field code	Field type	Description
AUTH_CNIL_O	Boolean	Accept communications from institution
AUTH_PARTNER	Boolean	Accept communications from partners
AUTH_CNIL_T	Boolean	Accept transmission of electronic coordinates to third parties
AUTH_SMS_MMS	Boolean	Accept SMS or MMS from institution
AUTH_LETTER	Boolean	Accept letter from institution
AUTH_PHONE	Boolean	Accept phone from institution
AUTH_EMAIL	Boolean	Accept email from institution

Criterion

Field code

Field type

Mandatory

Description

CRIT01

CodeValue

The code must be created as described in Define criteria's for the contacts.

Several values can be given. They need to be separated by pipes.
On the CONFIG row, the user can define the default code.

Examples:

Field value	Comment
PREFCOL=Red	The preferred color is Red only
PREFCOL=Red\|Green	The preferred color is Red and Green
Red\|Green	PREFCOL must have been defined in a CONFIG row
PREFCOL=Red PREFCOL=Green	This is equivalent to PREFCOL=Red\|Green, but using two CRIT columns.

...

CRIT50

CodeValue

File format for contacts

The generic file format is described in Generic file format (Extended). This documents describes all available import fields.

In general:

Field with empty data mean "ignore this field".
Put value '=' will remove the current value in system.
Field with data will be imported.

A contact can be Individual or Structure so for Contact creation case: At least FIRSTNAME, LASTNAME or STRUCT_NAME must have data.

In case you want to associate a contact to a criteria or structure type. Follow these links: Define criteria's for the contacts and Define type of structure to create the records.

Examples

	File	Modified
Labels No labels Preview View	Microsoft Excel Sheet contact criteria import template.xls	Jun 21, 2019 by Laurent BURGBACHER
Labels No labels Preview View	Microsoft Excel Sheet structure types template.xls	Jun 21, 2019 by Laurent BURGBACHER
Labels No labels Preview View	PNG File image2023-7-20_11-1-3.png	Jul 20, 2023 by buih [X]
Labels No labels Preview View	Microsoft Excel Spreadsheet Contact import template full.xlsx	Feb 06, 2024 by rks

Download All

Global fields

Field code Field type Mandatory Description

INSTITUTION

Text 15

Yes

Institution code owning the data to be imported.

ORGANIZATION

Text 8

Yes

Default: *

Organization owning the data to be imported.

A « * » indicates that all organizations of the given institution are concerned. This is the default value.

OPERATION

Enum

Yes

Operation to be executed

INSERT : insert the row as a new contact

UPDATE : update the contact. Can be used to link criterion to contact.

UPDATEACT: update the contact and reactivate it if needed.

DELETE: Applicable to all contacts.

Delete the contact address, communication mean
For authorization and criterion value. Only delete the value that specified in excel file.
The email will be empty and contact can't be login to ticketshop anymore.
This action is irreversible.

INVALIDATE: Applicable to contact which has no sales.

Just mark contact as invalid with reason: ACCOUNT_DELETED.
All other information are in tact (not removed).

ANONYMIZE: Applicable to all contacts (even if it has sales).

All contact information is deleted. Contact name is changed in to 'XXXXXXXX'.
Set contact as ACCOUNT_DELETED and remark contact with 'Contact anonymized'.
The contact's sales information are in tact (not removed).
This action is irreversible.

DROP : Should only be used for mis-import. Applicable to contact that imported and has no modified since then.

Drop the contact and all its information.
The contact is not dropped if it is used in existing sales.

CONFIG: Configuration line for admin numbers and criteria.

If the codes are defined on this row, then the fields can contain only the value instead of the CodeValue.
The configuration remains valid until a new configuration line
Initially, no codes are defined, forcing the CodeValue format.

Contact

Field code	Field type	Mandatory	Description
CONTACT_NR	Text 20	The field CONTACT_NR is mandatory for all OPERATION that is not INSERT.	Contact number. If your contact numbers have leading zeros, they must also be given, as they will be kept; the contact number is stored as a text. To be able to import existing contact numbers, the sequence for contact numbers must be adapted to generate numbers high than the highest imported contact number so that new contacts do not get an existing value. This is to be handled outside the import file.
TITLE	Text 200		Data will be imported if value is defined. Complete list for a specific institution can be found in screen Institution context → Initialization → List of Values → Individual Title. Standard values are: MR, MRS, MISS, MR_AND_MRS, UNDEFINED.
LETTER_SALUTATION	Text 200		If value is empty, these fields will be interpolated from the TITLE field, according to the language of the individual. Ex: if TITLE = 'MR' → ADDR_SALUTATION = 'Dear Sir' → LETTER_SALUTATION = 'Mr'.
ADDR_SALUTATION	Text 200
FUNCTION	Text 1000		The function of the contact (Head of operations, Chairman, ...)
REMARK	Text 2000		Free remark. Adding two pipes « `\|\|` » at the beginning will add the text to an existing remark instead of overwriting it.

Individual

Field code	Field type	Description
FIRSTNAME	Text 100	First name
LASTNAME	Text 100	Last name
BIRTHDATE	Date	Birth date
PREF_LANG	Code	Preferred language. Use the 2 lettre ISO code. Examples: en, fr, es, it, ...

Structure

Field code

Field type

Mandatory

Description

OFFICIAL_NAME

Text 100

Official name of the structure. The name will be truncated if it is too long.

ADDRESS_NAME1

Text 38

Name of the structure for the adresse

If it is empty, it is taken from the official name

If it is too long, it is truncated.

ADDRESS_NAME2

Text 38

STRUCT_TYPE

Code

The type of the structure. The code must be defined as described in Define type of structure

COMMUNITY_TYPE

Code

The code of the community type. If it is left empty, then the structure isn't member of any community.

ADMIN_NUMBER

CodeValue

Administrative number, for example a SIRET number. Only one number can be given here, but subsequent updates of the contact may help adding several numbers.

On the CONFIG row, the user can define the default code.

Example

Field value	Comment
SIRET=404 833 048 00022	The SIRET number is 404 833 048 00022
404 833 048 00022	SIRET must have been defined in a CONFIG row

NB_EMPLOYEES

Number

WEB_SITE

Text 255

URL to the structure's website. It must contain the http:// prefix.

Address

Field code	Field type	Description
ADDR_TYPE	Text 3	M: Main address S: Shipment address B: Billing address Ex: M or B or S or MB or MBS, etc... Leave empty --> that address use for 3 types.
LINE1	Text 100	Address lines.
LINE2	Text 100
LINE3	Text 100
ZIPCODE	Text 10
TOWN	Text 100	Some customer use TOWN_COMPLEMENT please note: length of TOWN + length of TOWN_COMPLEMENT < Text 100 characters
TOWN_COMPLEMENT	Text 100
COUNTRY	Code	Country code using the ISO codes Example: FR, ES, EN, GB (and not UK), ... Full name of the country can also be given. They should be written exactly (spaces,hyphens, ...) as they are defined in SecuTix. By default, the country of the institution is used.
DEPARTMENT	Code	List of departments can be provided by SecuTix (/wiki/spaces/DOCEN/pages/41625114)

Communication means

Field code

Field type

Mandatory

Description

TELEPHONE

Text 15

Landline phone number.

Following formats will be recognized. These examples assume country FR (country of the address, or by default the country of the institution).

The initial << + >> sign will be recognized as well as 00.

The country prefix can be forced if it entered in parenthesis at the beginning.

All other non digit signs are ignored.

The national prefix (0 for CH, FR, 1 for US, 9 for ES, etc. see COUNTRY table) will be automatically removed in the database if present in the input number.

Examples

Input country	Input number	Stored as	Remark
FR	01 40 15 80 00	33 / 140158000	The leading 0 (national prefix) has been removed for France
FR	1 40 15 80 00	33 / 140158000
FR	0041 21 613 21 11	33 / 041216132111	Country prefix (41 but looking for 33) could not be recognized One leading 0 (national prefix) has been removed for France
CH	0041 21 613 21 11 +41 21 613 21 11	41 / 216132111	Country prefix could be recognized
FR	(41)216132111 (41)0216132111	41 / 216132111	This format allows to force the country code. This is the preferred format for import, as it lets you choose the country prefix independently from the country of residence.
FR	01 SVP 77 77	017777

FAX

Text 15

Fax number. Same rules as for PHONE.

CELLPHONE

Text 15

Mobile phone. Same rules as for PHONE.

EMAIL1

Text 250

Will be used as username for ticket shop login.

If email does already exist in the database, the email is imported for the contact but can't be used to login.

There no possibility to import a list of passwords. Each new contact will need to request a password change (click on Have you forgotten your password ?)

The email validated by regular expression: ^[0-9a-z+\$_-]+(\.[0-9a-z+\$_-]+)*@[0-9a-z+_-]+(\.[0-9a-z+_-]+)+$

EMAIL2

Text 250

Alternative email address. Cannot be used for login.

EMAIL3

Text 250

Alternative email address. Cannot be used for login.

Authorization

Field code	Field type	Description
AUTH_CNIL_O	Boolean	Accept communications from institution
AUTH_PARTNER	Boolean	Accept communications from partners
AUTH_CNIL_T	Boolean	Accept transmission of electronic coordinates to third parties
AUTH_SMS_MMS	Boolean	Accept SMS or MMS from institution
AUTH_LETTER	Boolean	Accept letter from institution
AUTH_PHONE	Boolean	Accept phone from institution
AUTH_EMAIL	Boolean	Accept email from institution

Criterion

Field code

Field type

Mandatory

Description

CRIT01

CodeValue

The code must be created as described in Define criteria's for the contacts.

Several values can be given. They need to be separated by pipes.
On the CONFIG row, the user can define the default code.

Examples:

Field value	Comment
PREFCOL=Red	The preferred color is Red only
PREFCOL=Red\|Green	The preferred color is Red and Green
Red\|Green	PREFCOL must have been defined in a CONFIG row
PREFCOL=Red PREFCOL=Green	This is equivalent to PREFCOL=Red\|Green, but using two CRIT columns.

...

CRIT50

CodeValue

Browser not supported