BETA
Bulk decodes (i.e., changes field values) of multiple fields at the same time, reading all source and target values from an excel spreadsheet.
A decode creates a copy of an original field and maps values in the source field to a new set of values. It is possible to do a Many to One mapping - ie,. values Men, M and Male all decode to MALE.
Method
| Key | Value(s) | Description |
|---|---|---|
| method | BulkDeCode | Decodes multiple fields, reading source and target values from an excel spreadsheet. |
| project | ||
| filename | "%DATAPATH%FolderName/Filename.xlsx | File containing Decode details |
| renameSuffix | "_source" | suffix string to attach to existing field name |
| newSuffix | "_decode" | suffix string to be appended to name of new decode field |
| hideSource | true/false | Optional. Default = false |
| startRow | 2 | Optional. Row number of first data row. Default is 2. |
| keepUncoded | true/false | Optional. Default = false. If false, source values that don't have a specified decode value will be mapped to null. If true, source values that don't have a specified decode value will be mapped to the source value. |
| onErrorIgnore | true/false | if true, will process whole file, gathering list of all fields where there were decode issues |
| errorOnRename | true/false | v 5.6.2 onwards Default = false If true, and BulkDecode encounters a field that has already had a source field created (e.g., [Gender] has been decoded already, and [Gender_source] already exists), Gender would be over-written to include the new decode, but Gender_source would not be affected. |
| skipIfExistingSource | true/false | v 5.6.2 onwards Default = false If the decode source field already exists, the decode will be skipped and the process will move onto the next item. For example, if [Gender] has already been decoded for values M and F, and therefore [Gender_source] already exists, If BulkDecode encounters [Gender]=U -> Unknown, and skipIfExistingSource = true, then no attempt would be made to decode the U value. |
File Layout
Bulk Decodes will use the first sheet in the workbook.
| A / 1 | Source Table Name - case sensitive |
| B / 2 | Source Field Name - case sensitive |
| C / 3 | Source Field Value |
| D / 4 | Target Field Value |
NOTE: Bulk Decodes will fail with "Not enough rows or columns" if there are more than 4 columns in Sheet 0.
TODO: Do all values for a field need to be grouped together in the file?
Examples
{
"method": "BulkDecode",
"project": "",
"filename": "",
"renameSuffix": "",
"newSuffix": "",
"startRow": 0,
"hideSource": false,
"keepUncoded": false,
"errorOnRename": false,
"skipIfExistingSource": false,
"reverseScriptPath": ""
}{
"method": "BulkDeCodeFields",
"filename": "%DATAPATH%BulkDecode/BulkDecodeFile.xlsx",
"renameSuffix": "_source",
"hideSource": true,
"project": "BulkDecode"
}{
"method": "BulkDeCodeFields",
"filename": "%DATAPATH%BulkDecode/BulkDecodeFile.xlsx",
"renameSuffix": "_source",
"newSuffix": "_decoded",
"project": "BulkDecode"
}