Parsing, Transformation, and Advanced Tool Configuration
Parsing questions test Text to Columns, RegEx, JSON Parse, XML Parse, and the Dynamic Rename / Dynamic Select tools. Text to Columns splits on a delimiter or fixed width; a trap is that it drops the original field unless "Output original field" is checked, and empty splits create nulls. RegEx tool offers Match, Parse, Replace, and Tokenize modes - Parse extracts capture groups into new fields, Tokenize splits on every match.
JSON Parse converts a JSON string field into a table; nested arrays require iterative parsing or the JSON Parse with a path. XML Parse similarly flattens elements. Dynamic Rename uses incoming field metadata (e.g., first row as headers) to rename columns; Dynamic Select selects fields by name pattern or type, which is essential when schemas change run to run.
Traps: RegEx is case-sensitive unless (?i) is used; greedy .* consumes too much - use .*?. Text to Columns on a large field count can hit limits. Dynamic Select with "Unknown" type may drop fields unexpectedly.
Rule of thumb: when column names vary by file, use Dynamic Rename (First Row as Header) plus Dynamic Select (by pattern) rather than hard-coded Select.
Worked example: a field Name, Age, City in one column. Text to Columns with comma delimiter, output original unchecked, yields three fields; trim each with Data Cleansing to remove leading spaces.