Extracting Metadata from an XLS(X) File

If you want to extract metadata from an XLS(X) file, right-click Metadata (in Outline) and select New Metadata > Extract from XLS(X) file.

Hint!

Equally, you can drag an XLS file from the Project Explorer area and drop it on Metadata in the Outline. This will also bring the extracting wizard described below.

In this wizard:

• Browse for the desired XLS file and click OK.

• Choose the orientation of the source data. In Properties > Global > Orientation, you can switch between Vertical processing (row by row) or Horizontal processing (column by column).

• Select cells representing the header of your data. You can do that by clicking a whole Excel row/column, clicking and drawing a selection area, Ctrl-clicking or Shift-clicking cells just like you would do in Excel. By default, the first row is selected.

• Click Mark selection as fields. Cells you have selected will change color and will be considered metadata fields from now on. If you change your mind, click a selected cell and click Clear to not extract metadata from it.

• For each field, you need to specify a cell providing a sample value. The wizard then derives the corresponding metadata type from it. By default, a cell just underneath a marked cell is selected (notice its dashed border), see below. In the figure, 'Percent' will become the field name while '10,00%' determines the field type (which would be long in this case). To change the area where sample values are taken from, adjust Data offset (more on that below). As for colors: orange cells form the header, yellow ones indicate the beginning of the area data is taken from.

Optional tasks you can do in this dialog:

• Type in Encryption password if the source file is locked. Be sure to type the password exactly as it should be, including correct letter case or special characters.
  Type the password before specifying the file.

• Data contains headers - cells marked for field extraction will be considered headers. Data type and format is extracted from cells below the marked ones - with respect to the current Data offset.

• Extract formats - for each field, its Format property will get populated with a pattern corresponding to the sample data. This format pattern will appear in the next step of the wizard, in Property > Advanced > Format as e.g. #0.00%. For more information, see Numeric Format.

Warning!

The format extracted from metadata is not relevant to Format field in SpreadsheetDataReader. Format field is an extra metadata field holding the Excel format of a particular cell (as a string).

• Adjust Data offset (in the right-hand Properties pane, Selected cells tab). In metadata, data offset determines where data types are guessed from. Basically, its value represents 'a number of rows (in vertical mode) or columns (in horizontal mode) to be omitted'. By default, data offset is 1 ('data beginning in the following row'). Click the spinner in the Value field to adjust data offset smoothly. Notice how modifying data offset is visualized in the sheet preview - you can see the 'omitted' rows change color.

As a final step, click either Finish or Next. If you use Next, you can change the metadata name.

Extraction of Metadata from XLS(X) File into External Metadata File

You can extract metadata from an XLS(X) file and save it to an external file. In main menu, choose File > New > Other. A new window opens. In the window, choose Data Shaper > Metadata > Metadata (Extract Metadata from XLS(X) file).

The last step of the wizard lets you specify the metadata file name and its location.