Appendix : the XlContactExport / XlContactImport file format

Prerequisites

Understanding the import/export file format used by XlContactExport / XlContactImport suppose you have some basic notions of using a spreadsheet, on a computer, a tablet or a phone device.

XlContactExport / XlContactImport file uses by default the classic Excel format (.xls / Excel 97 – 2003) which can be edited using old and current Microsoft Office suite on computers, and alternatives like OpenOffice, or Thinkfree, Document to go, AlwaysOnPC and several others on the Android platform. You will need one of these installed if you plan to edit an XlContactExport/XlContactImport file. To simply view it, a basic viewer will be enough.

Please note that since version 1.1.0, the pro versions of the apps, XlContactExport Pro and XlContactImport Pro offer the support for the default Excel 2007 workbook format (.xlsx) too.

If you plan to read or modify an Excel file on a computer, you can also need to know how to transfer the file from your Android phone to your computer, for example, using a direct link with an USB cable, or sending it using an email.

First look

Open the test file here.

As you can see, data for contacts are displayed line by line. Each line, row, reflect a person or a segment of the person data (for example : one line for the home datas and one for the work datas)

In the same time, each column reflects a type of data. The types currently managed by the apps are : person name, phone number, phone type, custom label of phone type, group code name, contact account name, contact account type, note, favorite, email address, email type, custom label of email type, instant messaging address, instant messaging type, custom label of instant messaging type, instant messaging protocol, custom label of instant messaging protocol, complete postal address, postal address type, custom label of postal address type, street, po box, neighborhood, city, region, postcode, city, region, country, organization name, organization type, custom label of organization type, activity name, department name, activity description, organization symbol, phonetic name of organisation, office location, event date, event type, custom label of event type, website url, website type, custom label of website type, SIP address, SIP address type, custom label of SIP address type, nickname, nickname type, custom label of nickname type, relation, relation type, custom label of relation type, given name of the person, family name of the person, prefix of the person, middle name of the person, suffix of the person, phonetic given name of the person, phonetic middle name of the person, phonetic family name of the person.

The first line is a header line describing each type. This header is optional and can be deleted if you know the format well.


For the reader who would use commonly files with delimiters (like the .csv format), please note that the XlContactImport/XlContactExport file format is not quite an usual file of this kind. We will see the major differences in the next parts.

We will refer now to the test file and a variant of the test file in the following parts, but do not hesitate to use XlContactExport if you want to understand the format using your own contacts data exported in a file.

The format

Header line

It is important to note that the header line is optional. It’s here only for your convenience. As you will see using XlContactExport options screen, you can choose to add it or not to an export file. As a consequence, XlContactImport will understand an Excel export file even if this line is not present.

Furthermore, you can change the label of a exported file at your convenience. XlContactImport will understand this modified file. XlContactImport can also understand that the first line is not a line of data but a header line if you respect these two conditions :

• the first two columns (A1 & B1) at least must be populated
• the second column (B1) must not contain a text with two numbers or above in it.

If these two conditions aren’t respected, XlContactImport will probably try to create a contact using A1 as contact name and B1 as phone number.

Columns types

As you will see, the columns order follows essentially the natural order of data you can find on the main screen for one contact of your phone contact database. Only a few "key" fields like the account and group fields are early displayed because of the importance they can have in the classification of contacts. So, the types are, by column order :

