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 Diligent One apps:
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
Excel 2007 and later (*.xlsx) a maximum of 1,048,576
records by 16,384 fields (maximum worksheet size supported by Excel)
Excel 97 and 2003 a maximum of 65,536 records
Analytics tables that exceed these maximums export successfully, but the excess records are ignored and not exported.
Length of fields
no specific field length limit
combined field lengths cannot exceed the overall record length limit of 32 KB
(32,765 characters in non-Unicode Analytics, 16,382 characters in Unicode Analytics)
for Excel 2.1, a maximum of 247 characters
Length of field names
a maximum of 64 characters
for Excel 2.1, a maximum of 248 characters
Length of worksheet names
a maximum of 31 characters
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.
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.
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:
Delimited
optionally select Export with field names to
include the field names or the column names as headings in the export.
Select the Column Separator and Text Qualifier characters that you want to use in the delimited file.
Text optionally
select Export with field names to include
the field names or the column names as headings in the export.
Field values in the exported text file are separated with blank spaces and values are not qualified.
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:
To create a new Excel file, or export to an existing Excel file
Keep the default name in the Add
worksheet text box, or change it if required. Names longer than 31 characters are truncated.
When
you export to a newly created or existing *.xlsx Excel file a worksheet
is automatically created in the Excel file. The worksheet has the
same name as the Analytics table you are exporting from unless
you change the name.
Note
If you specify a worksheet name,
it can contain only alphanumeric characters or the underscore character
( _ ). The name cannot contain special characters, spaces, or start with
a number.
You can overwrite a worksheet in an existing Excel file, but only if the worksheet was originally created by exporting from Analytics to Excel.
You cannot overwrite worksheets that were created directly in Excel, or any worksheet that has been renamed.
To overwrite an existing Excel file
Delete
the name in the Add worksheet text
box, and leave the text box empty.
When you overwrite an existing Excel file, a worksheet
with the same name as the Analytics table you are exporting from
is automatically created in the resulting Excel file.
XML
Optionally
select Export with XML Schema to include
the XML Schema in the exported XML file.
The XML Schema contains metadata that describes the structure
of the XML file, including the data type of the fields. You can
validate the file against the Schema once the file has been exported.
Exports from the Unicode edition of Analytics
Do one of the following:
Select Unicode if the data you are exporting contains characters that are not supported by extended ASCII (ANSI)
The exported data is encoded as Unicode UTF-16 LE.
Do not select Unicode if all the characters in the data you are exporting are supported by extended ASCII (ANSI)
The exported data is encoded as extended ASCII (ANSI). Any unsupported characters are omitted from the exported file.
Note
The Unicode option is available only when you export to Clipboard, Delimited, Text,
or XML.
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:
(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.
Show me how