# ReportAdapter for Microsoft® Excel® The **ReportAdapter for Microsoft® Excel®** is used to extract data from an existing **Excel®** file. The resulting **Access** file can be used with **UniversalReporter** or **ApplicationReporter** in order to compare data from **Excel®** sheets with **Access** tables, for example those extracted with other **CAXperts ReportAdapter**s. ## Prerequisites - Supported operating systems: - Microsoft® Windows XP - Microsoft® Windows Server 2003 - Microsoft® Windows Vista - Microsoft® Windows 7 or later - Microsoft® Excel® 2003 or later ## Setting up ReportAdapter for Microsoft® Excel® ### Extracting data Start **ReportAdapter for Microsoft® Excel®** and select your **Excel®** file by using the **Browse...** button next to the **Excel file** field: ![](./media/ReportAdapterForMicrosoftExcel_image8.png) > **ReportAdapter for Microsoft® Excel®** always displays the **Excel®** file that was last opened with the application. Use the **Browse...** button next to the **Access database** field to select the location in which the **Microsoft® Access®** database file is to be created. Settings are stored automatically in the configuration file shown under **Tools → Settings**, see *Settings*. Start the process by clicking **Extract** after the attributes to be extracted have been configured, see *Configure attributes*. If the specified **Access** database already exists, a message box appears asking whether the existing file should be overwritten. If it is overwritten, a backup of the existing file, `.bak`, is created first. Any existing backup file is overwritten with the new backup. Depending on the settings, see *Add manual extract info*, additional information can be added to the extracted data. If the option **Add manual extract info** is enabled, another window appears before extraction, in which information such as a revision number or description can be entered. This information is stored in the output database and can be recalled in **UniversalReporter** or **ApplicationReporter**. The **Days valid** field can be used to define the period during which the extracted data remain valid. After this period, **UniversalReporter** or **ApplicationReporter** displays a warning whenever the database is opened. > **Days valid** has to be a positive number (or empty); all other fields are text fields with up to 255 characters. ![](./media/ReportAdapterForMicrosoftExcel_image10.png) > To modify or add attributes in the resulting **Access** database, see *Configure attributes*. ## Configure attributes **ReportAdapter for Microsoft® Excel®** does not contain a predefined set of attributes that are extracted by default. After starting the program for the first time, the user must define and configure the required attributes by using **Tools → Configure attributes**. No database-specific or programming knowledge is required. ![](./media/ReportAdapterForMicrosoftExcel_image11.png) In the **Configure attributes** window, attributes are configured by Excel sheet. The **Available Excel sheets** combo box contains a list of all available sheets in the selected **Excel®** file. To add a sheet, together with all its attributes, to the **Available attributes** tree, select the **Excel®** sheet from the combo box or enter its name in the **Available Excel sheets** field. Then enter the header row number, that is, the row containing the column names of the selected sheet, in the **Header row number** field and click **Add to available attributes**. The Excel sheet name, together with all its attributes, then appears in the **Available attributes** tree. From **Available attributes**, the user can access all attributes defined in the **Excel®** header row. Names displayed in brackets are the column names in the resulting **Access** database, see *Attribute mapping*. The right-hand side, **Used attributes**, contains the attributes defined for extraction. > Column names in Access must not include spaces or special characters. These are therefore removed from all attribute names. Drag the required attributes from the left-hand side to the right-hand side, or select 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 is shown in red. 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 term, the **Available attributes** tree displays only those attributes containing that term. To return to the full tree, remove the search term from the **Search in available attributes** field. To remove a sheet from **Available attributes**, select the sheet name from the **Available Excel sheets** combo box, or enter the name in the **Available Excel sheets** field, and use the **Remove from attributes** button. All **Used attributes** belonging to that sheet are removed as well. Press **OK** to finish the configuration. The changes are applied during the next extraction. > The attribute configuration is saved and reloaded whenever the same **Excel®** file is used. ## Attribute mapping When comparing **Excel®** data in **UniversalReporter** with data from other applications, a mapping of attribute names may be necessary. It is possible to define a separate sheet in the **Excel®** file containing this mapping information, see *Settings*. If no mapping is available, the attribute names from the **Excel®** headers are used as the column names of the **Access** tables and all attributes are extracted as text. When mapping is used, the mapped names are displayed in brackets in the **Configure attributes** window. The mapping sheet must be defined as shown in the following example: ![](./media/ReportAdapterForMicrosoftExcel_image12.png) The mapping range to be specified in **Settings** is the range marked in red, in the example `A4:E15`. The header of this range is ignored. Only the sequence of columns is relevant. The first column must contain the header name used in the **Excel®** sheets, written exactly as it appears in the sheet. The second column is the name to be used as the column name in the **Access** database. The third column is needed only if data from **UniversalReporter** are to be written back to **Excel®**, see *Manual_UniversalReporter for SmartPlant*. In this column, **Read only** means that the value in the **Excel®** sheet is never changed, while **allow insert** means that the values of existing rows are not changed, but the value is inserted for new rows. In general, all **Excel®** fields are exported as text. If required, however, a number format can be defined in the fourth column of the mapping sheet. The optional data column defines attribute behaviour in **UniversalReporter**, see *Manual_UniversalReporter for SmartPlant / Rule data window*. ## Additional SQL statements ### Create additional SQL statements SQL statements used to extend standard output tables with user-defined attributes, to combine output tables, or to perform other tasks, such as creating additional tables or queries, can be defined by using **Tools → Additional SQL statements**. These statements are executed automatically after the extraction process has been completed. The additional SQL statements can be entered in the **Additional SQL statements to be processed after extraction** field. They should follow the format shown in the following examples: SQL1=ALTER TABLE Equipment ADD COLUMN \[MyAttribute\] VARCHAR(255) SQL2=UPDATE \[Equipment\] SET \[MyAttribute\] = \'Test\' ![](./media/ReportAdapterForMicrosoftExcel_image13.png) The tables that can be used in the queries are those that are created during extraction. Additional tables, if required, must be copied before extraction into the `Template.mdb` database, which can be found in the application folder. ### Use query builder The included query builder can assist in creating SQL statements. Create a new statement with the **Add** button and add one of the **Available SQL query templates** to it. Clicking **Add query**, or double-clicking a list item, inserts the query at the cursor position or replaces 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 are filled with the table name and column name selected in the corresponding fields. > 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 that the fields are populated only after extraction has been performed once. If the **Show hidden tables** field is checked, all tables in the database can be selected in order to build the query. If it is not checked, only the tables displayed in **UniversalReporter** are available, while tables created for internal use remain hidden. ### Create new SQL query template User-defined **SQL query templates** can be created. To add a new template, click **Add** and enter the template name. ![](./media/ReportAdapterForMicrosoftExcel_image14.png) Enter the SQL statement text and the placeholders for the **Tables and columns to be used by query** text boxes by using the `%TABLE1%` and `%COLUMN1%` buttons. User-defined templates can be deleted or modified by using the **Remove** or **Edit** buttons. > The SQL text boxes support syntax highlighting. ## Settings All program settings are stored automatically for each **Excel®** file in the **ReportAdapter** configuration file, which is located by default in the user profile folder. The configuration file can be changed by using **Tools → Settings**. ### Load settings from file To import settings into the current configuration file, use **File → 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 To export the settings from the current configuration file, use **File → Save settings to file**. > The location of the configuration file is not affected. ### Add manual extract info On the **General** tab under **Tools → Settings**, the user can configure whether additional information should be entered and stored together with the extracted data. Additional information can be entered before extraction only if **Add manual extract info** is checked. If **Keep last values** is checked, the **Information** window is filled with the values most recently used for the selected plant. ![](./media/ReportAdapterForMicrosoftExcel_image15.png) ### Mapping For attribute mapping, the name of the mapping sheet and the range containing the mapping data, see *Attribute Mapping*, must be defined on the **Mapping** tab: ![](./media/ReportAdapterForMicrosoftExcel_image16.png) ## Content of result database When the extraction process has finished, the resulting database in **Microsoft® Access** is available in the specified location. The database contains the configured tables and attributes. Some key attributes, for example `OID`, are extracted regardless of whether they were configured explicitly. These are required for internal use.