Export Table Data as a CSV or XLSX File

You can export table data as a CSV or XLSX file.

General Considerations Before You Export Data

Restriction

The following restrictions apply when exporting data:

  • The export option is disabled if any of the following are valid:

    • There is no data in the table.

    • The model that is used is in Edit mode.

  • Formatting (such as cell color, font styles, and so on) will not be exported.

  • The exported data has the same number of decimal places as are shown in the table.

    For example, if your table is set to show four decimal places and your unformatted data has seven decimal places, the exported data will only include four decimal places.

    To include all seven decimal places, in the Styling panel select Decimal Places and change the value.

  • Hyperlinks are removed.

  • Hierarchies are flattened.

  • Only the table data region is exported.

    Custom cells outside the data region (for example, on a grid page) are not exported.

Note

Before exporting data, make sure that the number formats are the same for the model and your local machine. For example, if the data you want to export uses a comma as a decimal separator and a period as a thousands separator (123.456,78), you will need to verify that your machine's number format is the same.

On a PC, in the Control Panel, go to Start of the navigation pathClock, Language, and Region Next navigation step Region Next navigation step Additional SettingsEnd of the navigation path and verify the separator formats.

You can also export from the Explorer. For more information, see Accessing the Explorer.

The notification dialog provides information on the export progress or completion. It also allows you to cancel the export.

Scope Options for Table Export

For table data, you can choose the scope of your CSV or XLSX file export to be either Point of view or All.

The following table contains more information on each scope:

Scope Information
  Point of View All
Scope description

Exports what you see in the table data region (all the visible rows and columns).

This includes visible comment columns, story/model calculations, and hierarchy levels in separate columns.

Exports all the data, not just the currently visible data.

Example
If your table has a hierarchy, all the nodes of the hierarchy will be exported, even if you have not drilled down on the data in your table.
Restriction

The All export doesn't include the following:

  • Classic Design Experience: visible content applied to the table such as calculations, comment columns, or renamed dimensions.

  • Optimized Story Experience: visible content applied to the table such as calculations or comment columns.

Hierarchies of visible dimensions are expanded.
Scope restrictions and notes

When there are too many rows or columns in your table, you will get a drill limitation warning.

Tip

The drill limitation can be adjusted to export more Point of view data. To export more data, do the following:

  1. Select the warning icon in your table to view the warning message.

  2. In the warning message, select Edit Drill Limitation.

  3. In the Edit Drill Limitation dialog, select Unlimited.

  4. Select OK.

Classic Design Experience: if you have renamed a measure or dimension, you won't see your names in the exported data. The measure and dimension names from the data source are exported.

SAP Analytics Cloud profile settings do not influence the format of the exported data.

There is no export of BW display attributes.

The following table features will not be exported:

  • Added row/column calculations including data point comments and dimension comments.

  • Hidden rows/columns.

  • Properties such as extra rows or columns for the ID or description.

Filters are considered in export.

The following table contains restrictions specific to scope and file type:

Restrictions Specific to Scope and File Type
File Type Point of View All

CSV

There are no specific restrictions.

Note

Browser, memory, and personal computer restrictions can still apply.

You can't export more than 3 million cells or 60 measure columns.

XLSX

You can't export more than 500 thousand cells.

You can't export more than 1.5 million cells of data.

Only the following data model types are supported:

  • SAP Analytics Cloud acquired

  • HANA live

  • BW live

Only classic story mode is supported.

Export Table Data as a CSV or XLSX File

You can export both acquired and live data from a table as a CSV or XLSX file. However, live data may take some time to export, because it must be downloaded before it can be exported.

To export table data as a CSV file or XLSX file, do the following:

  1. Choose your table.

  2. Choose Start of the navigation path (More Actions) Next navigation step  ExportEnd of the navigation path.

  3. In the dialog, provide a Name for your file.

  4. For File Type, choose either CSV or XLSX.

  5. Select the Scope for your file to be either Point of view or All.

    Tip

    If you want to include custom cells (that are outside the table data region) in your export, you need to select all the cells, copy them, and then paste them into Excel.

  6. To maintain Scaling, Units, and Currencies formatting, select Include Formatting.

    Tip

    Use Include Formatting when you want the formatting to exactly match what is in the chart or table. For example, if the table shows $12.04 Million, that is how the value will be exported, even if that is a rounded value.

    Excel treats these values as text, not as numeric data.

  7. Depending on the file type you selected, you will see one of the following options in the dialog:

    Selected File Type Option Option Description
    CSV Flatten the Hierarchy To flatten hierarchical data, select Flatten the Hierarchy.
    XLSX Indent Hierarchy To keep the hierarchical indentation for the data labels, select Indent Hierarchy.

  8. If you selected CSV as the file type, choose a CSV Delimiter (column separator).

  9. Select OK.

    Depending on your browser settings, your file is saved automatically or a dialog opens for you to choose where to save your file.

Note

If you have characters such as parentheses or the percent sign in your exported data, Microsoft Excel may treat your numeric data as text and align it to the left instead of the right.

To correct the formatting, do the following:

  1. In Excel, select the column that is incorrectly formatted.

  2. From the ribbon menu, select “Data”, select “Text to Column”, and then select “Finish”.

  3. From the ribbon menu, select “Home”, and then apply the correct number formatting.

Import Your CSV File into Excel

Remember

Before opening the exported CSV file in Excel, verify that your Excel list separator settings are set correctly.

Tip

If you have data columns that contain only numeric values (such as ID columns, zip codes, or data with leading zeros), you will need to import your CSV file into Excel. If you try to open it directly in Excel, the numeric columns will be treated as numbers, not text.

  1. Make a note of where you saved the CSV file, but do not open it.

  2. Open Excel.

    The following instructions apply to Excel 365. If you have an older version of Excel, the steps may be slightly different.

  3. Select the “Data” tab.

  4. Select “From Text/CSV”.

    The “Import Data” dialog box (Windows explorer dialog) appears.

  5. Select the CSV file to import and click “Import”.

    The text import dialog appears.

  6. From the “File Origin” list, select “Unicode (UTF-8)”.

  7. Select “Transform Data”.

    The “Power Query Editor” opens.

  8. Find a column that needs the data type to be changed.

  9. In the column header, right-click, select “Change Type”, and then select “Text”.

    When the prompt appears, select “Replace Current”.

  10. Repeat the process for each column that needs to have its type changed.

  11. When you are finished, from the menu select “File” and then select “Close & Load”.

Save your file as an Excel file.

Related Information