Map by order

Spreadsheet cells are mapped one by one to output fields in the same order as on the input. If you select another metadata, the cells will be remapped automatically to the new fields.

Map by name

Content is mapped to the record field with the same name or label. For each mapped leading cell, the component reads its contents (string) and tries to find a matching field with the same name or label (see Field Name vs. Label vs. Description).

Fields that could not be mapped to the current file are marked as unresolved. You can either map these explicitly, unmap them or modify output metadata.

Note that unresolved cells are not a bad thing – for example, you might read a group of similar input files, each containing just a subset of possible columns. Mappings with unresolved cells do not result in your graph failing on execution.

	Note
	Both Map by order and Map by name modes try to automatically map the contents of the input file to the output metadata. Thus these modes are useful in cases when you read multiple source files and you want to design a single "one-fits-all" generic mapping.

Explicit

In Explicit mapping, you explicitly decide which cells are mapped to which record fields.

This way, you can have, for example, a whole sheet mapped by order with only one cell, which does not fit the mapping, mapped explicitly to a correct field.

To use explicit mapping, go to Selected cells and fill in Field name or index with the target field.

If a cell is not mapped yet, you might need to switch Mapping mode to Explicit, first. You can also explicitly map a cell to a field by dragging the field from metadata viewer onto the cell. Opposite direction also works (dragging a cell to a field), but you have to first click the cell to select it, because only selected cell can be dragged. Note that you can drag-and-drop more fields/cells at once.

Auto

In auto mapping, the first spreadsheet row is whole mapped by name with data offset equal to 1.

The auto mapping is used, if you leave all the Mapping component property completely blank.

Another type of auto mapping is created when you map no cell in the mapping editor, but confirm the mapping by clicking the OK button. Then only basic mapping properties will be stored in the Mapping attribute. This way, you can change default Rows per record or Data offset used by the basic implicit mapping mentioned above (if default offset is set to 0, mapping by name is used instead of mapping by name).

Alternatively, by switching the reading Orientation property, the first column gets implicitly mapped instead of the first row.

Toolbar – buttons controlling how you Map your Excel data (either by order, or by name) and global data offset control (For an explanation of data offsets, see Advanced mapping options).

Sheet preview area – this is where you will do and see all the mapping of the source file.

Output metadata – Clover fields you will map Excel cells to.

Properties – either for the whole source file (Global) or just the ones concerning Selected cells

Summary tab – a place where you can neatly review the whole spreadsheet-to-CloverDX mapping you have made.

Select existing metadata in the graph using the Output metadata combo.

Create new metadata using the <new metadata> option in the Output metadata combo.

Double click a Field to rename it.

Change data Type via combo-boxes.

For more operations on the output metadata use the Edit button.

To create metadata, drag cells from the spreadsheet preview area and drop them between output metadata fields.

First, make sure you have set the Vertical mode in Properties → Global → Orientation. This makes SpreadsheetDataReader process the input by rows (opposite to Horizontal orientation, where reading advances by columns).

Optional (in case you have not extracted metadata as in Extracting Metadata from an XLS(X) File): select the first row and drag its fields to the Output metadata pane. This will create fields for all cells in the selection. Types will be guessed automatically, but it is worth checking them yourself afterwards.

Select the whole first row (by clicking the "1" row header) and click either Map by order or Map by name (for explanation, see Introduction to Spreadsheet Mapping).

Figure 54.18. Basic Mapping – notice leading cells and dashed borders marking the area data will be taken from

Data offsets (global)

Data offsets (global) determines where data is taken from. Basically, its value represents 'a number of rows (in vertical mode) or columns (in horizontal mode) to be omitted - relative to the leading cell (orange)'.

Click the arrow buttons in the top right corner to adjust data offsets for the whole spreadsheet. Additionally, you can click the spinner in the Selected cells area of each leading cell (the orange one) to adjust data offset locally, i.e. for a particular column only.

Notice how modifying data offset is visualized in the sheet preview – the 'omitted' rows change color. By following dashed cells, which appear when you click a leading cell, you can quickly state where your record will start at.

	Tip
	The arrow buttons in Data offsets (global) only shift the data offset property of each cell either up or down. So mixed offsets are retained, just shifted as desired. To set all data offsets to a single value, enter the value into the number field of Data offsets (global). Note that if there are some mixed offsets, the value is displayed in gray.

Figure 54.19.  The difference between global data offsets set to 1 (default) and 3. In the right hand figure, reading would start at row 4 (ignoring data in rows 2 and 3).

Figure 54.20.  Global data offset is set to 1 to all columns. In the third column, it is locally changed to 3.

Rows per record

Rows per record is a Global property specifying how many rows form one record. Best imagined if you look at the figure below:

Figure 54.21.  Rows per record is set to 4. This makes SpreadsheetDataReader take 4 Excel rows and create one record out of their cells. Cells actually becoming fields of a record are marked by a dashed border; therefore, the record is not populated by all data. Which cells populate a record is also determined by the data offsets setting, see the following bullet point.

Combination of Data offsets and Rows per record

Combination of Data offsets (global and local) and Rows per record – you can put the settings described in preceding bullet points together. See example:

Figure 54.22.  Rows per record is set to 3. The first and third columns contribute to the record by their first row (because of the global data offset being 1). The second and fourth columns have (local) data offsets 2 and 4, respectively. Thus the first record will be formed by 'zig-zagged' cells (the yellow ones – follow them to make sure you understand this concept clearly).

Max number of records

Max number of records is a Global property which you can specify via component attributes, too (see SpreadsheetDataReader Attributes). If you reduce it, you will notice the number of dashed cells in the spreadsheet preview reduces as well (highlighting only the cells which will be mapped to records in fact).

Format Field

Excel format (as in Excel's right-click menu – Format Cells) can be retrieved from read cells. Select a leading cell and specify the Format Field property (in Selected cells) as a target field to which the format patterns will be read. Keep in mind the target field has to be string. You can use this approach even if read data cells have various formats (e.g. various currencies).

	Note
	If an Excel cell has the General format, the format cannot be transferred to CloverDX due to internal Excel formatting. Instead, the target field will bear a string "General".

Figure 54.23.  Retrieving format from a date field. Format Field was set to the "Special" field as target.

Formats can also be extracted during the one-time metadata extraction process. In metadata, format is taken from a single cell which you supply as a sample value to the metadata extraction wizard. See Extracting Metadata from an XLS(X) File.

If a cell has its format specified by the Excel format string (excel:), SpreadsheetDataReader can read it back. Other readers would ignore it. For further reading on format strings, see Formatting cells (Field with format).

Multiple leading cells per column

In some spreadsheets, data in one column gets mixed, but you still need to process it all into one record. For example, imagine a column containing first names in odd rows and surnames in even rows one after another. In that case, you will create two leading cells above each other to be able to read both first names and surnames. Remember to set Rows per record to an appropriate value (2 in this example) not to read same data in all leading cells. Also, mind raising Data offset in the upper leading cell to start reading data where it truly begins. Look at the figure below:

Figure 54.24.  Reading mixed data using two leading cells per column. Rows per record is 2, Data offset needed to be raised to 2 – looking at the first leading cell which has to start reading on the third row.