Process CSV exports further in Excel

At many places in the system, views can be exported as a CSV file. The CSV format is a format that can be read by Excel, but unfortunately, it can't be directly opened in Excel. It's not enough to just "open" the CSV file with Excel. The file must be "imported" as a data source. 

And here's how to do it:

1. Open Excel (not the CSV file!)
2. Select the tab Data
3. Under Get and Transform Data select the option From Text/CSV
4. In the window Import Data, choose the downloaded CSV file and confirm with Open 
5. In the next window, choose 65001: Unicode (UTF-8) under File Origin, if it's not already selected.
6. Confirm the loading of the file with the button Load

Transforming CSV Export Data

Depending on what you're exporting, for example, date fields or decimal fields may not have the correct data type and therefore won't be properly formatted in the loaded Excel file.

This is a CSV-specific issue, but it can be resolved with a few clicks.

Transforming a Date Column

1. After loading the file, click under Data, under the point Queries and Connections on the button Queries and Connections.
2. In the side panel opening to the right, right-click on the query.
3. Click on Edit in the context menu. The transform window opens.
4. Here, right-click on the column title that you want to transform, e.g. Start date. 
5. In the context menu, select Change Type > Date.
6. Choose Close & Load to reload the data.
7. Done. The Start Date column now has the Date format. 

Transforming a Decimal Column

If a number field that represents a decimal number in smenso is exported, it may also happen that this is not correctly displayed in Excel.

Example: When exporting time evaluations, the time duration of a recorded time is such a decimal field, which is not correctly displayed by Excel by default.

To transform the decimal field and display it correctly in Excel, proceed as follows.

1. After loading the file, click under Data, under the point Queries and Connections on the button Queries and Connections.
2. In the side panel opening to the right, right-click on the query.
3. Click on Edit in the context menu. The transform window opens.
4. Here, right-click on the column title that you want to transform, e.g. Duration. 
5. In the context menu, select Change Type > Decimal Number and confirm with Replace current.
6. Again, in the context menu, select Change Type > With Locale.
7. In the new window, choose Data type Decimal Number and under Locale, choose English (USA). Confirm with OK.
8. Now, drag & drop the last applied step one step higher (before Changed type):
   
   
   
   
9. Choose Close & Load to reload the data.
10. Done. The Duration column now has the Decimal Number format in the correct locale and is displayed correctly. 

The following short video shows both transformation steps: