Squares Designs |
XlContactImport | XlContactExport |
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.
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.
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 :
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 :
|
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 |
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 :
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.
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.
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.