COLUMN	DESCRIPTION	PARENT / LINKED COLUMN
PERSON_NAME	A contact name. This data is very important as it will be the main information displayed on your phone and the main key for aggregation of data (1). Please note that you can repeat one name as many time as you wish on different line (as with "Alan Parker" and "Jessica" in the test file) : in this case, all data will be aggregated on the same contact name. Please read also carefully the notes on the Structured name fields which come below if you plan to use them.
PHONE_NUMBER	A phone number for the contact. To attribute another phone number to the same contact, write another line with the same contact name (as with "Alan Parker" and "Jessica" in the test file)
PHONE_TYPE	The type of phone for the current line. You have to use the following codes : "H" for "HOME" "M" for "MOBILE" "W" for "WORK" "P" for "PAGER" "FH" for "HOME FAX" "FW" for "HOME WORK" "A" for "ASSISTANT" "CL" for "CALLBACK" "CR" for "CAR" "CO" for "COMPANY MAIN" "I" for "ISDN" "MA" for "MAIN" "MM" for "MMS" "OF" for "OTHER FAX" "PA" for "PAGER" "R" for "RADIO" "T" for "TELEX" "TT" for "TTY TDD" "WM" for "WORK MOBILE" "WP" for "WORK PAGER" "O" for "OTHER" "C" for "CUSTOM" You can also use the "Joker" type if you don’t know what to use for the moment. This joker is "D" and it can be used on other column types (here, it will be transformed as "MOBILE" for the phone type)	PHONE_NUMBER
CUSTOM_PHONE_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field ("C" value in PHONE_TYPE). Leave blank otherwise.	PHONE_TYPE
GROUP_CODENAME	Name of a contact group visible on your phone and as used by the system -> the group names displayed on your phone are only translated labels for convenience. For example, a real group name is "System Group: My Contacts". Use my SystemGroups utility if you want to see what groups are available for use, or export your contact database with XlContactExport if you already have contacts put in groups. Using this column, you will be able to insert the contact in an existing group on the phone. Respect the typo of the name precisely (including spaces) to make the operation work ! Please note also that a group name will probably belong to an account, so the account data in the following 2 fields must match. Leave this field blank if you do not plan to use it (the contact will probably to go into the "not assigned" contact area, for contacts with no group).	CONTACT_ACCOUNT_NAME and CONTACT_ACCOUNT_TYPE
CONTACT_ACCOUNT_NAME	Name of a contact account visible on your phone and as used by the system. For example, an email address like "myself@gmail.com" can be the name of a contact account (2). Use my SystemGroups utility if you want to see what contact accounts are available for use, or export your contact database with XlContactExport if you already have contacts linked to accounts. Using this column, you will be able to put the contact in an existing group on the phone. Respect the typo of the name precisely (including dot and others symbols) to make the operation work ! Please note also that a contact account has a type, so the contact account type in the following field must match. Leave this field blank if you do not plan to use it (the contact will probably be created on the device account).	CONTACT_ACCOUNT_TYPE
CONTACT_ACCOUNT_TYPE	Name of a type of contact account visible on your phone and as used by the system -> the type names displayed on your phone can be only translated labels for convenience (2). For example, "com.google" can be a name of a type of contact account. Use my SystemGroups utility if you want to see what contact accounts are available for use, or export your contact database with XlContactExport if you already have contacts linked to accounts. Using this column, you will be able to put the contact in an existing group on the phone. Respect the typo of the name precisely (including dot and others symbols) to make the operation work ! Please note also that the contact account name in the previous field must match. Leave this field blank if you do not plan to use it (the contact will probably be created on the device account).	CONTACT_ACCOUNT_NAME
NOTE	Anything you want. Please note that the note field is not necessarily unique : if your phone contact interface supports it, you can create several different notes on different lines
FAVORITE	Any value you want to mark the contact as a favorite (use "F" if you don’t know what to use). Leave blank otherwise.
EMAIL_ADDRESS	A email address for the contact. To attribute another email address to the same contact, write another line with the same contact name (as with "Alan Parker" and "Jessica" in the test file)
EMAIL_TYPE	The type of email for the current line and the previous field. You have to use the following codes : "H" for "HOME" "W" for "WORK" "M" for "MOBILE" "O" for "OTHER" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "HOME" for the email type.	EMAIL_ADDRESS
CUSTOM_EMAIL_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	EMAIL_TYPE
INSTANT_MESS_ADDRESS	An instant messaging address for the contact. To attribute another instant messaging address to the same contact, write another line with the same contact name.
INSTANT_MESS_TYPE	The type of instant messaging for the current line and the previous field. You have to use the following codes : "H" for "HOME" "W" for "WORK" "O" for "OTHER" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "HOME" for the I.M. type.	INSTANT_MESS_ADDRESS
CUSTOM_INSTANT_MESS_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	INSTANT_MESS_TYPE
INSTANT_MESS_PROTOCOL	The protocol of instant messaging for the current line and the previous field. You have to use the following codes : "M" for "MSN" "S" for "SKYPE" "I" for "ICQ" "G" for "GOOGLE TALK" "Y" for "YAHOO" "A" for "AIM" "J" for "JABBER" "N" for "NETMEETING" "Q" for "QQ" "C" for "CUSTOM" You can also use the "Joker" type if you don’t know what to use for the moment. This joker is "D" and it can be used on other column types (here, it will be transformed as "MSN" for the I.M. type)	INSTANT_MESS_ADDRESS
CUSTOM_INSTANT_MESS_PROTOCOL_LABEL	Use this column only if you choose a custom protocol for the current line and the previous field. Leave blank otherwise.	INSTANT_MESS_PROTOCOL
POSTAL_ADDRESS	A complete postal address for the contact. To attribute another postal address to the same contact, write another line with the same contact name. Very important : format doesn't matter for this field you can use as your only postal address field (which is easier), but if you plan to use the following formatted field of the structured address (see below : street, po box, ..., country), be aware that you will probably see the compilation of these fields take precedence over this current field.
POSTAL_ADDRESS_TYPE	The type of postal address for the current line and the previous field. You have to use the following codes : "H" for "HOME" "W" for "WORK" "O" for "OTHER" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "HOME" for the address type.	POSTAL_ADDRESS
POSTAL_ADDRESS_STREET	The street of the structured postal address fields. Use it with the following other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_POBOX	The PO Box of the structured postal address fields. Use it with the following other fields and the previous field of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_NEIGHBORHOOD	The neighborhood of the structured postal address fields. Use it with the previous and the following other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_CITY	The city of the structured postal address fields. Use it with the previous and the following other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_REGION	The region of the structured postal address fields. Use it with the previous and the following other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_POSTCODE	The postcode of the structured postal address fields. Use it with the previous and the following other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
POSTAL_ADDRESS_COUNTRY	The country of the structured postal address fields. Use it with the previous other fields of the structured postal address, or leave it blank if you plan to use POSTAL_ADDRESS only.	POSTAL_ADDRESS
ORGANIZATION_NAME	An organization name for the contact. To attribute another one to the same contact, write another line with the same contact name.
ORGANIZATION_TYPE	The type of organization for the current line and the previous field. You have to use the following codes : "W" for "WORK" "O" for "OTHER" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "WORK" for the address type.	ORGANIZATION_NAME
CUSTOM_ORGANIZATION_TYPE_LABEL	Use this column only if you choose a custom label of organization type for the current line and the previous field. Leave blank otherwise.	ORGANIZATION_TYPE
ACTIVITY_NAME	The contact activity linked to the organization for the current line and the previous field.	ORGANIZATION_NAME
DEPARTMENT_NAME	The department name of the organization for the current line and the previous field.	ORGANIZATION_NAME
ACTIVITY_DESCRIPTION	The activity of the organization for the current line and the previous field.	ORGANIZATION_NAME
ORGANIZATION_SYMBOL	The symbol of the organization for the current line and the previous field.	ORGANIZATION_NAME
ORGANIZATION_PHONETIC_NAME	The phonetic name of the organization for the current line and the previous field.	ORGANIZATION_NAME
OFFICE_LOCATION	The office location of the organization for the current line and the previous field.	ORGANIZATION_NAME
EVENT_DATE	An date of event related to the contact. To attribute another event the same contact, write another line with the same contact name. A date is expected here, but Android manage it as a string, so be aware that this field can have a display problem in your contact interface due to the date format used. The best way to cope with it : create on your contact interface on your smartphone an event date for a contact and export the data with XlContactExport, you will see what date format is expected by your smartphone : dd/mm/yyyy ? yyyy-MM-dd ?
EVENT_TYPE	The type of instant messaging for the current line and the previous field. You have to use the following codes : "A" for "ANNIVERSARY" "B" for "BIRTHDAY" "O" for "OTHER" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "BIRTHDAY" for the event type.	EVENT_DATE
CUSTOM_EVENT_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	EVENT_TYPE
WEBSITE_URL	An url of website related to the contact. To attribute another website the same contact, write another line with the same contact name.
WEBSITE_TYPE	The type of website for the current line and the previous field. You have to use the following codes : "HP" for "HOMEPAGE" "B" for "BLOG" "P" for "PROFILE" "HM" for "HOME" "W" for "WORK" "F" for "FTP" "O" for "OTHERS" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "HOMEPAGE" for the website type.	WEBSITE_URL
CUSTOM_WEBSITE_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	WEBSITE_TYPE
SIPADDRESS	A SIP address related to the contact. To attribute another SIP address the same contact, write another line with the same contact name.
SIPADDRESS_TYPE	The type of SIP address for the current line and the previous field. You have to use the following codes : "H" for "HOME" "W" for "WORK" "O" for "OTHER" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "WORK" for the SIP address type.	SIPADDRESS_URL
CUSTOM_SIPADDRESS_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	SIPADDRESS_TYPE
NICKNAME	A nickname related to the contact. To attribute another nickname to the same contact, write another line with the same contact name.
NICKNAME_TYPE	The type of nickname for the current line and the previous field. You have to use the following codes : "D" for "DEFAULT" "M" for "MAIDEN NAME" "S" for "SHORT NAME" "I" for "INITIALS" "O" for "OTHER NAME" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don’t know what to use for the moment. Here, it will be transformed as "DEFAULT" for the nickname.	NICKNAME_URL
CUSTOM_NICKNAME_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	NICKNAME_TYPE
RELATION	A relation associated with the contact. To attribute another relation to the same contact, write another line with the same contact name.
RELATION_TYPE	The type of SIP address for the current line and the previous field. You have to use the following codes : "AS" for "ASSISTANT" "BR" for "BROTHER" "CH" for "CHILD" "DP" for "DOMESTIC PARTNER" "FA" for "FATHER" "FR" for "FRIEND" "MA" for "MANAGER" "MO" for "MOTHER" "PA" for "PARENT" "PR" for "PARTNER" "RB" for "REFERRED BY" "RE" for "RELATIVE" "SI" for "SISTER" "SP" for "SPOUSE" "C" for "CUSTOM" You can also use the "Joker" type ("D") if you don't know what to use for the moment. Here, it will be transformed as "RELATIVE" for the relative type.	RELATION_URL
CUSTOM_RELATION_TYPE_LABEL	Use this column only if you choose a custom type for the current line and the previous field. Leave blank otherwise.	RELATION_TYPE
PERSON_GIVEN_NAME	The given name of the contact. Use it with the following other fields which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_FAMILY_NAME	The family name of the contact. Use it with the following other fields, and the previous field, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_PREFIX	The prefix of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_MIDDLE_NAME	The middle name of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_SUFFIX	The suffix of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_PHONETIC_GIVEN_NAME	The phonetic given name of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_PHONETIC_MIDDLE_NAME	The phonetic middle name of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME
PERSON_PHONETIC_FAMILY_NAME	The phonetic family name of the contact. Use it with the previous and following other fields, which reflect the structured name of the contact. Be aware that this field, used with the others following fields of the structured name, will affect the display of the contact name on the smartphone after an import operation. The compilation of these fields will probably take precedence over the PERSON_NAME field, so you should use them carefully, or use only PERSON_NAME to store a general contact name.	PERSON_NAME

