About extracting and appending data

Extracting and appending allows you to copy records or fields from one ACL table and append them as a group to the end of another ACL table, regardless of whether one or both tables are sorted or unsorted (Figure 1). Any existing sort orders in the individual tables are separately maintained in the respective record sets in the resulting combined table. Even if the records in both tables are sorted, the resulting combined table is considered unsorted because the extracted records are appended to the end of the target table, without consideration of any existing sort order in the target table.

You can use multiple iterations of the extract and append operation to perform useful tasks such as combining monthly or quarterly tables into an annual table. The annual table retains any internal sorting of the monthly or quarterly data. If required, you can sort the resulting combined table after performing one or more extract and append operations.

Figure 1. Extracting and appending

Important

A best practice is to never append records to an original data file. You should create a new target table first, by extracting the records from the original table into a new table. Then extract the records from the source table or tables, and append them to the new table. This method preserves the original data file in the event you encounter any problems with the extract and append process.

Requirements when extracting and appending data

When you extract and append data, the data must meet certain requirements in order for the operation to be successful. If the data does not meet the requirements, jumbled, missing, or inaccurate data can result.

Tip

In some instances it may be easier or more practical to combine data outside ACL. If you have difficulty appending data in ACL because of inconsistencies between fields, see Combining data outside ACL.

The three different ways of extracting and appending data – by record, by view, and by fields – have somewhat different requirements. If you extract and append entire records, the data in both tables must be exactly identical in structure. If you extract and append the fields in a view, or a selection of individual fields, only the data structure of the corresponding fields in the two tables needs to be identical. Table 1 summarizes the requirements for the different extract and append methods, and Figure 2 illustrates the differences between extracting and appending by record, and by view or by fields.

Table 1. Extract and append requirements

Requirement

Extract/append by record

Extract/append by view

Extract/append by fields

Data elements

The fields (data elements) in the source and target tables must be the same.

Yes

No

The fields in the target table can be a subset of the fields in the source table.

No

The fields in the target table can be a subset of the fields in the source table.

Order of fields

The fields in the source and target tables must be in the same order in the table layouts.

Yes

No

The fields in the source table view must be in the same order as the fields in the target table’s table layout.

No

You must select the fields in the source table in the same order as the fields in the target table’s table layout.

Number of fields

The number of fields in the source and target tables must be in the same.

Yes

No

The number of fields in the source table view must be the same as the number of fields in the target table’s table layout.

No

You must select a number of fields in the source table equivalent to the number of fields in the target table’s table layout.

Structure of view

The data structure of the source table view must be identical to target table’s table layout.

No

Yes

No

Record length

The overall record length in the source and target tables must be the same.

Yes

No

No

Field length

The length of the corresponding fields in the source and target tables must be the same.

Yes

Yes

Yes

Start position

The start position of the corresponding fields in the source and target tables must be the same.

Yes

No

No

Data type

The data type of the corresponding fields in the source and target tables must be the same.

Yes

Yes

Yes

Datetime format

The format of dates and datetimes in corresponding fields in the source and target tables must be the same.

Yes

Yes

Yes

Figure 2 illustrates extracting and appending by record, which requires identical data structures in the source and target tables, and extracting and appending by view or by fields, which requires only that corresponding fields have an identical data structure, and that you extract the fields in the same order as their order in the target table’s table layout.

Figure 2. Extract and append by record, or by view/fields

You can extract and append data from both server tables and local tables. Data extracted from a server table can be appended to a table on the server, or on your local computer. Data extracted from a local table can be appended only to a table on your local computer.

If a difference in data structure at the field level is preventing successfully extracting and appending data, you may be able to harmonize the fields. For tables with different record structures (that is, data elements are not identical), use joining or relating.

Related concepts
About merging tables
Extracting and appending versus merging
Combining data
Data structure and data format requirements
Harmonizing fields


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