Normalization
boomxl automatically normalize the data during upload. boomxl data normalization is flexible enough to be custom implemented for specific columns in the uploaded file.
Below are the sample to normalize column data automatically and help you save countless number of hours cleaning/ normalizing data. boomxl normalize and clean data for you so that you can focus work that matter most to you and grow in your role.
date
date is inbuilt normalization that allows you to format the dates to unified format as per your business standards. You have to define the format you expects and boomxl will try to format that column value as per format defined by you. In the "type" key you need to pass "date" value and in "format" key, you need to pass the format that you expects.
columns:[{
name:"some_name",
label:"some human readable label",
normalize:{
type: "date",
format: "MM/DD/YYYY"
}
}]
}
Below is the table that represents the Keyword for formatting and its result
| Format Name | Result | Example |
|---|---|---|
| YYYY | 4 digit year | 2021 |
| YY | 2 digit year | 21 |
| M | Month | 1 2 .. 11 12 |
| MM | Month padded with 0 | 01 02 .. 11 12 |
| MMM | 3 character month name | Jan Feb .. Nov Dec |
| MMMM | Month name | January February .. December |
| D | Day of month | 1 2 .. 30 31 |
| DD | Day of month padded with 0 | 01 02 .. 30 31 |
| Do | Day of month | 1st 2nd .. 30th 31st |
| h | Hour | 0 1 .. 22 23 |
| hh | Hour with padded 0 | 00 01 .. 22 23 |
| m | Minutes | 0 1 .. 58 59 |
| mm | Minutes with padded 0 | 00 01 .. 58 59 |
| s | Seconds | 0 1 .. 58 59 |
| ss | Seconds with padded 0 | 00 01 .. 58 59 |
| A | AM/PM | AM |
| a | am/pm | pm |
custom
You can create your own formats using custom normalization. Use the defined formatting keywords and create formatting of your own choice, just pass the "custom" string in type key and your custom formatting in format key. The below formatting will convert telephone numbers to US format, like 2223334444 will be converted to +1 (222) 333 4444
columns:[{
name:"some_name",
label:"some human readable label",
normalize:{
type: "custom",
format: "+1 (000) 000 0000"
}
}]
}
Below is the table that represents the Keyword for formatting
| Character | Description |
|---|---|
| 0 | Any single Number |
| 9 | Any single Optional Number |
| A | Any single Alphanumeric character |
| S | Any single String character |
| u | To convert single character to UPPER case |
| U | To convert all characters to UPPER case |
| l | To convert single character to lower case |
| L | To convert all characters to lower case |
| / | Escape special formatting character |
| # | Recursive pattern matching, like if you put u# - and the input value is "abcdef" then it will apply "u" formatting for first position and print rest of characters, so the output will be "Abcdef" |
Below are some sample examples that will help you in building your own custom formatter
| Column Value | Formatting | Output |
|---|---|---|
| 2223334444 | +1 (000) 000 0000 | +1 (222) 333 4444 |
| 2223334444 | +1 000-000-0000 | +1 222-333-4444 |
| 2345 | 90.00% | 23.45% |
| 1234 | $# | $1234 |
| 999999 | 00 | 99 |
| 01202021 | 90/90/9900 | 01/20/2021 |
| abcd | uuuu | ABCD |
| abcd | u# | Abcd |
| abcd | U | ABCD |
| ABCD | llll | abcd |
| ABCD | L | abcd |
| ABCD | ll | ab |
| ABCD | l# | aBCD |