(1) If aggregation can be managed by the PERSON_NAME field here, you have to know that the common Android contact aggregation and its rules are still active when you import contacts. This way, in an import operation on your smartphone, 2 contacts with the same email address would probably be aggregated.

(2) The CONTACT_ACCOUNT_NAME and CONTACT_ACCOUNT_TYPE fields can be used to create contacts directly on your SIM card or directly on your device. Please be aware that if, in a refresh operation with XlContactImport, you changed first the values of the fields in order to move a contact in a different account (for example from the SIM account to a com.google account), Android cannot rewrite this information, so the contact must be deleted and recreated, and all data of the contact which is not in the import file will be lost. Of course, if you used XlcontactExport first, the case probably will not happen, as all the contact data is recorded in the file and so, can be recreated.

How to simply construct my own file ?

The simplest way to use the XlContactImport/XlContactExport file format is to "use" all the columns (by "use", let’s say that the full scope of columns is in the file, but you will probably leave a lot of then blank).
You can :

• use XlContactExport to produce a file that you will modify right after the export. All the columns will be in the export, as XlContactExport export all columns no matter the columns are populated or not . For a clearer view, keep the header line as a guide line.
• create your own file under a spreadsheet, for example, using the test file as a model. Keep the header line as a guide line and write your own data.

