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.

Example

Scenario

You want to perform analysis on an entire set of employee records but records for new employees are not yet contained in the Employee master table.

Approach

You extract the records for new employees and append them to the end of the Employee master table, and then perform the analysis.

Tip

A single execution of the append operation can replace multiple executions of the extract and append operation.

For more information, see Appending tables.

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.

Tip

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

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

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

Analytics 14.1 Help