Data Cleanup in OpenRefine - smith-special-collections/sc-documentation GitHub Wiki

Data Cleanup Scripts

Overview

Below you will find various scripts that allow automating many steps in cleaning up legacy container inventories in OpenRefine. The description of each script is in three parts:

  1. Requirements: Your data must meet these requirements (usually a set of columns with specific names) in order for the script to run properly. If you have other columns in the OpenRefine project they will not be altered by the script and, except where noted, they will not cause problems with the script.
  2. Steps: This shows the steps that the script carries out. Do not actually follow these steps! That's what the script is for.
  3. JSON: This is the script that should be pasted into the box created when you click "Apply..." in OpenRefine under Undo/Redo.

Contents

Flattening file levels in SSC accession inventories

Requirements

Date is in 2 columns

  1. Title/Date 1
  2. Title/Date 2

No titles are in box or folder columns (if they exist)

Steps

  1. Edit Columns > Add column based on column Title/Date 1 with expression value (column name Temp Title/Date 1)
  2. Fill down column Title/Date 1
  3. Facet out non-duplicates in column Title/Date 1
  4. Facet out blanks in column Temp Title/Date 1
  5. Facet out non-blanks in column Title/Date 2
  6. Remove matching rows
  7. Remove column Temp Title/Date 1
  8. Facet out blanks in column Title/Date 2
  9. Edit Cells > Transform on column Title/Date 1 with expression value + " -- " + cells['Title/Date 2'].value
  10. Remove column Title/Date 2
  11. Rename column Title/Date 1 to Title/Date

JSON

