Difference between revisions of "Exporting View Data"
Gadiyedwab (talk | contribs) |
Gadiyedwab (talk | contribs) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 20: | Line 20: | ||
* In the dialog, select "delimited" and then (very important!) for "File Origin" select "65001: Unicode (UTF-8)" | * In the dialog, select "delimited" and then (very important!) for "File Origin" select "65001: Unicode (UTF-8)" | ||
* Click next and select "Comma" as the delimited and click Finish | * Click next and select "Comma" as the delimited and click Finish | ||
| + | |||
| + | ==Exporting in Excel Workbook (.xlsx) Format== | ||
| + | |||
| + | When you export the data, you can choose to export in Excel Workbook (.xlsx) format. This format allows you to open the data directly using Excel and has several advantages: | ||
| + | |||
| + | * International characters are supported directly without the need to specify the character set. | ||
| + | * Text fields are handled as text, even if they happen to contain numbers such as in codes (e.g., "00055"), without losing any leading or trailing zeros. | ||
| + | * Dates in List views are exported directly to native Excel dates. Dates are formatted the same as they would be formatted in the list view. | ||
| + | * Decimal and Integer fields (unless unusually formatted) appear as decimals (#,##0.00) and integers (#,##0) in Excel. | ||
==Limitations== | ==Limitations== | ||
| Line 31: | Line 40: | ||
For most view, data export limit is 1,000,000 cells. That means, for example, that if the view has 10 fields, the maximum number of rows would be 100,000. | For most view, data export limit is 1,000,000 cells. That means, for example, that if the view has 10 fields, the maximum number of rows would be 100,000. | ||
| − | In some cases, the limit for list views is higher for data sources that can stream the data, typically databases. For | + | In some cases, the limit for list views is higher for data sources that can stream the data, typically databases. This is only available when exporting in CSV format. For those data sources, the limit is 5 million cells. For example, if your list has 10 fields, you may be able to export up to 500,000 rows. Such high limit is only supported when Explore Analytics can satisfy the request without holding the entire results in memory. |
===Export Limit for Pivot and Chart Views=== | ===Export Limit for Pivot and Chart Views=== | ||
Latest revision as of 16:25, 15 October 2022
Contents
Introduction
You can export the data that's displayed in the view. The data is exported in CSV format, suitable for spreadsheet programs such as Excel and for import into other applications.
The data that's exported is the formatted display data.
Exporting View Data
While the view is displaying, select "Export this view's data" from the "File" menu. The data will be exported and a file will be downloaded.
Please beware that the data is formatted and some accuracy may be lost or truncated. Do not rely on this data for backup.
International Characters
The data is exported using the UTF-8 character set. Please make sure to select this character set when using this data, for example in Excel, to ensure accurate display of international characters.
To use a UTF-8 CSV file in Excel, follow these steps:
- Open Excel with a blank new workbook
- From the Data menu select "From Text" and select the CSV file
- In the dialog, select "delimited" and then (very important!) for "File Origin" select "65001: Unicode (UTF-8)"
- Click next and select "Comma" as the delimited and click Finish
Exporting in Excel Workbook (.xlsx) Format
When you export the data, you can choose to export in Excel Workbook (.xlsx) format. This format allows you to open the data directly using Excel and has several advantages:
- International characters are supported directly without the need to specify the character set.
- Text fields are handled as text, even if they happen to contain numbers such as in codes (e.g., "00055"), without losing any leading or trailing zeros.
- Dates in List views are exported directly to native Excel dates. Dates are formatted the same as they would be formatted in the list view.
- Decimal and Integer fields (unless unusually formatted) appear as decimals (#,##0.00) and integers (#,##0) in Excel.
Limitations
There is a limit to the amount of data to be exported. The limit is on the total number of table cells. In other words, the limit is on the number of rows times the number of columns -- hence the number of cells.
The limit depends on the type of view and the type of data source.
Export Limit for List Views
For most view, data export limit is 1,000,000 cells. That means, for example, that if the view has 10 fields, the maximum number of rows would be 100,000.
In some cases, the limit for list views is higher for data sources that can stream the data, typically databases. This is only available when exporting in CSV format. For those data sources, the limit is 5 million cells. For example, if your list has 10 fields, you may be able to export up to 500,000 rows. Such high limit is only supported when Explore Analytics can satisfy the request without holding the entire results in memory.
Export Limit for Pivot and Chart Views
Because Pivot/Chart data cannot be streamed, a lower limit is implemented. Internally, the query limit is 1,000,000 cells. That's the number of cells that are requested from the data source. The data is then transformed into a Pivot table and the exact number of rows that this translates to is hard to predict. This is true for both Chart and Pivot because they are both processed in the same way.
