Data Mapper – User Manual

Mapping of data from unstructured to structured tables is done via the Data Mapping tool. The tool contains instructions on how to map the important data of an Excel file to be extracted. The data is mapped into structured database tables that can be used to visualize the data (Insights), track changes, consolidate or access from other platforms (Power Query, SQL etc.).

To open the data mapper, click on the “more” menu icon  (by hovering over the right-hand side of the FileBox) and select “Data Mapper”.

• The mapping is based on the latest file version of a FileBox.
• To use a specific version of a FileBox, go to the version tab of the FileBox (click on the FileBox name or choose FileBox Details) and open the Data Mapper from the “more” menu  of the version you wish to use.

 

Data Mapper Tool

1. First, name the table, by clicking on "New+".
2. If you wish to modify an existing table, you can access it by clicking on the drop-down menu.  

3. Table Type - Flat/ Dimension/ Multiple Tables
   Once you name the table you will see the following mapping options:
   1. Flat Table - Simple table with only Headers
   2. Dimension Table - Table with both Headers and Dimensions
   3. Multiple Tables - Allows you to map multiple tables within your sheet

4. You may choose which sheet or sheets (Excel tabs) to map. This is done by clicking on the gear icon near the table name.   Here you will specify the relevant sheets to map and save processing time by scanning the relevant ones only.

 5. Structure Role Types
The following list specifies the types of roles you can assign to a cell/range in a worksheet:

1. Header – Define the selected cells as the table’s headers. The selected cells will be the column headers of the mapped table, and the data that resides below will populate the values in that column.
2. Dimension – Group the headers in the selected cells together, as a time dimension for example. The mapped table will have a column with the group name as a header and each one of the selected cells will be a value in that column. It would also generate a “Value” column with the values for each one of the dimensions’ cell values.
   1. For example: Time dimension can be “Period”. We will select the headers of columns with monthly data (Jan, Feb etc.) to classify all the values for these dates under the “Period” dimension.
3. Attribute – Add properties that will apply to the entire table. The value of an attribute is extracted from a cell to the right of the selected attribute cell.
4. ID - Add properties that will apply to the entire table. The value of an attribute is extracted from the selected cell.
5. Footer – Select a cell to define the end of the table to be scanned. Define where to stop scanning for additional data, exclude data that resides below the mapped table but should be excluded from the extracted data. The selected cell and any data below it will not be included in the mapped table.
6. Custom Column - A custom column is a column where the value in a cell is computed using a formula that you create.  For the complete list of supported formulas click here. 

You do not have to include all cell types when mapping, you can use only one or several relevant roles together. Make sure to click the “Save” button to save the table structure for the file. 

Click here to see data mapping example, using the different role types

There are advanced mapping options in the tool - “Match Type”, "Data Manipulation" and "Data Filters". We will discuss them in the Advanced Data Mapper articles, found in the Data Mapper section of the Knowledge Base - link.

New file versions will be scanned automatically by the table that was created. To get the data from the file versions that are already uploaded to DataRails’ FileBox, you should rescan them.

• To do so, open the Workspace and open the FileBox details. 

• Select the file versions you would like to process and database by checking the checkbox, then click on the rescan  icon. 

• A pop-up table with the optional tables to scan will open, choose the desired tables and click on “Rescan”. 
• 

A green checkmark  will be seen to the right, indicating that the tables were extracted successfully from that version.

If an error icon    appears instead, it means that the system could not extract some or all of the tables (respectfully) from that version. Clicking on the icons will prompt a pop-up window stating the issue at hand (Status). You can click on the table’s name to get additional information regarding the cells that were not found. Make sure you are looking for the errors under the relevant sheet of the Excel workbook. 

New versions that will be uploaded to the FileBox will be scanned automatically and their data will appear in the table you mapped.