Changing the Default Data Structures

<< <%SKIN-STRTRANS-SYNTOC%> >>

Navigation:  Data > Exporting Data >

Changing the Default Data Structures

All container commands have a set of properties that you can use to specify how to export data extracted by commands in the containers:

Property

Description

Export Method

Specifies how to export data extracted by commands in the container. The following options are available:

 

Separate - Extracted data is written to a separate child table. The data in the child table will be linked to data in its parent table by a key.

 

Add columns and Rows - Extracted data will be merged with data extracted by the parent container command. Each data entry extracted by this container command will add a new row in the data table, and new data fields will add new columns in the data table.

 

Add columns Only - Extracted data will be merged with data extracted by the parent container command. Data entries extracted by this container will not add new rows to the data table, but new data fields will add new columns to the data table. If this container extracts more data entries than there are rows in the data table, the additional data entries will be discarded.

 

Add Columns and Merge Rows - Extracted data will be merged with data extracted by the parent container command. If this container extracts more than one row, the values for each row will be combined to generate a single row, which will then be merged with data from the parent container command. The Capture command option Merge Rows Method specifies how the row values are combined.

 

Convert Rows Into Columns - Extracted data will be merged with data extracted by the parent container command. Each row extracted by this container adds new columns to the data table. The option Export Rows to Columns Name Command can be used to specify which Capture command extracts the data that will be used as the new column name, and Export Rows to Columns Value Command can be used to specify which Capture command extracts the data that will be stored on the column.

Export Rows to Columns Name Command

A capture command that will provide the data for column names. This property is optional, but must be set together with a command name for the Value Command property.

Export Rows to Columns Value Command

A capture command that will provide the data for column values. This property is optional, but must be set together with a command name for the Name Command property.

Export ID Name

Specifies the name of the primary key column in the exported table (database table, spreadsheet, CSV file, XML Node), if this container generates a new table. The export name post-fixed with ID is used if this property is empty. If multiple agents are exporting data to the same table, then you must set this option to the same value for all those agents.

Export Name

Specifies the name of the exported table (database table, spreadsheet, CSV file, XML Node),  if this container generates a new table. The command name is used if this property is empty. If multiple agents are exporting data to the same table, then you must set this option to the same value for all those agents.

Plural Export Name

Specifies the plural name of the exported table if this container generates a new table. The Export Table Name with the added character s is used if this property is empty. This property can be used to control the name of XML nodes when exporting to XML.

Export Empty Row If No Data

Exports a single empty data row if this container extracts no data. Parent and sibling data will be lost if merged with an empty data set, so this option ensures that parent and sibling data is exported when this container extracts no data.

 

Separate Export Method

When using this method, data extracted by commands in the container will saved separately. If, for example, the data exports to a database, then the data extracted by the container will go into separate database tables.

 

The figures below show a simple agent having one list container, along with the data export it generates. By default, the Movie List container will merge its data with the data from the imdb container, and the result will be a single data table containing the entire data extraction.

 

agentWithOneList

A simple agent with one list command

 

defaultMergeDataExtract

The above agent extracts this data by default. Data from the Movie List container merges with

data from the "imdb" container.

 

In this example, you can separate the data from the Movie List command by setting the property Export Method to Separate.

 

separateData

The data is exported to separate data tables when the property Sub-Container Export method is set to Separate.

 

Add Columns Only Export Method

When using this export method, data from the container will combine horizontally with data from its parent container as shown in the example below.

 

Consider an example in which the parent container and the current container generate the following data sets. Here is the data for the parent container:

 

Column1

Column2

Data1

Data2

Data3

Data4

Data5

Data6

Data7

Data8

Data9

Data10

 

Here is the data from the current container:

Column3

Column4

Column5

Data11

Data12

Data13

Data14

Data15

Data16

Data17

Data18

Data19

 

This would be the result of a merge of these two data sets:

Column1

Column2

Column3

Column4

Column5

Data1

Data2

Data11

Data12

Data13

Data3

Data4

Data14

Data15

Data16

Data5

Data6

Data17

Data18

Data19

Data7

Data8

 

 

 

Data9

Data10

 

 

 

 

 

Add Columns and Merge Rows Export Method

When using this export method, data from the container will be combined into a single row, which is then merged with data from its parent container as shown in the example below.

 

Consider an example in which the parent container and the current container generate the following data sets. Here is the data for the parent container:

 

Column 1

Column 2

Column 3

Data7

Data1

Data2

Data8

Data3

Data4

Data9

Data5

Data6

 

Here is the data from the sub-container:

Column 4

Column 5

Data10

Data12

Data11

Data13

 

The result will look like the table below if the option Merge Rows Method is set to Concatenate for both Column 4 and Column 5:

Column1

Column2

Column3

Column4

Column5

Data7

Data1

Data2

Data10,Data11

Data12,Data13

Data8

Data3

Data4

Data10,Data11

Data12,Data13

Data9

Data5

Data6

Data10,Data11

Data12,Data13

 

 

Convert Rows Into Columns

When using this export method, each data entry extracted by the container adds new data fields to the data extracted by the parent container. By default, the names of the new data fields will be the same as the names of the Capture commands extracting the data plus an index number. For example, if the Capture command name is image, the field names will be as follows:

 

image_1, image_2, image_3, ....

 

If the container extracts data such as name/value properties, then you may want the new data fields to have names that correspond to the extracted data. For example, consider the following data extracted by a container command:

 

Name

Value

Width

100

Height

50

Depth

25

 

You may want this data to generate the following data fields:

 

Width

Height

Depth

100

50

25

 

You can achieve this by setting the property Export Rows to Columns Name Command to the name of the command that will extract the values you want to use as field names. Also, set the property Export Rows to Columns Value Command to the name of the command that will extract the values you wish to use as field values.

 

Limitations

Using the export method Convert Rows Into Columns can lead to a different number of columns in the exported data table every time you run an agent. This is usually only desirable when exporting data to Excel spreadsheets, and may sometimes be useful when exporting data to CSV files.