From Table

The “From Table” function allows you to capture metadata from an existing datagrid in an open Excel workbook and save to a schema file.

A source datagrid can either be a Range or a Excel Table

Key Steps

To capture a schema from an existing datagrid, follow the steps below:

Step #

Step Detail

1

Select the source datagrid:

  • Tables: just click any cell inside the source table
  • Ranges: select the entire source range including column headers

2

Click on the “From Excel” button in the TableWiz Menu bar.

3

A parameters form is activated (See Figure below)

4

Fill in the parameters and/or accept the defaults generated by TableWiz based on the active worksheet and selection. Refer to the Parameter Description Table below.

5

When ready to capture metadata, click on the “Go Button”

6

The function will complete. A dialog box will inform you that the schema has been created and provide the full file name and path. Optionally, you can click on “Edit” button to open the schema in the default editor for Xml files.

7

If you want to retry the function, go to step 3

8

Else, click the “Cancel” button (or the “X” button in the top right corner of the form) to close the parameter form and complete the function.

Table 3: From Table Key Steps

Parameters

The following dialog / form appears, containing the parameters required to create a metadata schema from your source data.

Figure 8 – From Table Function Input Parameters Form

The parameters in the Parameters Form screenshot above are described in more detail in the Table below.

Table 4: From Table Parameters Description Table

Section

Property Name

Description

Data Type

Input Parameters

 

This group of parameters defines the source of the data range TableWiz will use to capture metadata information.

 
 

Workbook Name

defaults to the name of the current workbook.  to source data from a different workbook, select that workbook manually and click on the “Refresh” button

Text non-editable

 

Worksheet Name

similar to Workbook name, this defaults to the current worksheet

 
 

Refresh Button

Click on this button to refresh the source

Button

 

Source Data Type

the default value is based on the selection in the active worksheet when the “From Excel” function is started.  But it can be changed by the user.

TableWiz captures different information depending on the source data type.

Table: TableWiz captures the name of the source table

Range: Tablewiz captures the full Excel address of the source range (see screenshot below this table

 
 

# Data Rows

This parameter setting defines the number of rows that Tablewiz scans for representation information.  If the number is very large (> 10% of the total rows in the source data) a warning message will be displayed.

When capturing metadata information, TableWiz determines the data representation that has been configured in the source workbook.  Because table ranges (whether Excel Table objects or Ranges) can intermix actual data with format and layout structures e.g. subheadings, spacers, and images, TableWiz doesn’t rely on just a single row, but scans through multiple rows looking for patterns.

Please refer to the section on Data Representation and Type below for more information.

 

Schema Properties

 

This group of parameters defines the identifiers and descriptors that are written to the schema file.  All are defaulted based on the source data properties but can be edited if desired.

 
 

Schema File Name

the name of the disk file that the metadata information is saved to

 
 

Schema Name

a syntax-controlled name for the schema: must be alpha-numeric and underline characters only, with no spaces

 
 

Schema Title

Human readable Title for the schema

 
 

Schema Description

a longer description for the schema, based on all the source data properties

 

The image below shows the parameter form set to capture metadata from a source Range instead of an Excel Table.

Figure 9- Parameters Form (Source = Range)

Result

Once TableWiz has created the schema and saved to your default folder, you will see a dialog box that tells you the schema capture is complete.

At this point you have two options:

  1. Click on “Ok” to complete the function, or
  2. Click on “Edit” to open the newly created schema in the default editor for XML files.

Figure 10: Completion Dialog Box

On MS Windows, you’ll see a popup something like the figure below. On this PC, the default editor for XML files is Liquid Studio 2020 (Community Edition). This behaviour is controlled by MS Windows so what exactly happens on your system may be different.

Figure 11: Opening the schema in Default XML Editor

Here’s an example of what a schema file looks like in Liquid Studio 2020 Community Edition

Figure 12: Liquid Studio Community Edition view of a schema

Depending on your system and your XML editor your experience may be different.

You have now created a schema from a datagrid, either a Range or an Excel Table.