Exporting data
Concept Information
You can export Analytics data to other file formats so that you can use the data in other applications:
- Microsoft Excel (*.xlsx, *.xls)
- Text (*.txt)
- Delimited text (*.del)
- Comma-separated values (*.csv)
- Microsoft Access (*.mdb)
- XML (*.xml)
- JSON (*.json)
- dBASE III PLUS (*.dbf)
- Windows clipboard for pasting into other documents or applications
Note
You must have 32-bit Microsoft Access Database Engine installed to export to older Excel files (*.xls) and Microsoft Access files (*.mdb). For more information, see Exclude optional Microsoft Access Database Engine.
You can also export Analytics data to HighBond apps:
- Export exceptions to the Results app
For more information, see Exporting exceptions to the Results app in HighBond.
-
Export a compressed CSV file (*.csv.gz) to either a HighBond robot or a Workflow robot in the Robots app
For more information, see Exporting data to the Robots app in HighBond.
Opening an exported file
In the Analytics results tab, the screen display of the export command log entry contains links to:
- the exported file
- the folder containing the file
The links allow you to conveniently open the file, or the folder containing the file, directly from Analytics.
The exported file opens in the application associated with the file extension if the application is installed on your computer.
Exporting to Excel
You can export Analytics tables as individual Excel worksheets in newly created or existing Excel files. Exporting to an existing Excel file is supported for *.xlsx only.
Character and size limits
The following limits apply when exporting data to an Excel file.
Limit | Details |
---|---|
Number of records |
Analytics tables that exceed these maximums export successfully, but the excess records are ignored and not exported. |
Length of fields |
|
Length of field names |
|
Length of worksheet names |
If you use an Analytics table name as the worksheet name, the name is truncated when exported to Excel if it exceeds 31 characters. |
Datetime and time data exported to older Excel files (*.xls)
Datetime and time data exported to older Excel files (*.xls) may not display correctly when you initially open the Excel file. Datetimes may display only the date portion, and times may display “00/01/1900”. The complete datetime and time data is present in the Excel file, however you need to modify the way the cells are formatted in Excel to allow the data to display correctly.
Exporting data from server tables
You cannot save data exported from a server table to the server. You can export data from both server tables and local tables to your local computer.
Steps
You can export some or all of the records or fields in an Analytics table to use in other applications.
Specify the fields to export
- Select
- On the Main tab, select one of the
following:
- Fields specify which fields you want to export
When you select this option, the fields are exported using the physical field names in the table layout.
For information about renaming fields, see Rename a field in a table layout.
- View export all fields
in the current view
When you select this option, the fields are exported using the column display names. The fields are exported in the same order as they appear in the view.
For information about renaming columns, see Rename columns in a view.
- Fields specify which fields you want to export
- If you chose Fields, do one of
the following:
- Select the field(s) to export from the Export Fields list.
- Click Export Fields to select the field(s), or to create an expression.
Select the export format
Select the export format from the Export As dropdown list and follow the guidelines below.
Export format | Guidelines |
---|---|
Delimited or Text (or comma-separated values) |
Do one of the following:
Tip To export to a comma-separated values file (*.csv), select Delimited and make sure to select a comma , in the Column Separator dropdown list. When specifying the export file name in the To field, include the .csv file extension. For example: vendors.csv |
Excel (.xlsx) |
Do one of the following:
|
XML |
|
Exports from the Unicode edition of Analytics |
Do one of the following:
Note The Unicode option is available only when you export to Clipboard, Delimited, Text, or XML. For more information, see Diligent Unicode products. |
HighBond (HighBond users only) |
|
Robots (HighBond users only) |
Finalize the export
-
If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
Note
The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).
The IF statement considers all records in the view and filters out those that do not meet the specified condition.
- Do one of the following:
In the To text box, specify the name of the file that will contain the exported data.
Click To and specify the file name, or select an existing file in the Save or Save File As dialog box.
If Analytics prefills a table name, you can accept the prefilled name, or change it.
Note
If you are exporting data to the clipboard, the To text box is disabled because you are not saving the data in a file.
- Click the More tab.
-
In the Scope panel, select the appropriate option:
- All
- First
- Next
- While
Show me moreScope option Details All (Default) Specifies that all records in the view are processed. First Enter a number in the text box. Starts processing at the first record in the view and includes only the specified number of records. Next Enter a number in the text box. Starts processing at the currently selected record in the view and includes only the specified number of records. The actual record number must be selected in the leftmost column in the view, not data in the row. While Use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria.
Enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder.
A WHILE statement allows records in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered.
You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.
Note
The First or Next options reference either the physical order or the indexed order of records in a table. First or Next disregard any filtering or quick sorting applied to a table view. However, output results of analytical operations respect any filtering.
If a view is quick sorted, Next behaves like First.
- If you are exporting to a delimited file or a text file, optionally select Append To Existing File if you want to append the exported data to the end of an existing file.
- Click OK.
- If the overwrite prompt appears, select the appropriate option.