Overview
The Format Tool in Savant allows you to dynamically modify your data and schema within your tables. It is particularly useful for:
Specifying column names when they are not present.
Dynamically changing data types.
Hiding and renaming columns.
This tool provides flexibility in working with data that might have inconsistent formatting or schema issues, making it easier to prepare your data for analysis.
Example
Example 1: Setting Column Names
Imagine you have an Excel spreadsheet where the table headers are located in the second row. Using the Format Tool, you can specify that the column names should be taken from the second row.
Original Table:
| A | B | C |
1 |
|
|
|
2 | Name | Age | Date |
3 | John | 23 | 2023-01-01 |
4 | Alice | 30 | 2023-02-15 |
After using the Format Tool to specify that the column names should be taken from the second row:
Transformed Table:
Name | Age | Date |
John | 23 | 2023-01-01 |
Alice | 30 | 2023-02-15 |
Example 2: Changing Data Types and Renaming Columns
Consider a dataset where some columns contain date values but are not formatted as dates. With the Format Tool, you can dynamically change the data type of these columns to "Date". Additionally, you can rename these columns for better clarity.
Configuration
Replace Field Names from Row
Select the checkbox "Replace field names from row".
Specify the row number that contains the headers in the newly available "Row" box.
Configure Format Steps
In the "Format Steps" section, click "Add Step".
Follow the prompts to configure the step:
For fields that: Select "Contain", "End with", or "Starts with".
Enter text: Specify the text that the field names should contain, end with, or start with.
Select action: Choose one of the following:
Change Type: Change the data type to one of Savant's supported data types (Boolean, Date, DateTime, Integer, Number, or Text).
Hide: Hide the specified columns.
Rename: Rename the specified columns.
Detailed Configuration Options
When configuring format steps, you have multiple options to tailor the formatting to your needs:
Change Type:
Select this option to change the data type of columns that match your criteria.
Available data types include Boolean, Date, DateTime, Integer, Number, or Text.
Hide:
Select this option to hide columns that match your criteria.
Hidden columns will not be displayed in the resulting dataset.
Rename:
Select this option to rename columns that match your criteria.
You can specify whether the renaming should occur for columns that start with, end with, or contain the specified text.
For the "Replace" option, enter the text you want to replace and the new text to replace it with.
Additional Tips
Multiple Headers: If your dataset contains multiple header rows or complex structures, consider using multiple Format Tool instances to sequentially refine the data.