Getting started with Microsoft Excel

Overview

This page explains how to write data to, and consume data from, Microsoft Excel files.

The Microsoft Excel module is available in Semarchy xDI Designer by default.

Create metadata

To create the metadata:

  1. Right-click the project folder, then select New > Metadata.

  2. At the New Metadata window, select Microsoft Excel and click Next.

  3. Name the data model and click Next.

  4. Select the installed Microsoft Excel module and click Finish.
    The Server Wizard displays. It allows you to define the settings required to reverse engineer and work with Microsoft Excel XLSX files.

  5. At the Choose the reverse mode step, select one of the following file path modes and click Next:

    • Excel file path: Allows you to specify a path to an existing Microsoft Excel file.

    • Excel folder path: Allows you to specify a path to a folder containing Microsoft Excel files.

      This enables you to reverse-engineer different Microsoft Excel files available in the folder and create metadata within the same data schema.

    • Legacy mode: Allows you to manually define the URL.

      Use this mode only if there is existing metadata that was created using the same mode, i.e., before the above two modes were available. In doing so, you can ensure that all your metadata is created using the same mode.

  6. Depending on the mode you selected in the previous step, perform the following actions:

    • Excel file path or Excel folder path: Browse and select the Microsoft Excel file or the folder containing Microsoft Excel files. Then select the column name style from the drop-down list and click Next.

    • Legacy mode: Specify the URL to the Microsoft Excel file. Then specify the column name style using the columnNameStyle parameter in the URL.

      (Optional) Define the specific area of a sheet from where you want to extract data or where you want to write data using the definedName parameter in the URL.

      Leave the username and password fields empty.

      Microsoft Excel component does not support password protected files. Hence, make sure that the Microsoft Excel file you selected is not password protected.

      Click Connect, then click Next and proceed to step 9.

  7. (Optional) At the Custom selection area step, click getting started excel create area to define a specific area in the Microsoft Excel file for reading or writing data and complete the information in the entry that is created:

    • Name: Name to identify the area. This name is displayed at the time of reverse-engineering data.

    • Sheet: Exact name of the sheet in the Microsoft Excel file.

    • Area: Area of the sheet in the XN:XN format. For example, A6:C10 for the area between the cells A6 and C10.

      Finally, click Next.

  8. Leave the username and password fields empty, click Connect, then click Next.

    Microsoft Excel component does not support password protected files and folders. Hence, make sure that the Microsoft Excel file or folder you selected is not password protected.

  9. At the Properties step, in Catalog Name, select the Microsoft Excel file you want to reverse-engineer.

    If you selected Excel folder path at the reverse mode step, all the Microsoft Excel files available in the folder display. However, you can only select one file at a time. You can reopen the Schema Wizard later to reverse-engineer another file from the folder.

  10. Click Next once you have selected the file you want to reverse-engineer.

  11. At the Reverse step, click Refresh to display all the sheets and areas in the selected Microsoft Excel file(s).

    Excel defined names and tables existing in the file are automatically listed alongside the sheets and custom areas.

  12. Select the sheets and areas whose data you want to reverse engineer, then click Finish.

The Microsoft Excel file is reverse engineered.

URL parameters

The column name style and the custom selection area are specified as parameters in the URL.
The Excel file path and Excel folder path modes allow you to define these parameters at specific steps of the wizard. They are automatically appended to the URL and can also be modified in the URL.
When using the Legacy mode, you need to define these parameters manually in the URL.

The following table describes the parameters:

Parameter

Description

columnNameStyle

Determines how the column names are handled. Possible values are:

  • PRESERVE: the column names are used as specified in the metadata.

  • FLAT: removes special characters from the column names, and makes them lowercase.

  • NORMALIZE: removes special characters from the column names, and makes them UPPERCASE.

The URL, including the parameter, should be:

jdbc:semarchy:excel://<pathToTheFile>/customers.xlsx?forceDatatypeAsString=true&columnNameStyle=PRESERVE

definedNamed

Defines an area of a sheet from where data is to be extracted or where data is to be written.

definedNamed:"<areaName>"="<fileName>"."<sheetName>$!<area>"

For example, in the following Microsoft Excel file named customers.xlsx, we wish to select the area between the cells A6 and C10 from sheet1:
getting started excel sheet area

The URL including the parameters should be:

jdbc:semarchy:excel://<pathToTheFile>/customers.xlsx?forceDatatypeAsString=true&columnNameStyle=PRESERVE&definedNamed:"myArea"="customers"."sheet1$!A6:C10"

Create mapping to write to a Microsoft Excel file

The following example illustrates a mapping used to write data from a database table to a Microsoft Excel file.
getting started excel write mapping

By default, the template used in the mapping for writing data to a Microsoft Excel file does not delete the existing data.

To delete the existing data, select the Truncate Target Table option in the template.

Create mapping to read from a Microsoft Excel file

The following example illustrates a mapping used to read data from a Microsoft Excel file and write it to a database table.
getting started excel read mapping