Outlook virtually has no rules when it comes to the creation of user-defined field names, that is, almost any character is allowed in the construction of the actual user-defined field name with the following exceptions which are not allowed:

• <_> (underscore/underline)
• <,> (comma)
• <#> (number/pound sign)
• field name length not to exceed 32 characters

On the other hand, database products (such as Microsoft Access) have individual rules in terms of what characters are disallowed when defining database field names. The most common across all databases are:

• < > (leading space)
• <.> (period)
• <!> (exclamation point>
• <`> (accent)
• <[> (left square bracket)
• <]> (right square bracket)
• — (any non-printable character)
• field name length not to exceed 64 characters

Comparing the two lists above, you’ll notice that there is no overlap between the two, or in other words, any of the three illegal characters in Outlook can be used to define a field in MS Access and conversely, any of the illegal field characters can be used in user-defined field names created in Outlook.

So why does this matter to you, the Outlook user? It’s not your problem, right? Let whatever software that is going to move the data into and out of Outlook worry about things. That may be true but the focus and emphasis of this article is on “problem avoidance”, not right or wrong. To stress, different databases can have different rules as to field name structure and different file processing engines can also enforce their own restrictions (such as Microsoft Jet, SQL etc) when dealing with file names and data elements.

At a very minimum, using any of the characters above in field names, be it within Outlook or your database, means that the field name containing any illegal character cannot be duplicated between the two (i.e. <Rating.Value> – legal in Outlook is illegal in MS Access so either the field will be disallowed or some kind of character replacement will have to occur (i.e. replace an illegal character with another – such as an <*>). The other downside of character replacement is that duplicate field names can result.

A very simple example that you can try for yourself is to create a simple Excel spreadsheet giving one column the column name of “General P.O. Box” and try to import that into Outlook. You will notice that the period characters are replaced automatically by the Microsoft Excel file driver with “#” characters. Save the same worksheet as a CSV file, and the period characters are not changed when importing otherwise identical information. (ContactGenie products that process text files for importing purposes, first import the text data into an internal database and therefore the field naming conventions in terms of illegal characters apply to text files).

So what is the best practice to follow?

Very simply, the simplest and safest approach is to use only alphanumeric characters (a-z, A-Z, 0-9) with no spaces. For example, a field name of <RatingValue> is just as clear as one defined as <Rating.Value>. One only needs to look at Microsoft’s own internal field names for standard Outlook fields – <http://www.contactgenie.com/outlook_fields.htm>. For someone new to naming conventions, it can take a little getting used to thinking in terms of how a field name is to be constructed for programmatic use versus the field name (displayname/caption) that will be used for display purposes.

To put the best practice even more simply – follow the “KISS” principle and do not get cute/creative.

Internal versus Display Names for Outlook user-defined fields

When creating a new user-defined field in Outlook, the initial field name entered is defined as the internal UDF name for the field. It cannot be changed once created. When the UDFs are being created for a contact item that does not use a custom form, there is only one field name that is ever displayed in the “User-defined fields in folder” or “User-defined field in item” lists and that is the name used when creating the user-defined field.

When creating user-defined fields during the design of an Outlook custom form, there are actually two names associated with any given field. The first is the internal name used when working with a given field and the second is the DisplayName (or caption) which is the value used to show the field on the custom form. As mentioned, when creating a new user-defined field during custom form design, the initial name entered is assigned as the internal name but in this case, that name is also assigned as the field <caption> by default. Far too many people, create a user-defined field using the name they would like to see it on screen when the custom form is displayed. A field caption can be changed at any time after the field is initially created so some discipline should be used when creating new field names.

The Outlook field chooser in the custom form designer, shows all Outlook field names using the assigned DisplayNames and not internally assigned field names. DisplayNames will vary based on the language in use (i.e. English, French etc). A complete cross-reference of Outlook internal field names to display names for 9 languages can be seen here:
http://www.contactgenie.com/outlook_fields.htm

Following the rule of simplicity will go a long way in avoiding issues with other programs that deal with Outlook data with the operating assumption being that the priority is to get the job done and not try to find out how many exceptions there are to any given rule.

It would not be unreasonable to assume that when reading a CSV (comma separated values) file, that the field data would always be separated/delimited by a comma. It may not be unreasonable but it also would not be correct.

When opening or saving an Excel worksheet as a CSV file, the delimitor character expected/used is the list separator character defined in your regional settings, which for North America happens to be a comma. For many other countries, it can be something else, such as a semi-colon which in effect makes a CSV file a <custom delimited> file. Trying to import a CSV file into Outlook created by Excel in these cases will result in a failure since Microsoft Outlook expects the CSV file to be delimited by a comma.

If you find that when working with a CSV file, all the header fields are contained in the first column, the likely cause is the delimiter character used versus the one expected. ContactGenie import programs (QuickPort/DataPort) support custom delimited files so the only requirement would be to select the file as <custom delimited> and enter the correct delimiter character.

The the two approaches are:

1 – Export the folder to a PST file
2 – Manually create a PST folder and copy the folder to the new PST file

One would assume that the end result is the same in either case and the assumption would be wrong if user-defined fields are involved which have been defined at the folder level (more specifically, no custom form has been assigned as the default for the folder).

One of things that is not carried over in a folder <export> are the “user-defined fields in folder” definitions which will result in those fields not being available for any <view>. That is not the case when the folder is <copied> to a new location. In both cases, the underlying user-defined field information remains with the individual contact items.

To make user-defined fields accessible in views for an exported folder, each UDF must be added to the folder in the newly created destination. Open a new contact and add each UDF ensuring that the exact same name and field type is used to avoid potential problems. Outlook will not prevent the addition of a folder UDF which has a different field type then what exists in an individual contact item with a UDF field having the same field name.

There are many things you don’t have to think about when working strictly within the confines of a one program. However, when the data contained in one program has to be used in another program, the integrity of the data becomes important.

This article focuses on the difference between two simple functions that visually appear to do the same thing but can have radically different results. These functions are the “Clear Contents” and “Delete” options you find when you right click on an Excel row# (right clicking within a cell brings up additional options).

Let’s take the simplest of scenarios – you have an Excel worksheet with 10 rows. You decide that you no longer care about rows 6 through 10 and want to get rid of the information in the worksheet. You have two choices, clear the data or delete the rows. In both cases, your worksheet looks the same – only the first 5 rows are populated. Unfortunately, it’s the “not the same” when external factors come into play.

Clear Contents

This function does exactly what it says, clears the contents of the rows and from a visual perspective it appears that your worksheet only has 5 rows. Reality is that you still have the same 10 rows in your worksheet, only difference now being that the last 5 rows are blank. Why does this matter?

If you save this worksheet as a csv file, you are going to have 5 empty lines in your text file. Empty CSV records are impossible to miss, each line consists of a row of commas, the length of which is determined by the number of columns used. In addition, the program dealing with this CSV file has to handle the issue of empty rows.

Think of it this way, when you remove information from any kind of database, do you “delete” the record or just go through and clear the value of every field (something you can’t do in any kind of intelligently designed database even if you wanted to)?

The same issue applies when it comes to “columns”. If your worksheet has 10 columns (A thru J for sake of argument), and you “clear” columns F thru J, you still have 10 columns in your worksheet. Except now, if you save this worksheet as a CSV file, the column “header” information related to these columns will be empty (assuming that Row#1 contains column header information) meaning that whatever program has to deal with this CSV file also now has to contend with a file that has 10 fields per row but the last 5 columns have no column headings (field names).

Again, to compare this with a database (MS Access, MS SQL, Oracle etc), when you want to remove “fields” in a database (a field = a column in Excel terms), it is not done by blanking out the field name and the contents of the field (even you wanted to do that, you can’t).

Which brings us to “Delete”

This function also does exactly what the name states, it actually deletes the selected rows or columns. So to use the same example above, if you “deleted” the last 5 rows instead of “Clear Contents” and then saved the same worksheet, the CSV file will only end up having 5 lines and not 10. There will not be any trailing lines comprised of all commas. The same applies to columns.

And finally, the impact on “Named Ranges”

If you are very conversant with Excel “Named Ranges”, you will be very familiar with the following. However, for far too many people the only time “Named Ranges” become even remotely important is when they try and import an Excel worksheet into Outlook.

Using the same example, the “Named Range” would be created to cover rows/columns $A$1:$J$10. If you use “Clear Contents” to empty the last 5 rows, the Named Range does not get adjusted. You still have 10 rows within that Named Range. However, if you delete the rows, Excel will automatically adjust the Named Range to $A$1:$J$5 to reflect the change. Why is that important? Outlook will import all information included within the Named Range. Again, the same is equally true of columns.

In summary, if you’re of the mindset that the rest of the world should be able to deal with virtually any kind of garbage presented to it, then none of this is relevant. There seems to be no limit on the ways that can be found to contort data.  If, on the other hand, your objective is to accomplish a task with the least amount of potential problems and the priority is to get the job done versus finding out how many ways there are to potentially break another program, taking some very basic and minor steps goes a very long way to achieving that objective. Most problems are not created intentionally but purely caused by a lack of awareness of the consequences resulting from the choices made.

For some reason, when Outlook is involved, far too many people seem to think that anything involving Outlook is an Outlook issue. What really needs to be understood is that the actual importing and exporting from any program is a defined process. The import/export functionality in Outlook has not changed since Outlook ‘2000 (or possibly Outlook ‘97) and deals with very specific kinds of data.

Step #1

Make sure that whatever product you are using does not provide some kind of fucntionality that will directly update the contact PST files. Most won’t but some have a contact synchronization function. How do you find out? Check the help file for the program in question or go to that vendor’s website. If tools are provided from the vendor to directly update Outlook, remember that whatever happens is NOT an Outlook issue and the place to ask questions or get support should the need arise is from the vendor providing the tool. While you’re checking this, take note of what file formats the program will export its data to, specifically formats like CSV, tab-delimited etc.

Hint: search google using any term similar to “export contacts from ???” (where ??? is the program name). Very often a page outlining the steps will appear immediately.

Step #2

Become familiar with what kind of data Outlook can import data from – do this by going to Outlook’s menu – select File –> Import/Export. On this list you will see two kinds of items: (1) (very old and dated) application specific options and (2) generic file formats such as CSV or Microsoft Excel 97-2003 etc. If you don’t see your program on the list (and you most likely won’t – then the answer is that it cannot import data from that program directly. The source program must first be able to export its own data to one of the formats that Outlook supports. Typically, in 98% of the cases, there will be an option to export to a CSV (comma separated value) file but there are cases where the process is not straight-forward such as AOL, Notes etc.

Step #3

Export the contact info from “Program X” to a common file type. If there are multiple options, the safest format to select is CSV (comma separated values)

Step #4

Import the file into Outlook using the file created in step #3 – it’s a very quick and simple and easy process. From the menu bar –> File –> Import/Export and follow the screens remembering to manually map your fields (click on the MAP Custom fields button when it appears). Remember too, you cannot import data into Outlook user-defined fields and Outlook does not recognize custom forms so any new contact created via the Outlook import process will always use the IPM.Contact (standard contact form) message class. When user-defined fields are involved, you will need to write your own custom code to import the data or use a 3rd party program (such as those in the ContactGenie series)

Reports were only recieved from users using Outlook ‘2007 and  involved systems where Outlook was already running in the background. The Outlook error was not consistent and did not occur all the time. Based on reports submitted, the frequency of this problem appeared to be also directly related to the custom form in use.

The updated versions use a different approach then originally implemented to avoid this issue but still do not use Outlook for any of the actual import/export functions. 

Other issues corrected involved dates not being correctly converted to/from UTC/local date time as well as updates to the FullName/FileAs fields were not always updated correctly depending on the fields being imported and the default standard format option selected for the fields.

Anyone using an earlier version of either program, should update to the current release of QuickPort (1.0.3) or DataPort (3.0.3)  whether or not a problem was encountered.

The current versions can be downloaded from the ContactGenie download page.

The most important thing to remember regarding street addresses is that the street address is stored as a single field within Outlook which is also the way it is exported. When exporting the street address via the Outlook export wizard to either Excel or Access, the entire field may not be visible giving the appearance of missing information. The reality is that the cell (in Excel) or field (in Access) simply needs to be expanded to show all the information.

The strange characters that are seen in Excel, for example, are any <CRLF> characters (carriage return/linefeed) contained in the street address used to separate the individual lines.

There is no way to export the multi-line address field as individual fields from within Outlook, these will have to be separated manually. If you include the Street 2 and Street 3 fields from any of the address groups, teh fields will always export as empty fields. (All ContactGenie products with export functionality support exporting street address lines as separate fields (max 4 lines) per street address)

When importing a street address contained in multiple fields via the Outlook wizard, map each individual street address line (max 3 lines) to the corresponding street address lines in Outlook (i.e. Business Street, Business Street 2, Business Street 3). The Outlook import wizard will combine these fields and store them in the first Street Address field.

Finally, whenever importing a multiline street address field, it should always be mapped manually to the Outlook field street address. This way there is chance of Outlook not parsing the information correctly and placing street address info in one of the other address fields such as city, state etc.

The Display Names are based on the Microsoft Office language setting in use and change accordingly whereas the internal names always remain consistent. When information is exported from Outlook via the Outlook export wizard, the internal field names are used.

There are some fields that are not directly obvious between the two sets such as <Notes> which is internally referred to as the <Body> field.

Another group of fields which are not directly obvious are those related to the Mailing Address. For example, the MailingAddressCity is simply referred to as City whereas the equivalent field for the Business Address group is <Business Address City> (display name) and <BusinessAddressCity> (internal name).

A complete cross reference (in 9 languages) between Outlook internal and display names is available at http://www.contactgenie.com/outlook_fields.htm

All ContactGenie products now provide the option to view (and export) Outlook fields using the internal Outlook names or the Display Names in any of the supported 9 languages regardless of the language setting being used.

1. any user-defined fields (standard message class)
2. custom fields contained in a custom form
3. an Exchange public folder
4. a delegate Exchange folder
5. any standard field that does not appear in the Outlook import/export field map

The Outlook import/export wizard only creates new contacts using the standard contact form (MessageClass = <IPM.Contact>) regardless of what the default is for the form

Similarly, when exporting, no custom form assignment is recognized. Only information from the Outlook standard fields will be exported.

If any of the cases above apply to you, then your options are to either write your own custom code or use a 3rd party program.

ContactGenie products offer several different options for both import and export and addresses all the items above (ContactGenie Import products – QuickPort and DataPort 3.0 include export functionality)

For more information about ContactGenie products see the overview that is of interest:

ContactGenie Import Overview  or
ContactGenie Export Overview

There is also a free export utility available from the folks at CodeTwo  - CodeTwo Outlook Export  which allows exporting of user-defined fields which can be easily selected if the UDFs are defined for the folder. Since things change on a regular basis, you should review the current release of the program if it’s of interest to see if it is applicable for your situation.

• When I try to import it keeps asking me for a “named range”
• Ended up with all kinds of blank contacts in the Outlook folder
• It’s only importing “x” number of contacts from my Excel file no matter what I do
• Some columns are not appearing when mapping fields
• Added some new contacts (rows) to my Excel spreadsheet but these won’t import

This particular article is not going to cover the “how-to” of named ranges but rather provide a very simple alternative to importing information contained in an Excel worksheet that not only solves the above topics but eliminates a variety of other issues.

If the sole reason you are dealing with “Named Ranges” is because you are trying to import Excel data into Outlook and especially if it is something that you only do “once in a while”, then it is most likely that you are spending needless time trying to learn something that is otherwise of no value to you. You should really be concentrating on getting the task completed and move on. Some may disagree with that premise but it comes down to productivity and priorities.

The simple alternative is:

1. Save the worksheet as a CSV file
2. Import the CSV file instead of the Excel file
3. Manually map the fields you want to import (use <Map Custom Fields> button when it appears)
4. Complete the import

Not only does this one simple alternative alleviate all of the issues identified but solves some others as well that will be covered in other posts. This approach also works for any version of Excel so you do not need to be concerned about things like Excel file formats.

Similarly, on the export side when you want to use your data in Excel, the cleanest solution is to save any data as a CSV file which can be easily opened as an Excel worksheet regardless of the version of Excel in use. If you are currently using Excel ‘2007 and want the workbook to be in native Excel ‘2007 format (.xlsx file extension), you can’t do that via Outlook export in any case, so whether you save your file as a CSV or .xls – either way you will have to open the exported file and perform a “SaveAs” to get a native  ‘2007 Excel file.

You will also notice a difference in Excel column names when you export directly to an Excel file format versus exporting to an intermediate CSV file. When exporting directly to an Excel file format, all column headings will have a leading “apostrophe” <’> which does not occur if you open a CSV file and then re-save it as an .xls/xlsx worksbook. For many, this will not be important but it will affect those that use column names within the worksheet in formulas etc.

So in summary, the pro’s and con’s to using CSV versus Excel can be summarized as follows:

Pros
- Univeral format regardless of Excel version in use
- Don’t have to worry about “named ranges”

Cons
- Requires an extra step to save an Excel worksheet as a CSV file when importing
- Requires an extra step to open a CSV file in Excel and saving it in the Excel format of choice
*** the extra step will always be required if an Excel ‘2007 workbook is involved since no version of Outlook (including ‘2010) supports the native Excel ‘2007 file format (.xlsx)