Extract from a CSV Column

Worked example: two columns out of a request log

Three log rows, one of which is deliberately not an API call:

request_id,path
r-8891,/api/v2/orders?id=1204
r-8892,/api/v1/reports/monthly.csv
r-8893,/healthz

Set Column to read to path and type this into Pattern:

/api/(?<version>v\d+)/(?<resource>[a-z-]+)

The result:

request_id,path,path_version,path_resource
r-8891,/api/v2/orders?id=1204,v2,orders
r-8892,/api/v1/reports/monthly.csv,v1,reports
r-8893,/healthz,,

With this above it:

3 rows · extracted from "path" · 2 new columns
2 rows matched · 1 row did not match · original kept

1 row (33%) did not match the pattern, so their new cells are
blank. Check the pattern against one of those values before you
rely on this file.

Four things are worth noticing. The columns are called path_version and path_resource, taken from the group names in the pattern and prefixed with the source column so two extractions from two columns cannot collide. They are inserted directly to the right of path, in pattern order, not appended at the end. The /healthz row is still there with two empty cells, because dropping rows silently would be a worse answer than keeping them. And that 33% is the whole point of the exercise: on three rows it is obvious, on thirty thousand it is the difference between a correct file and one that quietly lost a third of its data.

Naming, and taking more than one match

Column names come from three places, in order. A comma separated list typed into New column names wins outright and is applied left to right. Failing that, a named group supplies the name, so (?<year>\d{4}) gives ref_year when the source column is ref. Failing both, the group number is used, giving ref_1 and ref_2. A name that would clash with a column already in the file gets a numeric suffix, so nothing is overwritten.

Take is set to The first match, which is what you want when the pattern describes the whole cell. Switch it to Every match when one cell holds several things: a note listing three order numbers, a field holding several email addresses, a path with repeating segments. In that mode the occurrence number is added to each name, so a single unnamed group over a note column produces note_1_1 and note_1_2, meaning group 1 of occurrence 1 and group 1 of occurrence 2.

The result is rectangular, so the number of columns is set by whichever cell had the most matches and shorter cells are padded with blanks on the right. That is where Max new columns earns its place. Left uncapped, one pathological cell in a large file could widen the table to hundreds of columns; the cap stops matching once it is reached, defaults to 12, and accepts anything from 1 to 50.

Practical notes

• An invalid pattern says so. A stray bracket produces a plain message naming the problem rather than a file with something wrong in it.
• Nothing matching at all gets its own warning. It reminds you that this is a regular expression and that a dot matches any character, which is the most common reason a pattern that looks right returns nothing.
• Row order and row count never change. This tool only adds columns and optionally removes the one you read from. No filtering, no sorting, no deduplication.
• Extracted values keep their exact text. A captured 007 is written as 007, because cells are strings from read to write and nothing here reinterprets them as numbers.
• Leave the pattern empty to see the columns. With no pattern typed the file passes through unchanged and the column picker fills with the real names from your header row.
• The download is a new file. requests.csv comes back as requests-extracted.csv.

Related