UPDATE: Starting with Outlook ‘2013, Excel (.xls) files are no longer supported in either the import or export process. The only options with ‘2013 are CSV text and Outlook PST files.
If you are reading this then its likely you are or have encountered a problem importing from an Excel worksheet. One Excel feature that probably generates the most questions (and problems) is “Named Ranges” which include the following:
- 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:
- Save the worksheet as a CSV file
- Import the CSV file instead of the Excel file
- Manually map the fields you want to import (use <Map Custom Fields> button when it appears)
- 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 supports the native Excel ‘2007 (.xlsx) file format.