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 → .
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.18. Extract Metadata from Excel Spreadsheet Wizard
In this wizard:
Browse for the desired XLS file and click .
Choose the orientation of the source data. In Vertical processing (row by row) or Horizontal processing (column by column).→ → you can switch between
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
longin 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 → → as e.g. #0.00%. See Numeric Format for more information.
Figure 32.19. Format Extracted from Spreadsheet Cell
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 Next, you can change the metadata name.or . If you use
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.