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:
- Open Excel (not the CSV file!)
- Select the tab
Data
- Under Get and Transform Data select the option
From Text/CSV
- In the window Import Data, choose the downloaded CSV file and confirm with
Open
- In the next window, choose
65001: Unicode (UTF-8)
under File Origin, if it's not already selected. - 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
- After loading the file, click under
Data
, under the pointQueries and Connections
on the buttonQueries and Connections
. - In the side panel opening to the right, right-click on the query.
- Click on
Edit
in the context menu. The transform window opens. - Here, right-click on the column title that you want to transform, e.g.
Start date
. - In the context menu, select
Change Type
>Date
. - Choose
Close & Load
to reload the data. - 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.
- After loading the file, click under
Data
, under the pointQueries and Connections
on the buttonQueries and Connections
. - In the side panel opening to the right, right-click on the query.
- Click on
Edit
in the context menu. The transform window opens. - Here, right-click on the column title that you want to transform, e.g.
Duration
. - In the context menu, select
Change Type
>Decimal Number
and confirm withReplace current
. - Again, in the context menu, select
Change Type
>With Locale
. - In the new window, choose Data type
Decimal Number
and under Locale, chooseEnglish (USA)
. Confirm withOK
. - Now, drag & drop the last applied step one step higher (before Changed type):
- Choose
Close & Load
to reload the data. - 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:
Comments
0 comments
Please sign in to leave a comment.