Important note : the type columns of the first line of data (for the first contact) must always be populated (if used in the file), even if the preceding data itself is left blank. You can use any correct values to populate the type columns if there is data, or at least the joker "D" (as you see it in the test file), if there is a blank value in the previous field.

Anyway, you must know in the same time that the number of columns can be reduced depending on what you have to do and following precise instructions. That’s what we will see in the next part if you want to dig a little further.

Tell me more on the behaviour

As it was said previously, the XlContactExport / XlContactImport file format is not a typical delimited file (like .csv). Its structure is rather a "cumulative" format, based on the order of the columns from the left to the right, and the type columns as delimiter of the groups of datas.

This signifies the header line is not analyzed by XlContactImport in an import operation, so you cannot except a XlContactImport / XlContactExport file with the order of columns changed, for example, to work. You have to respect the order described in the table above, or it would create a mess with data in the import operation. In the same way, the type columns of the first line of data (for the first contact) must always be populated (if used in the file), even if the preceding data itself is left blank. It enables the data to be segmented.

If you respect the columns order, you can reduce the number of used columns at the right side if you don’t plan to use them. For example, a test file like this one...

...is enough to create a contact named "John Doe" and its mobile phone number, and a contact named "Jessica" and its two mobiles phone numbers.

The type itself is not needed in this case ("Mobile"), as the same result would be reached if the joker "D" was added.

The phone type will become needed, at least on the first line, if you plan to add at least one email in one line, like in the example below.
We will have also to respect another condition : leave a few empty columns for the data situated BEFORE the email fields. Here, we have to leave space for the custom label of phone type, the group code name, the contact account name, the contact account type, the note and the favorite. So, after 6 columns, the email can be written.

In the same way, the email type will become needed only if you plan to add at least one instant messaging address in one line… and so on (this time, you have also to leave only one empty column for the custom label of email type).

By the way, you can add the real types !

The file for theses tests is here.

Ultimately, you understand now the only way to make use of the last columns (for example, the Relation columns or the Nickname columns) is to make sure all previous columns are included in the file… and this way, we use the whole scope of the format.

And if I do something wrong with the file ?

This situation can only apply to XlContactImport, as XlContactExport only export your current data.

In XlContactExport, use carefully the "delete and replace" option, but if you use the "refresh" option, you will have the opportunity to add or modify only the contacts and corresponding data specified in the file. All your other contacts will be left unchanged. Do not hesitate in the beginning to experiment with a smaller lot of lines in the Excel import file to gain confidence, as I did when I designed this software for my own contacts data. Today, I use XlContactExport and XlContactImport every time I need to add and change data of my contact database.