The ReportAdapter for Microsoft® Excel® is used to
extract data out of an existing Excel® file. The
resulting Access file can be used with
UniversalReporter or
ApplicationReporter, to compare data of
Excel® sheets with Access tables,
e.g. with those extracted with other CAXperts
ReportAdapters. Supported operating systems: Microsoft® Windows XP Microsoft® Windows Server 2003 Microsoft® Windows Vista Microsoft® Windows 7 or later Microsoft® Excel® 2003 or later Start ReportAdapter for Microsoft® Excel® and select
your Excel® file using Browse… button,
next to Excel file field: Use Browse… button, next to Access
database field, to select the location to create a
Microsoft® Access® database file in which the results
will be shown. Settings are stored automatically in the configuration file which is
found under Tools → Settings (see
Settings). Start the process by clicking the Extract button,
after attributes to be extracted were configured (see Configure
attributes). If the specified Access database already exists, a
message box will appear whether to overwrite the existing file or not.
In case of overwriting it, a backup of the existing file (.bak) is
created first. A possibly existing backup file will be overwritten with
the new backup. Depending on the settings (see Add manual extract info)
additional information can be added to extracted data. If the option
Add manual extract info is set another window will come
up, before extraction, where information like revision number or a
description can be added. This information is stored in the output
database and can be recalled in UniversalReporter (or
ApplicationReporter). The field Days valid can be used to define a period
during which the extracted data are valid. After this period
UniversalReporter (or
ApplicationReporter) will display a warning whenever
the database is opened. ReportAdapter for Microsoft® Excel® does not contain
a predefined set of attributes, which are extracted by default. After
starting the program for the first time, customer has to define and
configure by his own, using Tools → Configure
attributes. No database specific or programming knowledge is
required. In the appearing Configure attributes window,
attributes have to be configured by Excel sheets. The combo box
Available Excel sheets contains a list of all available
sheets of the selected Excel® file. To add a sheet,
with all its attributes, to the Available attributes
tree, select the Excel® sheet from the combo box or
type the name in the Available Excel sheets field, type
the row number of the header (the row which contains the column names of
the selected sheet) in the field Header row number and
use the Add to available attributes button. The Excel
sheet name with all its attributes will appear in the Available
attributes tree. From the Available attributes, user can access all
attributes given in the Excel® header row. Names
displayed in brackets are the column names in the resulting
Access database (see Attribute mapping). The
right side (Used attributes) contains the attributes
defined for extraction. Drag the desired attributes from the left side to the right side or
check them and use the > button. Drag from right to
left or use the < button to remove attributes from
the Used attributes list. A maximum of 230 attributes
per sheet can be selected as Used attributes. If this
number is exceeded the sheet name will get a red font colour. The Search in available attributes field can be used
to find an attribute or its mapped name in the Available
attributes tree. If the search field contains a search word,
the Available attributes tree displays only attributes
which contain the search word. To go back to the complete tree, remove
the search word from Search in available attributes
field. To remove a sheet from the Available attributes,
select the sheet name from the combo box Available Excel
sheets or type the name in the Available Excel
sheets field and use the Remove from
attributes button. All Used attributes of the
respective sheet will be removed as well. Press OK to finish the configuration. The changes
will be applied on the next extraction. For comparing Excel® data in
UniversalReporter with data of other applications, a
mapping of attribute names might be necessary. It is possible to define
a separate sheet in the Excel® file which contains this
mapping information (see Settings). If no mapping is available,
the attribute names from Excel® headers are used as
column names of the Access tables and all attributes
are extracted as text. With mapping, the mapped names are displayed
between brackets, in the Configure attributes window.
The mapping sheet has to be defined as described in the following
example: The mapping range to be specified in Settings is the
range marked in red (in the example: A4:E15). The header of this range
will be ignored, important is the sequence of columns only. The first column has to contain the header name used in the
Excel® sheets (written in exactly the same way as in
the sheet), the second column is the name to be used as column name in
the Access database. The third column is only needed if
data from UniversalReporter are to be written back to
Excel® (see Manual_UniversalReporter for
SmartPlant). In this column, Read only means the
value in the Excel® sheet will never be changed,
allow insert means that values of existing rows will
not be changed, for new rows however the value will be inserted. In
general, all Excel® fields are exported as text. If
needed, however, a number format can be defined in the fourth column of
mapping sheet. Optional data column defines attribute behavior in
UniversalReporter (see Manual_UniversalReporter for
SmartPlant / Rule data window). SQL statements to extend standard output tables with user defined
attributes, to combine output table or to perform some other tasks (like
creating additional tables, queries) can be defined using Tools
→ Additional SQL statements. They will be executed
automatically after the extraction process has been completed. The additional SQL statements can be typed in the Additional
SQL statements to be processed after extraction field. They
have to look like the following examples SQL1=ALTER TABLE Equipment ADD COLUMN [MyAttribute] VARCHAR(255) SQL2=UPDATE [Equipment] SET [MyAttribute] = 'Test' Tables that can be used in the queries are those that will be created
during extraction. Additional tables (if needed) have to be copied
before extraction to the Template.mdb database which can be found in the
application folder. The included query builder can assist to create the SQL statements.
Create a new statement with the Add button and add one
of the Available SQL query templates to it. Add
query or double-click on a list item will add the query at the
cursor position or replace the selected text. The Available SQL query templates look like: UPDATE [%TABLE1%] SET [%TABLE1%].[%COLUMN1%]= When the template is used, the %TABLE1% and %COLUMN1% fields will be
filled with the table name and column name selected in the %TABLE1% and
%COLUMN1% fields. With the Show hidden tables field checked, all
tables of the database can be selected to build the query. If it is not
checked, only tables that will be displayed in
UniversalReporter are available and tables created for
internal use are hidden. User defined SQL query templates can be created. To
add a new template, click the Add button and enter the
template name. Fill in the text of SQL statement and the Placeholders for
Tables and columns to be used by query textboxes by
using the %TABLE1% and %COLUMN1% buttons. User defined templates can be deleted or modified using the
Remove or Edit buttons. All program settings are stored automatically for each
Excel® file in the ReportAdapter
configuration file, located by default in the user profile folder. The
configuration file can be changed by using menu Tools →
Settings. To import settings into the current configuration file use menu
File → Load settings from file. To export the settings from the current configuration file use menu
File → Save settings to file. Under Tools → Settings General tab,
the user can configure whether he wants to enter additional information
that will be stored together with the extracted data. Only if Add manual extract info is checked,
additional information can be added before extraction. If Keep last values is checked, the
Information window will be filled with the values last
used for the selected plant. For attribute mapping the name of the mapping sheet and the range
with mapping data (see Attribute Mapping) has to be defined on
Mapping tab: When extraction process has finished, resulting database in
Microsoft® Access is available in the specified
location. The database contains the configured tables and attributes.
Some key attributes (e.g. OID) are extracted independent of being
configured or not. These are needed for internal use.
ReportAdapter for Microsoft®
Excel®
Prerequisites
Setting up
ReportAdapter for Microsoft® Excel®
Extracting data
ReportAdapter for Microsoft® Excel®
will always display the Excel® file that was last
opened with ReportAdapter for Microsoft® Excel®.
Days valid has to be a positive number (or empty); all
other fields are text fields with up to 255 characters.
To modify or add attributes to the resulting Access
database see Configure Attributes.Configure attributes
Column names in Access are not allowed to include blanks or special
characters. This is why these are removed from all attribute names.
The attribute configuration is saved and will be reloaded whenever the
same Excel® file is used.Attribute mapping
Additional SQL statements
Create additional SQL
statements
Use query builder
The %TABLE1% and %COLUMN1% fields are filled from the Access database
defined for extraction output, which also includes the tables from
Template.mdb. This means, the fields are only filled after the
extraction was performed once.Create new SQL query
template
The SQL text boxes support syntax highlighting.Settings
Load settings from file
The location of the configuration file is not affected, only the content
of the file will be overwritten.Save settings to file
The location of the configuration file is not affected.Add manual extract info
Mapping
Content of result database
