Home page for Opal-Convert: Opal-Convert vCard to Excel to vCard (VCF) (online link)

Opal-Convert Excel/CSV/vCard Documentation

  • Basics
  • Convert from vCard to Excel (VCF to XLS/XLSX)
  • Convert from Excel to vCard (XLSX to VCF)
  • Filter/transpose table
  • Other options
  • Upgrade Opal-Convert to handle more contacts

  • Basics

    Opal-Convert is very easy to use, yet powerful. Simply open a file (top left button), and Opal-Convert will automatically recognize whether you've loaded a vCard file (or files), or Excel file. Then adjust any options (more details below), and then finally save the file (save button will turn green).

    As you change the options, the output will update immediately in realtime. This allows you to have instant feedback to see if the conversion results match your expectations (for example, to see whether a finished vCard picks up all the fields from your initial Excel input when you convert from Excel to vCard (vcf 2.1 or 3)). It's much more intuitive than saving a hundred times to check your results. Batch conversion is featured too so you can convert multiple vCards at once.

    You can also switch between 'Grid view' and 'Text view'. 'Grid view' is clearer to see, though the 'Text view' shows exactly what will be saved.

    Feel free to experiment with changing the options as you can reset them back to the defaults at any time (see top middle of main window).

    Converting from vCard (or vCards) to Excel

    There are many options you may wish to consider when performing this conversion. Nevertheless, if you ignore them and go on to save the Excel file, you may find the results are still to your satisfaction, since Opal-Convert usually picks suitable output names by default. In any case, it's easy to experiment because the output changes instantly as you adjust the options. See here for converting from Excel *to* vCard instead of from vCard to Excel.

    A note on batch conversion:
    You can convert a single vCard file to Excel, but Opal-Convert can also convert from multiple vCards. In this case, you might need to open a batch/folder of vCard files to create the final Excel file. This can be achieved either from opening a folder of vCard files (select "Open file/s -> From folder... (batch of vCards)") or opening the vCards directly from Outlook (select "From Outlook Directly (vCards)"). Alternatively, if you have only a single file, but one which contains multiple vCard contacts, don't open a batch, but instead select "Open file/s -> From file... (Excel or vCard file)". Opal Convert will automatically recognize that the file contains multiple vCards.

    If you want the final Excel table to have the headers down the first column instead of across the first row, then tick "Transpose / rotate the data" found in the bottom middle of the main window.

    Here are some of the main options that will be be shown:

    "Text encoding" dropdown menu
    Opal-Convert will automatically attempt to decipher the language/encoding format of the vCard/s. However, if you see strange or random symbols or characters in the left or right panel, then click the "Text encoding" dropdown menu to see a range of encodings you can try out. The top ones in the list are most likely to work, so try those first. If this doesn't work, try picking one from the "Less common encodings", or from the rest.

    "Format for Excel headers" and "Adjust Excel headers manually..." dropdown menus
    This menu presents a selection of options to determine the headers (column titles) of the output Excel, including support for a Gmail-style Excel so you can import your contacts into Gmail.

    For example, the home telephone number could look like any of these according to which one you select: "Tel (home)" (default), "Home: Tel", "Home Phone" (Gmail style) or simply "Tel". If you want to use your own headers, click the "Adjust Excel headers manually..." button. A new window will open up (see right) and you can map the existing headers to new ones (you could do this afterwards in Excel, but Opal-Convert makes it easier, and allows you to 'ignore' particular headers to remove those columns entirely).

    "Merge fields" tickboxes
    Ticking these will combine certain fields into a single column (rather than taking up 2 or more columns). For example, if "Name" is ticked, then a single column will be created for the first, last and middle names of a contact. If it is unticked, then three separate columns in the Excel will usually be created instead.

    Delimiter to use if merging fields
    The character/symbol to use when merging the previously mentioned compound fields (see "Merge fields" above).

    Further options

    Split property groupings: For rare types of vCard collections, grouped properties may cause unwanted columns in the final Excel sheet, so if you find this to be the case, untick this option. For example, the "TEL" property might be prefaced with "item1." to make "item1.TEL" for one vCard, "item2.TEL" for the next, "item3.TEL" for the third etc. etc. However, they all combine as "TEL" and should be treated as one column in the final Excel sheet.

    Filter out irrelevant parameters: Certain parameters are often redundant or superfluous in the final Excel document. Nevertheless, try unticking this option to see what information may have been excluded in the Excel headers (only the headers will be affected).

    Filter rows with empty values: Any empty rows can be filtered out if desired.

    Filter out HTML/XML/binary data: Certain vCards may have unwanted HTML or XML data. This will filter it. Also, any rows which contain binary data (denoted by "ENCODING=BASE64" in the parameter) will be filtered out if this option is selected.

    Excel export options

    Just a couple of options here:
  • You can save either as Excel 2007+ (XLSX) or the older Excel 1997-2003 workbook (XLS).
  • Also, if the input has a blank cell, you can optionally replace this with something else like a dash or word like 'empty'.

    Converting Excel to vCard (or vCards)

    First off, select the "From file... (Excel or vCard file)" from the "Open file/s" dropdown menu, then open your Excel file.

    Once the Excel is open, you should see it display on the left hand side of the main window, along with the resulting vCard/s on the right.

    If your Excel sheet lacks a header row, make sure you tick "Add header row (field titles) if missing". You may also wish to tick "Replaces newlines with custom char/s" for a bit of extra safety if your vCard reader isn't 'clever' enough to recognize properties with carriage returns in them. If aesthetics is a priority though (e.g.: for the address), keep this unticked.

    Next step is to find the "Find matches to Excel headers..." button and click it (see picture under "Further settings" below). A new window will open (see image to right) where you can map the Excel headers to the appropriate vCard properties.

    For example, for the "Tel (home)" property in the Excel sheet, Opal-Convert will try to match this to the nearest property (which is "Phone (home)" in this case). Other Excel headers you use may not be recognized, so select the dropdown for each item and match them up if you can. You can also select: <ignore> from the dropdown for any of the Excel headers, so they're not included in the final vCard.

    If your Excel has an entire "address" or "name" occupying a single header/row/column, you may want this to be picked up by the vCard "ADDR - Address Label" and "Full Name / File as" properties respectively. However, Outlook 2003 may have problems with the Address Label field, so it might be worth using the "ADDR - Street / Address line 1" property instead. You can also populate more specific vCard properties such as Last/First/Middle name and Street/Town/State. At the top of the window shown to the right is an option to select the type of vCard Opal-Convert will create. Even if your contact reader isn't Outlook (e.g.: mobile devices, Lotus Notes, Kontact/KAddressBook, or Evolution), start off with "General / Outlook (Simple)" or "vCard 2.1" mode and try the other modes out if you need more specific and rarely used properties (e.g. Nickname, IM address, or Assistant name).

    Once you're finished with this window, click Close.

    Further settings

    Now let's look at some of the vCard export options (see picture to the left), and then we're ready to save the file. As always, the best thing to do is experiment to see what works best. Opal-Convert makes this easy by adjusting the output on the fly as you tick or untick the various checkboxes.

    Output encoding: It may be worth experimenting with the "Output Encoding" dropdown menu if you have problems using the converted vCard/s in your vCard reader. If so, try out the top-most options, though usually the default - Unicode (UTF-8) - will suffice.

    Create vCard v3.0 (instead of 2.1): Some vCard readers may require the vCard in v3.0 format. If v2.1 fails when you try importing the vCard/s into your vCard reader, then try ticking this option.

    Use non-Windows EOL (for mobiles, Mac, Unix): Certain computers expect a slightly different kind of text file to what Windows expects. Tick this option if you intend to use the vCard/s on the Mac, Unix, and mobile phones.

    Remove empty vCard fields: Removes properties which don't contain any information. Cases where you might untick this is if you want the property to be displayed in your vCard reader even if the value is empty.

    Don't merge duplicate vCard properties: Sometimes, vCard readers are intelligent enough to look for duplicates of a vCard property (such as email), but where each has a different value (in such a case, it may treat them individually, which can be good (e.g.: if the reader has multiple email fields)). Otherwise, it may be best to be on the safe side, and keep this unticked..

    Include unrecognized properties ("X-" prefix): If it turns out any Excel fields can't be translated (or are unknown to the vCard format generally), then if you tick "Include unrecognized properties", Opal-Convert will prefix such properties with "X-" and add them to the vCard, ensuring little or nothing gets lost.

    If empty, derive 'File as' from name/s: If the 'Full Name / File As' field hasn't already been assigned, Opal-Convert will create it from any existing names. It shouldn't hurt to keep this ticked (especially to help compatibility with Outlook 2003 if only the name fields (and not the formatted name) have been assigned to). But if for some reason, you'd like them to remain empty and unchanged (perhaps for you to complete later, then by all means, untick this option.

    Convert address label to multiline (Outlook friendly): If an address is stored in the Address Label (LABEL) element of the vCard, it may be a good idea to tick this (or keep it ticked) if you are eventually importing the converted vCard/s into Outlook (otherwise the address may not appear). Furthermore, you can retain the carriage return formatting if you use one of the following delimiters to separate the address fields in your Excel: "=0D=0A=", "=0D=0A", "\n", "<br>", ";"

    Merge multiple contacts into single VCF file In case your reader supports this option, ticking this allows every vCard to be stored into a single file rather than a collection of files (according to how many contacts you have).

    Outlook compatiblity

    If you intend to import the converted vCard/s into Outlook, instead of clicking "Save file/s" button, clcik the blue "Import vCards into Outlook" button. Opal-Convert will take you through the stages necessary to complete the process including allowing you to import a few sample vCards for testing purposes.

    An example Excel file...

    You can convert a single Excel into a single vCard, or multiple-contact Excel file into multiple vCards. A typical multi-contact Excel could look like this:

    Telephone First name Last name Company Email
    01234 567890 Dan White BCFS dw@bcfs.com
    0089 56789 Simon Wakefield Piano TA Ltd. simonw@gmail.com
    - Matthew Smith Gizmoz Corp -
    99999 John Jones - jj@opalgra.com

    Four vCards would be created from the above Excel data. In the above case, the Excel headers are along the first row. If the headers are down the first column, so make sure you tick "Transpose / rotate the data". When you're happy with the settings, click the "Save" button, and Windows will ask you to select a folder to save the vCards to (we recommend creating a new folder for this purpose).

    You can also convert an Excel file to create just a single vCard. In this case, a sample Excel file could look like:

    Full name First name Surname Company Address (work) E-mail Phone (business) Phone (home) Mobile
    David P Jones David Jones ABC ltd. AB12 3CD; UK contact@abc.com 01234 567890 01234 567890 0011 2233444

    In the above example, the Excel headers are across the top row. If the data is formatted with the headers down the first column instead (as shown below in effectively the same Excel), then make sure you tick "Transpose / rotate the data":

    Full name David P Jones
    First name David
    Surname Jones
    Company ABC ltd.
    Address (work) AB12 3CD; UK
    E-mail contact@abc.com
    Phone (business) 01234 567890
    Phone (home) 01234 567890
    Mobile 0011 2233444

    Filter/tranpose table

    This selection of options apply no matter what you're converting from or to.

    Keep/remove selected
    This is used in conjunction with the 'Cols' and 'Rows' entries below. You can choose whether to keep certain rows/columns or remove them.

    Cols/Rows fields:
    If you only want the first column or row kept from the input, then just type in "1" (without the quotes) in the "Cols" or "Rows" fields. If you want the 2nd and 3rd columns, then you can use a comma to separate each number "2,3". You can even change the order of the columns by typing say, "3,2".

    Tranpose table option
    You can change the layout of the table so that rows become columns and vice versa.

    Remove rows containing:
    You can specifiy rows you want removed by searching for certain keywords (separate each word by a comma for multiple entries).

    Other options

    As previously mentioned you can change all options back to default with the "Reset default prefs" button. This is useful to allow experimentation, or if you feel as if things aren't working correctly (see top middle of main window).

    If you want Opal-Convert to be portable (so you can run it from a pen drive for example), then simply create an empty text file called "portable.dat" in the same location as the main executable.

    Upgrade Opal-Convert to handle more contacts

    If the demo version doesn't allow enough contacts, you can upgrade from the main web site.