Exporting to or importing from Excel in Outlook 2013 and Outlook 2016
In previous versions of Outlook, I regularly exported some of my Outlook Calendar, Contacts and Mail data to Excel files to work with it from there or imported data into Outlook from Excel. You also have some Import/Export tips which involve Excel.
However, now that I’ve upgraded to Outlook 2016, the option to import from or export to an Excel file is no longer available.
Why has it been taken away and what is the alternative now?
The Import and Export Wizard of Outlook 2013 and Outlook 2016 has indeed been “cleaned up” by removing some old or redundant file formats. This included the Excel option as well.
Even though the xlsx-format has been the standard since Office 2003, the Import and Export Wizard still only supported the old xls-format. Instead of updating the wizard to support the xlsx-format, they took it out completely as you can basically achieve the same with the csv-format which Excel supports as well.
Opening csv-exports in Excel
The steps to export your data to a csv-file instead of an xls-file are the same with the exception that you now choose for “Comma Separated Values”;
- File-> Open & Export-> Import/Export-> Export to a file
To export to Excel choose: Comma Separated Values.
Once you have saved the csv-file, you can open it in Excel via a simple double click on the csv-file or via the “File-> Open” command in Excel 2013 or the “File-> Open-> Browse” command in Outlook 2016 and setting the file type filter to “All files (*.*)” or to “Text Files (*.prn;*.txt;*.csv”).
In most cases, this will open the file as if it was an xls-file and you can directly start working with it.
Single column issue
In some cases, everything will show up in a single column and with a lot of commas visible. That happens when your default list separator in the Regional Settings of Windows isn’t set to a comma. This can be fixed easily from within Excel in the following way:
- Click on the “A” column to select the entire column.
- Select the Data tab.
- Click on: Text to Columns
- In the Convert Text to Columns Wizard which opens, select: Delimited
- On the second screen select: Comma
- Press Finish.
When you want to save it as an actual Excel file, choose File-> Save As and set the “Save as type” dropdown to “Excel Workbook (*.xlsx)” or Excel 97-2003 Workbook (*.xls).
Quick Excel exports via Copy & Paste
You actually don’t need the Import and Export Wizard at all when you want to Export something to Excel.
When your folder is in a List view in Outlook, you can simply select the items which you want to export and then copy them (CTRL+C) in Outlook and paste them (CTRL+V) directly in Excel.
You can refine this by customizing you View in Outlook by adding or removing columns so just the data which you need will get copied so you won’t have a lot of empty columns or unwanted data in Excel either.
In addition, you can apply a filter to your View in Outlook already so you won’t have to filter them afterwards in Excel.
For a step-by-step example on how to do this for Contacts see the guide:
Export Contacts to Excel.
Save as csv in Excel and then import
When you have an Excel file which you want to import into Outlook, you must first save the file as a csv-file in Excel;
- File-> Save As and set the “Save as type” dropdown to “CSV (Comma delimited) (*.csv)”.
Save As dialog in Excel 2013.
Save As dialog in Excel 2016.
You may get the following warning when saving your file:
Some features in your workbook might be lost if you save it as CSV (Comma delimited). Do you want to keep using that format?
Simply choose “Yes” as you’ll still keep the original Excel file as well.
You can now import that csv-file into Outlook just as you would have done with an Excel file but now of course choose to import a “Comma Separated Values” file.
In most cases, when you get at the “The following actions will be performed” step, the option to import your csv-file is selected and you can press Finish to complete the Import process.
In some cases however, this option is not selected. In that case, do not select it but press Cancel as forcing the import will create broken items.
If the csv-file isn’t automatically selected when importing, something is wrong.
The reason is because with some Regional settings, Excel uses the semi-colon ( ; ) as a delimiter for a csv-file instead of a comma, which Outlook expects.
This can easily be fixed by doing a quick “Find and Replace” in Notepad or by changing the delimiter in your Regional Settings in Windows to a comma and resaving the csv-file.
For detailed instructions about that see: Importing contacts from an exported csv-file fails
