Exporting data

Concept Information

EXPORT command

You can convert and export Analytics data to use in other applications. Destination applications and formats include:

  • Microsoft Excel (.xlsx, .xls)
  • Text (.txt)
  • Delimited text (.del)
  • Comma-separated values (.csv)
  • Microsoft Access (.mdb)
  • Windows clipboard for pasting into other documents or applications
  • XML (.xml)
  • JSON (.json)
  • dBASE III PLUS (.dbf)
  • HighBond (export exceptions to Results)

    For more information, see Exporting exceptions to HighBond Results.

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 to 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:

Number of records
  • Excel 2007 and later (*.xlsx) a maximum of 1,048,576 records
  • 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

 

Datetime and time data exported to Excel

Datetime and time data exported to Excel 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

  1. Select Data > Export.
  2. 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.

  3. 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 drop-down list and follow the guidelines below.

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 drop-down 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.

    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.

For more information, see Diligent Unicode products.
HighBond

(HighBond users only)

Finalize the export

  1. 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.

  2. 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.

  3. Click the More tab.

  4. Select the appropriate option in the Scope panel:

    • All
    • First
    • Next
    • While
    All This option is selected by default. Leave it selected to specify that all records in the view are processed.
    First Select this option and enter a number in the text box to start processing at the first record in the view and include only the specified number of records.
    Next Select this option and enter a number in the text box to start processing at the currently selected record in the view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row.
    While

    Select this option to use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria. You can 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 number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

    If a view is quick sorted, Next behaves like First.
  5. 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.
  6. Click OK.
  7. If the overwrite prompt appears, select the appropriate option.