Optimizely Campaign creates CSV files in UTF-8 without BOM. Microsoft Excel incorrectly interprets the format as ISO-88591. To correctly open CSV files in Excel, perform the following steps:
- Open a blank Excel workbook.
- In the Data tab, click Get Data > From File > From Text/CSV.
- Select the file to open and click Import.
- In the File origin area, select 65001: Unicode (UTF-8) and Semicolon in the Delimiters area.
- Click Load.
The Excel format allows rich formatting, calculations, and output in pivot tables and in a chart.
To use your own Excel template, download the default template, customize it then upload it. This Excel template contains the report data in the first worksheet grouped by the selected groupings. The second worksheet contains a copy of the data without sum fields. Use this worksheet to reference data you want to display in other tables or charts (for example, pivot tables).
In general, the following standards apply when formatting the Excel templates:
- Supported file formats. Optimizely Campaign supports the Excel '97 format (file suffix .xls) and Excel 2007 (file suffix .xlsx). However, Optimizely does not guarantee support of functions and formatting of these two formats.
- Data export and data format. Exported data is exported into the first table with sum fields and second worksheet table without sum fields. Do not change these worksheets; otherwise, the correct export of the data fails. The format of the header page is completely adopted, but not the content. The data formats of the first data row (row 2 in the Excel worksheet) are adopted. Thus, values can be formatted and the formatting can be changed.
- Evaluations. You can perform evaluations on the second and subsequent worksheets. Because an unlimited number of rows may be added when executing the report, chart data, pivot tables and formulas must be designed for a very large number of data rows. This especially applies to reports without time restriction.
- Pivot tables. Automatic updates must be turned on startup. You find this option under PivotTable Options > Data > Refresh data when opening the file.
Upon request, Optimizely creates Excel templates with pivot tables, charts and using the layout of your choice. Contact customer support.