Extracting and appending data

You can extract records or fields from one ACL table and append them as a group to the end of another ACL table with an identical record or field structure, regardless of whether one or both tables are sorted or unsorted. The resulting combined table is considered unsorted.

To extract and append data:

  1. Open the table from which you want to extract records or fields.
  2. Select Data > Extract Data.
  3. On the Main tab, do one of the following:
    • Select Record to extract entire records. The fields in the record are extracted in the order they appear in the table layout.

    • Select View to extract all the fields in the current view. The fields are extracted in the order they appear in the view.

      Note

      The number, selection, and order of the fields in the view must exactly match the number, selection, and order of fields in the target table’s table layout.

    • Select Fields to extract a selection of individual fields. The fields are extracted in the order you select them.

    If you want to extract data from a child table in a table relation, select Fields, or select View if the child table fields have previously been added to the view. You cannot extract child table data using the Record option.

    Note

    If you are extracting one or more computed fields, selecting Record preserves the extracted fields as computed expressions. Selecting View or Fields converts the extracted fields to physical fields of the appropriate data type and populates them with the actual computed values.

    You cannot append computed and physical fields to each other. If the source and/or target table contains computed fields, be aware that the option you select – Record, View, or Fields – can create or reconcile a mismatch between corresponding computed and physical fields.

  4. If you selected Fields, do one of the following:
    • Select the appropriate fields from the Extract Fields list.

    • Click Extract Fields to select the appropriate fields, or to create an expression, then click OK.

    Note

    The number, selection, and order of the fields you select must exactly match the number, selection, and order of fields in the target table’s table layout.

    Click Extract Fields if you want to select fields from a child table in a table relation. The From Table drop-down list in the Selected Fields dialog box allows you to select the appropriate child table.

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

    The IF statement considers all records in the view and filters out those that do not meet the specified condition.

  6. Do one of the following:
    • In the To text box, specify the name of the target table.

    • Click To and specify the name of the target table, or select an existing table in the Save or Save File As dialog box as the target table.

    If ACL prefills a table name, you can accept the prefilled name, or change it.

    You can also specify an absolute or relative file path, or navigate to a different folder, to append data to a target table in a location other than the project location. For example: C:\Results\GL_2011.fil or Results\GL_2011.fil. Regardless of where you append data, the target table is added to the open project if it is not already in the project.

  7. If you are connected to a server table, do one of the following:
    • Select Local to save the output table to the same location as the project, or to specify a path or navigate to a different local folder.

    • Leave Local deselected to save the output table to the Prefix folder on the ACL Server.

      Note

      For output results produced from analysis or processing of ACL Analytics Exchange server tables, select Local. You cannot use the Local setting to import results tables to ACL Analytics Exchange Server.

  8. Select or deselect Use Output Table depending on whether or not you want the ACL table containing the output results to open automatically upon completion of the operation.
  9. Click the More tab.
  10. Select the appropriate option in the Scope panel.
    • All – This option is selected by default. Leave it selected to specify that all records in the view will be 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.

    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.

  11. If required, select EOF (End of file processing) to force the extract operation to execute one more time when the end of a table is reached.

    The EOF parameter is usually used if you are extracting records as part of a larger analytic process and the Extract command occurs inside a group in a script. If you are extracting records based on a comparison between sequential records, you may need to use EOF to ensure the final record in a table is extracted.

  12. Do one of the following:
    • Select Append To Existing File if you are certain the records or fields and the target table are identical in structure.

    • Leave Append To Existing File deselected if you want ACL to compare the record lengths of the records or fields and the target table. If the record lengths are not identical, the data structure is not identical.

    Note

    Leaving Append To Existing File deselected is recommended if you are uncertain about the data structure. For more information, see Appending results to ACL tables and text files.

  13. Click OK.
  14. If the overwrite prompt appears, select Append.

    If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending results to ACL tables and text files.

Related concepts
Saving results and specifying results output folders
About extracting and appending data
Extracting and appending computed fields


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback