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 → .
![[Tip]](figures/tip.png) | Tip |
|---|---|
Equally, you can drag an XLS file from the Navigator area and drop it on Metadata in the Outline. This will also bring the extracting wizard described below. |
 |
Figure 32.22. Extract Metadata from Excel Spreadsheet Wizard
In this wizard:
Browse for the desired XLS file and click .
Decide about the orientation of the source data. In → → 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 colour 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
longin this case). To change the area where sample values are taken from, adjust Data offset (more on that below).
As for colours: 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 → → as e.g. #0.00%. See Numeric Format for more information.
Figure 32.23. Format Extracted from Spreadsheet Cell
![[Caution]](figures/caution.png)
Caution The format extracted from metadata has nothing to do with 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 visualised in the sheet preview - you can see the 'omitted' rows change colour.
As a final step, click either or . 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 → → . A new window opens. In the window, choose → → .
The last step of the wizard lets you specify the metadata file name and its location.