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 "
I don't want to do anything with the
ignore this field".
Put value '=' will remove the current value in system.
Field with data will be imported.

Note

When you need a standard import, please use the template as example.
Please keep all columns, all 4 first rows, event if there’s no data.
Please keep the order of all columns and all rows. Don’t move or change them.
Please keep the headers/titles as they are, especially the 4th row.
If you want to send us *.csv file, please keep columns, rows and their order also.
Please use option “Comma delimited” for .csv file.

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

Note

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 will be created and then Relay will be created. (3)
- Preferred language applies to both structure and relay.
- 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

Attachments

Global fields

Field code	Field type	Mandatory	Description

INSTIT

INSTITUTION	Text 15	Yes	Institution code owning the data to be imported.
ORGANIZATION	Text

15

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.

Del
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

.The contact row is removed from system forever

.

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

Yes if OPERATION

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.

Note: The field CONTACT_NR is mandatory for all OPERATION that is not INSERT.

ROLEEnum Role of the contact when it is created. If the field is left empty, RELAY will be used when Individual and structure data are given. PUBLIC when only individual or structure data is given.TITLEText 200


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 of individual/relay
LASTNAME	Text 100	Last name of individ
BIRTHDATE	Date	Birth date of individual/relay.
PREF_LANG	Code	Preferred language applies to both structure and relay 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
IT	(39)0444 651070	39 / 0444 651070	Keep the “0” in telephone numbers for Italy.

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 Gene. 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

Attachments

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,

it

these fields will be interpolated from

other field.LETTER_SALUTATIONText 200

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	Mandatory	Description
FIRSTNAME	Text 100

First name

of the individual


LASTNAME	Text 100

Last name

of the individual


BIRTHDATE	Date

	Birth date
PREF_LANG	Code

Language code

Ex

Preferred language.

Use the 2 lettre ISO code. Examples: en, fr, es, it, ...

PHOTO_PATHText 2000

Structure

Field code	Field type	Mandatory	Description

STRUCT

OFFICIAL_NAME

Text 100

Structure name

Data

Official name of the structure. The name will be

truncate if length > 100

truncated if it is too long.
ADDRESS_NAME1	Text 38

Taken from structure name if not defined (first 38 characters)

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

Taken from structure name if not defined (next 38 characters).STRUC_CODECodeValue

Must be predefined in system

See reference page Structure type definition

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

WEBSITEText 2000

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	Mandatory	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

Anything. Customer responsible for correct data

	Address lines.
LINE2	Text 100


LINE3	Text 100


ZIPCODE	Text 10


TOWN	Text

20

100

Some customer use TOWN_COMPLEMENT please note:

length of TOWN + length of TOWN_COMPLEMENT < Text 100 characters

TOWN_

COMP

COMPLEMENT

Text

20

100
COUNTRY	Code

Country code

Ex

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

PHONE

TELEPHONE

Text 15

Landline phone number

. Follow standard format and international format.

Ex: 01 40 15 80 00, 0169395090, (33)0134806517,

FAXText 15 CELLPHONEText 15

Mobile phone

Ex: 06 88 48 87 10, (33)0677744594

EMAIL 1Text 50

.

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

is already existedEMAIL 2Text 50 EMAIL 3Text 50

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

The email validation are different from systems. It won't be imported if validation failed (standard email format will be ok, don't worry).

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	Mandatory	Description
AUTH_CNIL_O	Boolean

Accept

communication

communications from institution
AUTH_PARTNER	Boolean

Accept

communication

communications from

one partner

partners
AUTH_CNIL_T	Boolean

Accepts

Accept transmission of

elec.

electronic coordinates to third parties
AUTH_SMS_MMS	Boolean

Accept

sms/mms

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

CRIT1CodeValue

Formula: CritCode=CritValue1|CritValue2|CritValue3

Special case: If we use CONFIG row which mean:

CONFIG contains CritCode.
Contact contains CritValue1|CritValue2|CritValue3.

Must be predefined in Contact criteria definition

... CRIT50CodeValue

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

Version	Old Version 22	New Version Current
Changes made by	LGN (Unlicensed)	Kho SA
Saved on	Feb 24, 2017	yesterday at 5:03 pm

Versions Compared

Key

File format for contacts

Examples

Global fields

Contact

Individual

Structure

Address

Communication means

Authorization

Criterion

File format for contacts

Examples

Global fields

Contact

Individual

Structure

Address

Communication means

Authorization

Criterion

Content Comparison

Versions Compared

Key

File format for contacts

Examples

Global fields

Contact

Individual

Structure

Address

Communication means

Authorization

Criterion

File format for contacts

Examples

Global fields

Contact

Individual

Structure

Address

Communication means

Authorization

Criterion