Extracting and appending data

Extracting and appending data allows you to extract records or fields from one Analytics table and append them as a group to the end of another Analytics table. Extracting is the same as copying, and appending is the same as adding. The two tables can be sorted or unsorted.

The table you append to (the target table) is increased in size. A new table is not created.

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.

Extract and append best practice

When you extract and append data, 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.

Different options when extracting and appending data

There are three different options you can choose from when extracting and appending data:

Option	Description
Extract by record	Extracts entire records. The fields are extracted in the order they appear in the table layout. The data structure of the source and target tables must be exactly identical.
Extract by view	Extracts all the fields in the current view. The fields are extracted in the order they appear in the view. The data structure of the corresponding fields in the source and target tables must be exactly identical.
Extract by fields	Extracts a selection of individual fields. The fields are extracted in the order you select them. The data structure of the corresponding fields in the source and target tables must be exactly identical.

The key difference between the options

The figure below illustrates the key difference between extracting and appending by record, and by view or by fields.

Extracting and appending by record

As shown in the two tables on the left side of the figure, with fields A, B, C: The number and the order of the fields must be identical in the source and target tables.

Extracting and appending by view or by fields

As shown in the two tables on the right side of the figure: The number and the order of the fields in the source and target tables does not have to be identical. In this situation, you tailor the view in the source table, or select the appropriate fields when you extract, to match the number and the order of fields in the target table. In the example below, you position fields in the view, or select fields, in the order: D, B, E. You omit fields A and C.

How sorting works when extracting and appending

When you extract and append, any existing sort orders in the source and the target 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 as a group to the end of the target table, without consideration of any existing sort order in the target table.

For example, if you extract and append monthly or quarterly tables to create an annual table, any internal sorting of the monthly or quarterly data is retained. If required, you can sort the resulting combined table after performing one or more extract and append operations.

Extracting and appending from server tables and local tables

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.

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.

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 more information, see Harmonizing fields.

For tables with different record structures (that is, data elements are not identical), use joining or relating.

The table below summarizes the requirements for the different extract and append options.

Requirement	Extract and append by record	Extract and append by view	Extract and append by fields
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 Corresponding 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 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
Field name The names of the corresponding fields in the source and target tables must be the same.	No	No	No
	Target table field names are used in the resulting combined table.
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