[ { "op": "core/column-addition", "description": "Create column Temp Title/Date 1 at index 2 based on column Title/Date 1 using expression grel:value", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Temp Title/Date 1", "columnInsertIndex": 2, "baseColumnName": "Title/Date 1", "expression": "grel:value", "onError": "set-to-blank" }, { "op": "core/fill-down", "description": "Fill down cells in column Title/Date 1", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Title/Date 1" }, { "op": "core/row-removal", "description": "Remove rows", "engineConfig": { "facets": [ { "type": "list", "name": "Title/Date 1", "expression": "(facetCount(value, 'value', 'Title/Date 1') > 1).toString()", "columnName": "Title/Date 1", "invert": false, "selection": [ { "v": { "v": "true", "l": "true" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false }, { "type": "list", "name": "Title/Date 2", "expression": "isBlank(value).toString()", "columnName": "Title/Date 2", "invert": false, "selection": [ { "v": { "v": "true", "l": "true" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false }, { "type": "list", "name": "Temp Title/Date 1", "expression": "isBlank(value).toString()", "columnName": "Temp Title/Date 1", "invert": false, "selection": [ { "v": { "v": "false", "l": "false" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false } ], "mode": "row-based" } }, { "op": "core/column-removal", "description": "Remove column Temp Title/Date 1", "columnName": "Temp Title/Date 1" }, { "op": "core/text-transform", "description": "Text transform on cells in column Title/Date 1 using expression grel:value + \" -- \" + cells['Title/Date 2'].value", "engineConfig": { "facets": [ { "type": "list", "name": "Title/Date 2", "expression": "isBlank(value).toString()", "columnName": "Title/Date 2", "invert": false, "selection": [ { "v": { "v": "false", "l": "false" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false } ], "mode": "row-based" }, "columnName": "Title/Date 1", "expression": "grel:value + \" -- \" + cells['Title/Date 2'].value", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/column-removal", "description": "Remove column Title/Date 2", "columnName": "Title/Date 2" }, { "op": "core/column-rename", "description": "Rename column Title/Date 1 to Title/Date", "oldColumnName": "Title/Date 1", "newColumnName": "Title/Date" } ]

Extract dates from mixed title/date field

Requirements

Data is in column Title/Date

Steps

  1. Edit Cells > Transform on column Title/Date with expression value.replace("n.d.","undated").replace("no date","undated")
  2. Edit Columns > Split into multiple columns with separator (regular expression and only split into 2 columns): [,][ ](?=[\[\(c]*[a]*[.]*[ ]*[0-9]{1})|[,][ ](?=[u][n][d][a][t][e][d])|[,][ ](?=[A-Za-z]{3}[.]*[ ][0-9]{2})
  3. Rename column Title/Date 1 to Title
  4. Rename column Title/Date 2 to Date Expression

JSON

[ { "op": "core/text-transform", "description": "Text transform on cells in column Title/Date using expression grel:value.replace(\"n.d.\",\"undated\").replace(\"no date\",\"undated\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Title/Date", "expression": "grel:value.replace(\"n.d.\",\"undated\").replace(\"no date\",\"undated\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/column-split", "description": "Split column Title/Date by separator", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Title/Date", "guessCellType": false, "removeOriginalColumn": true, "mode": "separator", "separator": "[,][ ](?=[\\[\\(c]*[a]*[.]*[ ]*[0-9]{1})|[,][ ](?=[u][n][d][a][t][e][d])|[,][ ](?=[A-Za-z]{3}[.]*[ ][0-9]{2})", "regex": true, "maxColumns": 2 }, { "op": "core/column-rename", "description": "Rename column Title/Date 1 to Title", "oldColumnName": "Title/Date 1", "newColumnName": "Title" }, { "op": "core/column-rename", "description": "Rename column Title/Date 2 to Date Expression", "oldColumnName": "Title/Date 2", "newColumnName": "Date Expression" } ]

Split Date Expression into Begin and End

Requirements

Data is in column Date Expression

Steps

  1. Edit Cells > Transform on column Date Expression with expression value.replace(/-$/,"-?").replace("-present","-?")
  2. Edit Cells > Transform on column Date Expression with expression value.replace("c.a.","approximately").replace("c.","approximately").replace("ca.","approximately").replace("Deapproximately","Dec").replace("circa","approximately")
  3. Edit Cells > Transform on column Date Expression with expression value.replace(".","")
  4. Edit Columns > Add column based on column Date Expression with expression value (column name Temp Date Expression)
  5. Edit Cells > Transform on column Temp Date Expression with expression value.replace(", undated","").replace("undated","").replace("approximately","").replace("Fall","").replace("Winter","").replace("Spring","").replace("Summer","")
  6. Trim leading and trailing whitespace on column Temp Date Expression
  7. Text filter (regular expression) on column Temp Date Expression ^\d{4}s$
  8. Edit Cells > Transform on column Temp Date Expression with expression value.replace("s","-") + value.replace("0s","9")
  9. Edit Columns > Split into multiple columns with separator - on Temp Date Expression (only split into 2 columns)
  10. Rename Temp Date Expression 1 to Date Begin
  11. Rename Temp Date Expression 2 to Date End
  12. Text filter on Date End 0s
  13. Edit Cells > Transform on column Date Begin with expression value.replace("0s","0")
  14. Edit Cells > Transform on column Date End with expression value.replace("0s","9")
  15. Text filter (regular expression) on Date Begin ^19\d{2}
  16. Text filter (regular expression) on Date End ^\d{2}$
  17. Edit Cells > Transform on column Date End with expression "19" + value
  18. Text filter (regular expression) on Date Begin ^20\d{2}
  19. Text filter (regular expression) on Date End ^\d{2}$
  20. Edit Cells > Transform on column Date End with expression "20" + value

JSON

[ { "op": "core/text-transform", "description": "Text transform on cells in column Date Expression using expression grel:value.replace(/-$/,\"-?\").replace(\"-present\",\"-?\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Date Expression", "expression": "grel:value.replace(/-$/,\"-?\").replace(\"-present\",\"-?\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Expression using expression grel:value.replace(\"c.a.\",\"approximately\").replace(\"c.\",\"approximately\").replace(\"ca.\",\"approximately\").replace(\"Deapproximately\",\"Dec\").replace(\"circa\",\"approximately\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Date Expression", "expression": "grel:value.replace(\"c.a.\",\"approximately\").replace(\"c.\",\"approximately\").replace(\"ca.\",\"approximately\").replace(\"Deapproximately\",\"Dec\").replace(\"circa\",\"approximately\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Expression using expression grel:value.replace(\".\",\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Date Expression", "expression": "grel:value.replace(\".\",\"\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/column-addition", "description": "Create column Temp Date Expression at index 3 based on column Date Expression using expression grel:value", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Temp Date Expression", "columnInsertIndex": 3, "baseColumnName": "Date Expression", "expression": "grel:value", "onError": "set-to-blank" }, { "op": "core/text-transform", "description": "Text transform on cells in column Temp Date Expression using expression grel:value.replace(\", undated\",\"\").replace(\"undated\",\"\").replace(\"approximately\",\"\").replace(\"Fall\",\"\").replace(\"Winter\",\"\").replace(\"Spring\",\"\").replace(\"Summer\",\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Temp Date Expression", "expression": "grel:value.replace(\", undated\",\"\").replace(\"undated\",\"\").replace(\"approximately\",\"\").replace(\"Fall\",\"\").replace(\"Winter\",\"\").replace(\"Spring\",\"\").replace(\"Summer\",\"\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Temp Date Expression using expression value.trim()", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Temp Date Expression", "expression": "value.trim()", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Temp Date Expression using expression grel:value.replace(\"s\",\"-\") + value.replace(\"0s\",\"9\")", "engineConfig": { "facets": [ { "name": "Temp Date Expression", "columnName": "Temp Date Expression", "query": "^\\d{4}s$", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Temp Date Expression", "expression": "grel:value.replace(\"s\",\"-\") + value.replace(\"0s\",\"9\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/column-split", "description": "Split column Temp Date Expression by separator", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Temp Date Expression", "guessCellType": false, "removeOriginalColumn": true, "mode": "separator", "separator": "-", "regex": false, "maxColumns": 2 }, { "op": "core/column-rename", "description": "Rename column Temp Date Expression 1 to Date Begin", "oldColumnName": "Temp Date Expression 1", "newColumnName": "Date Begin" }, { "op": "core/column-rename", "description": "Rename column Temp Date Expression 2 to Date End", "oldColumnName": "Temp Date Expression 2", "newColumnName": "Date End" }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.replace(\"0s\",\"0\")", "engineConfig": { "facets": [ { "name": "Date End", "columnName": "Date End", "query": "0s", "mode": "text", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.replace(\"0s\",\"0\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.replace(\"0s\",\"9\")", "engineConfig": { "facets": [ { "name": "Date End", "columnName": "Date End", "query": "0s", "mode": "text", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.replace(\"0s\",\"9\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:\"19\" + value", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^19\\d{2}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\d{2}$", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:\"19\" + value", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:\"20\" + value", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^20\\d{2}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\d{2}$", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:\"20\" + value", "onError": "keep-original", "repeat": false, "repeatCount": 10 } ]

Change dates to standard format

Requirements

Data is in columns Date Begin and Date End

Note: Currently only changes dates in following formats

  • Jan 22, 1980
  • January 22, 1980
  • 1980 Jan 22
  • 1980 January 22
  • 22 Jan 1980
  • 22 January 1980
  • 1/22/1980
  • Jan 1980
  • January 1980
  • 1980 Jan
  • 1980 January
  • 1/1980

Steps (done to each column)

  1. Edit Cells > Transform with expression value.replace("January","Jan").replace("February","Feb").replace("March","Mar").replace("April","Apr").replace("June","Jun").replace("July","Jul").replace("August","Aug").replace("September","Sep").replace("October","Oct").replace("November","Nov").replace("December","Dec")
  2. Text filter (regular expression) ^\d+ \w{3} \d{4}
  3. Edit Cells > Transform with expression value.toDate("dd MMM yyyy").toString("yyyy-MM-dd")
  4. Text filter (regular expression) ^\w{3} \d{4}
  5. Edit Cells > Transform with expression value.toDate("MMM yyyy").toString("yyyy-MM")
  6. Text filter (regular expression) ^\w{3} \d+, \d{4}
  7. Edit Cells > Transform with expression value.toDate("MMM dd, yyyy").toString("yyyy-MM-dd")
  8. Text filter (regular expression) ^\d{4} \w{3} \d+
  9. Edit Cells > Transform with expression value.toDate("yyyy MMM dd").toString("yyyy-MM-dd")
  10. Text filter (regular expression) ^\d{4} \w{3}
  11. Edit Cells > Transform with expression value.toDate("yyyy MMM").toString("yyyy-MM")
  12. Text filter (regular expression) ^\d{1,2}/\d{1,2}/\d{4}
  13. Edit Cells > Transform with expression value.toDate("MM/dd/yyyy").toString("yyyy-MM-dd")
  14. Text filter (regular expression) ^\d{1,2}/\d{4}
  15. Edit Cells > Transform with expression value.toDate("MM/yyyy").toString("yyyy-MM")

JSON

[ { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.replace(\"January\",\"Jan\").replace(\"February\",\"Feb\").replace(\"March\",\"Mar\").replace(\"April\",\"Apr\").replace(\"June\",\"Jun\").replace(\"July\",\"Jul\").replace(\"August\",\"Aug\").replace(\"September\",\"Sep\").replace(\"October\",\"Oct\").replace(\"November\",\"Nov\").replace(\"December\",\"Dec\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.replace(\"January\",\"Jan\").replace(\"February\",\"Feb\").replace(\"March\",\"Mar\").replace(\"April\",\"Apr\").replace(\"June\",\"Jun\").replace(\"July\",\"Jul\").replace(\"August\",\"Aug\").replace(\"September\",\"Sep\").replace(\"October\",\"Oct\").replace(\"November\",\"Nov\").replace(\"December\",\"Dec\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.replace(\"January\",\"Jan\").replace(\"February\",\"Feb\").replace(\"March\",\"Mar\").replace(\"April\",\"Apr\").replace(\"June\",\"Jun\").replace(\"July\",\"Jul\").replace(\"August\",\"Aug\").replace(\"September\",\"Sep\").replace(\"October\",\"Oct\").replace(\"November\",\"Nov\").replace(\"December\",\"Dec\")", "engineConfig": { "facets": [], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.replace(\"January\",\"Jan\").replace(\"February\",\"Feb\").replace(\"March\",\"Mar\").replace(\"April\",\"Apr\").replace(\"June\",\"Jun\").replace(\"July\",\"Jul\").replace(\"August\",\"Aug\").replace(\"September\",\"Sep\").replace(\"October\",\"Oct\").replace(\"November\",\"Nov\").replace(\"December\",\"Dec\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"dd MMM yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\d+ \\w{3} \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"dd MMM yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"dd MMM yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date End", "columnName": "Date End", "query": "^\\d+ \\w{3} \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"dd MMM yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"MMM yyyy\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\w{3} \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"MMM yyyy\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"MMM yyyy\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\w{3} \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"MMM yyyy\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"MMM dd, yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\w{3} \\d+, \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"MMM dd, yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"MMM dd, yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\w{3} \\d+, \\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"MMM dd, yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"yyyy MMM dd\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\d{4} \\w{3} \\d+", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"yyyy MMM dd\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"yyyy MMM dd\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\d{4} \\w{3} \\d+", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"yyyy MMM dd\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"yyyy MMM\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\d{4} \\w{3}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"yyyy MMM\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"yyyy MMM\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" }, { "name": "Date End", "columnName": "Date End", "query": "^\\d{4} \\w{3}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"yyyy MMM\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"MM/dd/yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\d{1,2}/\\d{1,2}/\\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"MM/dd/yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"MM/dd/yyyy\").toString(\"yyyy-MM-dd\")", "engineConfig": { "facets": [ { "name": "Date End", "columnName": "Date End", "query": "^\\d{1,2}/\\d{1,2}/\\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"MM/dd/yyyy\").toString(\"yyyy-MM-dd\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date Begin using expression grel:value.toDate(\"MM/yyyy\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date Begin", "columnName": "Date Begin", "query": "^\\d{1,2}/\\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date Begin", "expression": "grel:value.toDate(\"MM/yyyy\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 }, { "op": "core/text-transform", "description": "Text transform on cells in column Date End using expression grel:value.toDate(\"MM/yyyy\").toString(\"yyyy-MM\")", "engineConfig": { "facets": [ { "name": "Date End", "columnName": "Date End", "query": "^\\d{1,2}/\\d{4}", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "columnName": "Date End", "expression": "grel:value.toDate(\"MM/yyyy\").toString(\"yyyy-MM\")", "onError": "keep-original", "repeat": false, "repeatCount": 10 } ]

Add additional columns for Excel importer (box inventory)

Requirements

Data is in exactly 5 columns

  1. Box
  2. Title
  3. Date Expression
  4. Date Begin
  5. Date End

Steps

  1. Text filter on column Date Expression with regular expression approximately|\?
  2. Edit Columns > Add column based on column Date Expression with expression value.replace(value,"Approximate") (column name Date Certainty)
  3. Facet Date Expression for non-blanks
  4. Facet Date End for blanks
  5. Edit Columns > Add column based on column Date End with expression "Single" (column name Dates Type)
  6. Move column Title to position 0
  7. Edit Columns > Add column based on column Title with expression value.replace(value,"") (column name Component Unique Identifier)
  8. Edit Columns > Add column based on column Component Unique Identifier with expression "2" (column name Hierarchical Relationship)
  9. Edit Columns > Add column based on column Hierarchical Relationship with expression "File" (column name Description Level)
  10. Edit Columns > Add column based on column Description Level with expression value.replace(value,"") (column name Publish?)
  11. Edit Columns > Add column based on column Publish? with expression value.replace(value,"") (column name Restrictions Apply?)
  12. Move column Date Begin to position 6
  13. Move column Date End to position 7
  14. Move column Dates Type to position 8
  15. Move column Date Expression to position 9
  16. Move column Date Certainty to position 10
  17. Edit Columns > Add column based on column Date Certainty with expression value.replace(value,"") (column name Extent portion)
  18. Edit Columns > Add column based on column Extent portion with expression value.replace(value,"") (column name Extent number)
  19. Edit Columns > Add column based on column Extent number with expression value.replace(value,"") (column name Extent type)
  20. Edit Columns > Add column based on column Extent type with expression value.replace(value,"") (column name Container Summary)
  21. Edit Columns > Add column based on column Container Summary with expression value.replace(value,"") (column name Physical Details)
  22. Edit Columns > Add column based on column Physical Details with expression value.replace(value,"") (column name Dimensions)
  23. Edit Columns > Add column based on column Dimensions with expression "mixed_materials" (column name Container Instance Type)
  24. Edit Columns > Add column based on column Container Instance Type with expression "box" (column name Top Container type)
  25. Rename column Box to Top Container [indicator]
  26. Edit Columns > Add column based on column Top Container [indicator] with expression value.replace(value,"") (column name barcode)
  27. Edit Columns > Add column based on column Description Level with expression value.replace(value,"") (column name Other Level)
  28. Edit Columns > Add column based on column Restrictions Apply? with expression value.replace(value,"") (column name Processing Note)
  29. Edit Columns > Add column based on column Processing Note with expression null (column name Dates Label)

JSON

[ { "op": "core/column-addition", "description": "Create column Date Certainty at index 3 based on column Date Expression using expression grel:value.replace(value,\"Approximate\")", "engineConfig": { "facets": [ { "name": "Date Expression", "columnName": "Date Expression", "query": "approximately|\\?", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "newColumnName": "Date Certainty", "columnInsertIndex": 3, "baseColumnName": "Date Expression", "expression": "grel:value.replace(value,\"Approximate\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dates Type at index 6 based on column Date End using expression grel:\"Single\"", "engineConfig": { "facets": [ { "type": "list", "name": "Date End", "expression": "isBlank(value).toString()", "columnName": "Date End", "invert": false, "selection": [ { "v": { "v": "true", "l": "true" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false }, { "type": "list", "name": "Date Expression", "expression": "isBlank(value).toString()", "columnName": "Date Expression", "invert": false, "selection": [ { "v": { "v": "false", "l": "false" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false } ], "mode": "row-based" }, "newColumnName": "Dates Type", "columnInsertIndex": 6, "baseColumnName": "Date End", "expression": "grel:\"Single\"", "onError": "set-to-blank" }, { "op": "core/column-move", "description": "Move column Title to position 0", "columnName": "Title", "index": 0 }, { "op": "core/column-addition", "description": "Create column Component Unique Identifier at index 1 based on column Title using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Component Unique Identifier", "columnInsertIndex": 1, "baseColumnName": "Title", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Hierarchical Relationship at index 2 based on column Component Unique Identifier using expression grel:\"2\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Hierarchical Relationship", "columnInsertIndex": 2, "baseColumnName": "Component Unique Identifier", "expression": "grel:\"2\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Description Level at index 3 based on column Hierarchical Relationship using expression grel:\"File\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Description Level", "columnInsertIndex": 3, "baseColumnName": "Hierarchical Relationship", "expression": "grel:\"File\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Publish? at index 4 based on column Description Level using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Publish?", "columnInsertIndex": 4, "baseColumnName": "Description Level", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Restrictions Apply? at index 5 based on column Publish? using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Restrictions Apply?", "columnInsertIndex": 5, "baseColumnName": "Publish?", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-move", "description": "Move column Date Begin to position 9", "columnName": "Date Begin", "index": 9 }, { "op": "core/column-move", "description": "Move column Date Begin to position 8", "columnName": "Date Begin", "index": 8 }, { "op": "core/column-move", "description": "Move column Date Begin to position 7", "columnName": "Date Begin", "index": 7 }, { "op": "core/column-move", "description": "Move column Date Begin to position 6", "columnName": "Date Begin", "index": 6 }, { "op": "core/column-move", "description": "Move column Date End to position 10", "columnName": "Date End", "index": 10 }, { "op": "core/column-move", "description": "Move column Date End to position 9", "columnName": "Date End", "index": 9 }, { "op": "core/column-move", "description": "Move column Date End to position 8", "columnName": "Date End", "index": 8 }, { "op": "core/column-move", "description": "Move column Date End to position 7", "columnName": "Date End", "index": 7 }, { "op": "core/column-move", "description": "Move column Dates Type to position 11", "columnName": "Dates Type", "index": 11 }, { "op": "core/column-move", "description": "Move column Dates Type to position 10", "columnName": "Dates Type", "index": 10 }, { "op": "core/column-move", "description": "Move column Dates Type to position 9", "columnName": "Dates Type", "index": 9 }, { "op": "core/column-move", "description": "Move column Dates Type to position 8", "columnName": "Dates Type", "index": 8 }, { "op": "core/column-move", "description": "Move column Date Expression to position 10", "columnName": "Date Expression", "index": 10 }, { "op": "core/column-move", "description": "Move column Date Expression to position 9", "columnName": "Date Expression", "index": 9 }, { "op": "core/column-move", "description": "Move column Date Certainty to position 11", "columnName": "Date Certainty", "index": 11 }, { "op": "core/column-move", "description": "Move column Date Certainty to position 10", "columnName": "Date Certainty", "index": 10 }, { "op": "core/column-addition", "description": "Create column Extent portion at index 11 based on column Date Certainty using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent portion", "columnInsertIndex": 11, "baseColumnName": "Date Certainty", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Extent number at index 12 based on column Extent portion using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent number", "columnInsertIndex": 12, "baseColumnName": "Extent portion", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Extent type at index 13 based on column Extent number using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent type", "columnInsertIndex": 13, "baseColumnName": "Extent number", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Container Summary at index 14 based on column Extent type using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Container Summary", "columnInsertIndex": 14, "baseColumnName": "Extent type", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Physical Details at index 15 based on column Container Summary using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Physical Details", "columnInsertIndex": 15, "baseColumnName": "Container Summary", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dimensions at index 16 based on column Physical Details using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Dimensions", "columnInsertIndex": 16, "baseColumnName": "Physical Details", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Container Instance Type at index 17 based on column Dimensions using expression grel:\"mixed_materials\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Container Instance Type", "columnInsertIndex": 17, "baseColumnName": "Dimensions", "expression": "grel:\"mixed_materials\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Top Container type at index 18 based on column Container Instance Type using expression grel:\"box\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Top Container type", "columnInsertIndex": 18, "baseColumnName": "Container Instance Type", "expression": "grel:\"box\"", "onError": "set-to-blank" }, { "op": "core/column-rename", "description": "Rename column Box to Top Container [indicator]", "oldColumnName": "Box", "newColumnName": "Top Container [indicator]" }, { "op": "core/column-addition", "description": "Create column barcode at index 20 based on column Top Container [indicator] using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "barcode", "columnInsertIndex": 20, "baseColumnName": "Top Container [indicator]", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Other Level at index 4 based on column Description Level using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Other Level", "columnInsertIndex": 4, "baseColumnName": "Description Level", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Processing Note at index 7 based on column Restrictions Apply? using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Processing Note", "columnInsertIndex": 7, "baseColumnName": "Restrictions Apply?", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dates Label at index 8 based on column Processing Note using expression grel:null", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Dates Label", "columnInsertIndex": 8, "baseColumnName": "Processing Note", "expression": "grel:null", "onError": "set-to-blank" } ]

Add additional columns for Excel importer (box/folder inventory) [Probably could be combined with above]

Requirements

Data is in exactly 6 columns

  1. Box
  2. Folder
  3. Title
  4. Date Expression
  5. Date Begin
  6. Date End

Steps

  1. Text filter on column Date Expression with regular expression approximately|\?
  2. Edit Columns > Add column based on column Date Expression with expression value.replace(value,"Approximate") (column name Date Certainty)
  3. Facet Date Expression for non-blanks
  4. Facet Date End for blanks
  5. Edit Columns > Add column based on column Date End with expression "Single" (column name Dates Type)
  6. Move column Title to position 0
  7. Edit Columns > Add column based on column Title with expression value.replace(value,"") (column name Component Unique Identifier)
  8. Edit Columns > Add column based on column Component Unique Identifier with expression "2" (column name Hierarchical Relationship)
  9. Edit Columns > Add column based on column Hierarchical Relationship with expression "File" (column name Description Level)
  10. Edit Columns > Add column based on column Description Level with expression value.replace(value,"") (column name Publish?)
  11. Edit Columns > Add column based on column Publish? with expression value.replace(value,"") (column name Restrictions Apply?)
  12. Move column Date Begin to position 6
  13. Move column Date End to position 7
  14. Move column Dates Type to position 8
  15. Move column Date Expression to position 9
  16. Move column Date Certainty to position 10
  17. Edit Columns > Add column based on column Date Certainty with expression value.replace(value,"") (column name Extent portion)
  18. Edit Columns > Add column based on column Extent portion with expression value.replace(value,"") (column name Extent number)
  19. Edit Columns > Add column based on column Extent number with expression value.replace(value,"") (column name Extent type)
  20. Edit Columns > Add column based on column Extent type with expression value.replace(value,"") (column name Container Summary)
  21. Edit Columns > Add column based on column Container Summary with expression value.replace(value,"") (column name Physical Details)
  22. Edit Columns > Add column based on column Physical Details with expression value.replace(value,"") (column name Dimensions)
  23. Edit Columns > Add column based on column Dimensions with expression "mixed_materials" (column name Container Instance Type)
  24. Edit Columns > Add column based on column Container Instance Type with expression "box" (column name Top Container type)
  25. Rename column Box to Top Container [indicator]
  26. Edit Columns > Add column based on column Top Container [indicator] with expression value.replace(value,"") (column name barcode)
  27. Facet out blanks on column Folder
  28. Edit Columns > Add column based on column barcode with expression "folder" (column name Child type)
  29. Rename column Folder to Child indicator
  30. Edit Columns > Add column based on column Description Level with expression value.replace(value,"") (column name Other Level)
  31. Edit Columns > Add column based on column Restrictions Apply? with expression value.replace(value,"") (column name Processing Note)
  32. Edit Columns > Add column based on column Processing Note with expression null (column name Dates Label)

JSON

[ { "op": "core/column-addition", "description": "Create column Date Certainty at index 4 based on column Date Expression using expression grel:value.replace(value,\"Approximate\")", "engineConfig": { "facets": [ { "name": "Date Expression", "columnName": "Date Expression", "query": "approximately|\\?", "mode": "regex", "caseSensitive": false, "invert": false, "type": "text" } ], "mode": "row-based" }, "newColumnName": "Date Certainty", "columnInsertIndex": 4, "baseColumnName": "Date Expression", "expression": "grel:value.replace(value,\"Approximate\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dates Type at index 7 based on column Date End using expression grel:\"Single\"", "engineConfig": { "facets": [ { "type": "list", "name": "Date Expression", "expression": "isBlank(value).toString()", "columnName": "Date Expression", "invert": false, "selection": [ { "v": { "v": "false", "l": "false" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false }, { "type": "list", "name": "Date End", "expression": "isBlank(value).toString()", "columnName": "Date End", "invert": false, "selection": [ { "v": { "v": "true", "l": "true" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false } ], "mode": "row-based" }, "newColumnName": "Dates Type", "columnInsertIndex": 7, "baseColumnName": "Date End", "expression": "grel:\"Single\"", "onError": "set-to-blank" }, { "op": "core/column-move", "description": "Move column Title to position 0", "columnName": "Title", "index": 0 }, { "op": "core/column-addition", "description": "Create column Component Unique Identifier at index 1 based on column Title using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Component Unique Identifier", "columnInsertIndex": 1, "baseColumnName": "Title", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Hierarchical Relationship at index 2 based on column Component Unique Identifier using expression grel:\"2\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Hierarchical Relationship", "columnInsertIndex": 2, "baseColumnName": "Component Unique Identifier", "expression": "grel:\"2\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Description Level at index 3 based on column Hierarchical Relationship using expression grel:\"File\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Description Level", "columnInsertIndex": 3, "baseColumnName": "Hierarchical Relationship", "expression": "grel:\"File\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Publish? at index 4 based on column Description Level using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Publish?", "columnInsertIndex": 4, "baseColumnName": "Description Level", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Restrictions Apply? at index 5 based on column Publish? using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Restrictions Apply?", "columnInsertIndex": 5, "baseColumnName": "Publish?", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-move", "description": "Move column Date Begin to position 9", "columnName": "Date Begin", "index": 9 }, { "op": "core/column-move", "description": "Move column Date Begin to position 8", "columnName": "Date Begin", "index": 8 }, { "op": "core/column-move", "description": "Move column Date Begin to position 7", "columnName": "Date Begin", "index": 7 }, { "op": "core/column-move", "description": "Move column Date Begin to position 6", "columnName": "Date Begin", "index": 6 }, { "op": "core/column-move", "description": "Move column Date End to position 10", "columnName": "Date End", "index": 10 }, { "op": "core/column-move", "description": "Move column Date End to position 9", "columnName": "Date End", "index": 9 }, { "op": "core/column-move", "description": "Move column Date End to position 8", "columnName": "Date End", "index": 8 }, { "op": "core/column-move", "description": "Move column Date End to position 7", "columnName": "Date End", "index": 7 }, { "op": "core/column-move", "description": "Move column Dates Type to position 11", "columnName": "Dates Type", "index": 11 }, { "op": "core/column-move", "description": "Move column Dates Type to position 10", "columnName": "Dates Type", "index": 10 }, { "op": "core/column-move", "description": "Move column Dates Type to position 9", "columnName": "Dates Type", "index": 9 }, { "op": "core/column-move", "description": "Move column Dates Type to position 8", "columnName": "Dates Type", "index": 8 }, { "op": "core/column-move", "description": "Move column Date Expression to position 10", "columnName": "Date Expression", "index": 10 }, { "op": "core/column-move", "description": "Move column Date Expression to position 9", "columnName": "Date Expression", "index": 9 }, { "op": "core/column-move", "description": "Move column Date Certainty to position 11", "columnName": "Date Certainty", "index": 11 }, { "op": "core/column-move", "description": "Move column Date Certainty to position 10", "columnName": "Date Certainty", "index": 10 }, { "op": "core/column-addition", "description": "Create column Extent portion at index 11 based on column Date Certainty using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent portion", "columnInsertIndex": 11, "baseColumnName": "Date Certainty", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Extent number at index 12 based on column Extent portion using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent number", "columnInsertIndex": 12, "baseColumnName": "Extent portion", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Extent type at index 13 based on column Extent number using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Extent type", "columnInsertIndex": 13, "baseColumnName": "Extent number", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Container Summary at index 14 based on column Extent type using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Container Summary", "columnInsertIndex": 14, "baseColumnName": "Extent type", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Physical Details at index 15 based on column Container Summary using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Physical Details", "columnInsertIndex": 15, "baseColumnName": "Container Summary", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dimensions at index 16 based on column Physical Details using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Dimensions", "columnInsertIndex": 16, "baseColumnName": "Physical Details", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Container Instance Type at index 17 based on column Dimensions using expression grel:\"mixed_materials\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Container Instance Type", "columnInsertIndex": 17, "baseColumnName": "Dimensions", "expression": "grel:\"mixed_materials\"", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Top Container type at index 18 based on column Container Instance Type using expression grel:\"box\"", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Top Container type", "columnInsertIndex": 18, "baseColumnName": "Container Instance Type", "expression": "grel:\"box\"", "onError": "set-to-blank" }, { "op": "core/column-rename", "description": "Rename column Box to Top Container [indicator]", "oldColumnName": "Box", "newColumnName": "Top Container [indicator]" }, { "op": "core/column-addition", "description": "Create column barcode at index 20 based on column Top Container [indicator] using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "barcode", "columnInsertIndex": 20, "baseColumnName": "Top Container [indicator]", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Child type at index 21 based on column barcode using expression grel:\"folder\"", "engineConfig": { "facets": [ { "type": "list", "name": "Folder", "expression": "isBlank(value).toString()", "columnName": "Folder", "invert": false, "selection": [ { "v": { "v": "false", "l": "false" } } ], "selectNumber": false, "selectDateTime": false, "selectBoolean": false, "omitBlank": false, "selectBlank": false, "omitError": false, "selectError": false } ], "mode": "row-based" }, "newColumnName": "Child type", "columnInsertIndex": 21, "baseColumnName": "barcode", "expression": "grel:\"folder\"", "onError": "set-to-blank" }, { "op": "core/column-rename", "description": "Rename column Folder to Child indicator", "oldColumnName": "Folder", "newColumnName": "Child indicator" }, { "op": "core/column-addition", "description": "Create column Other Level at index 4 based on column Description Level using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Other Level", "columnInsertIndex": 4, "baseColumnName": "Description Level", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Processing Note at index 7 based on column Restrictions Apply? using expression grel:value.replace(value,\"\")", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Processing Note", "columnInsertIndex": 7, "baseColumnName": "Restrictions Apply?", "expression": "grel:value.replace(value,\"\")", "onError": "set-to-blank" }, { "op": "core/column-addition", "description": "Create column Dates Label at index 8 based on column Processing Note using expression grel:null", "engineConfig": { "facets": [], "mode": "row-based" }, "newColumnName": "Dates Label", "columnInsertIndex": 8, "baseColumnName": "Processing Note", "expression": "grel:null", "onError": "set-to-blank" } ]