The Worst Data Table So Far

This intentionally messy table is designed for UiPath Studio Web practice. Students should use Assign activities to clean individual values, then apply those cleaning patterns across rows in a spreadsheet workflow.

Suggested workflow: read this table into a spreadsheet or scrape it from the webpage, add cleaned-output columns, write cleaned values, and add an exception flag, review_comments (short error message) and routing_status (pending_review) for records that should not be automatically corrected.

Student Data Source

raw_order_id raw_customer_name raw_invoice_amount raw_order_date raw_quantity raw_email raw_state raw_status Notes (drop this column)
ORD-0001 annE SMITH $1,250.00 04/01/2026 10 units ANNE.SMITH@@example.com wa paid delivered on time
ord_0002 bob jones USD 987.50 2026-04-02 five bob.jones@example Washington PAID needs invoice copy
Order #0003 CARLA RIVERA 1,045 April 3, 2026 7 carla.rivera@example.com WA Pending missing PO number
0004 D. Nguyen $-300.00 4/4/26 -2 d.nguyen@example.com W.A. refund negative amount and quantity need review
ORD-005 TBD missing open
ORD-0006-extra Frank O'Neil $2,410.3O 04-06-2026 003 frank.oneil@example.com WA Complete letter O appears in amount
ord 0007 GRACE LEE 1 100.75 2026/04/07 8.0 grace.lee @example.com Wash. paid in full email contains internal space
ORD-0008 Hank Miller (450.00) 13/04/2026 1,000 hank.miller@example.com WA chargeback European-style date or invalid US date
ORD-0009 Ivy Chen $0.00 0 ivy.chen@example.com wa cancelled blank date
duplicate ORD-0009 Ivy Chen $0 04/09/2026 0 units ivy.chen@example.com WA Cancelled possible duplicate order
ORD-0010 Jamal Brown $3,200.999 04/10/2026 12 jamal.brown@example.com OR shipped amount has too many decimals
N/A KATE WILSON n/a n/a n/a kate.wilson@example.com WA unknown placeholder values

Hints

Column Problem Pattern Cheat Sheet Reference / Suggested Assign Pattern
raw_order_id Inconsistent prefixes, extra words, underscores/spaces, missing prefix, duplicate indicator. Use Trim(), Replace("_", "-"), Regex.Replace(rawText, "[^0-9]", ""), then rebuild "ORD-" + number.
raw_customer_name Leading/trailing spaces, inconsistent capitalization, multiple internal spaces, blank names, initials. Use Trim(), Regex.Replace(rawText.Trim(), "\\s+", " "), ToUpper()/ToLower(); flag blank with String.IsNullOrWhiteSpace(rawText).
raw_invoice_amount Currency symbols, commas, USD prefix, blanks, negative values, parentheses, letter O instead of zero, too many decimals. Use Replace("$",""), Replace(",",""), Replace("USD",""), Regex.Replace(rawText, "[^0-9.()-]", ""), then CDbl(cleanAmountText); flag negatives and unusual decimals.
raw_order_date Multiple formats, blanks, TBD/N/A, ambiguous or invalid dates. Use Trim(); flag TBD/N/A/blank; use date conversion only after standardizing expected formats.
raw_quantity Text units, written numbers, blanks, negatives, commas, decimals used for whole units. Use Regex.Replace(rawText, "[^0-9.-]", ""), then convert; flag blanks, negatives, nonnumeric terms such as "five".
raw_email Uppercase, double @, missing domain suffix, internal spaces, placeholder text. Use Trim().ToLower(), Replace(" ",""), then validation flag for exactly one @ and likely domain structure.
raw_state Inconsistent abbreviations and names: wa, Washington, W.A., Wash., OR. Use Trim().ToUpper(), Replace(".",""), then map Washington/Wash/WA to WA.
raw_status Inconsistent casing and labels: paid, PAID, paid in full, complete, refund, chargeback, unknown. Use Trim().ToLower(), then map related labels to standardized categories such as paid, pending, cancelled, refund, review.
Notes (drop me) Extra whitespace and free-text indicators of exceptions. Use Regex.Replace(rawText.Trim(), "\\s+", " "); search for terms such as missing, duplicate, negative, invalid, review.

Suggested Clean Output Columns

Example Review Rules