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
- First, name the table.
- If you wish to modify an existing table, you can access it by clicking on the drop-down menu next to “New Table”.
- Mapping options - Flexible / Fixed Structures
Once you name the table you will see the following mapping options:
- Flexible Structure – Header names will follow an ordered sequence but might not be in the same column as before (New columns may be added in between the existing columns).
- Fixed Structure - Header names will be in the same order throughout versions. This type of structure has a shorter processing time.
- You may choose which sheet or sheets (Excel tabs) to map. This option allows you to specify the relevant sheets to map and save processing time by scanning the relevant ones only.
- Click on “Add Structure” to map the area in which our relevant information resides (rows/columns/cells).
- Structure Role Types
The following list specifies the types of roles you can assign to a cell/range in a worksheet:
- 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.
- 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.
- 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.
- 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.
- 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.
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.