Data extractor

This page does two unrelated kinds of work. The first half recognises things inside prose, which is pattern matching with no understanding behind it. The second half reshapes lines you already have, where nothing is recognised at all and the only thing that can go wrong is the separator. Knowing which half you are in tells you how much to trust the output.

What the patterns get right, and where they do not

A URL at the end of a sentence is followed by a full stop, and a URL in brackets by a closing bracket, and neither is part of the address. Naive extraction keeps them and produces links that look right and fail, so trailing .,;:!?)]} are stripped here. The cost is the handful of addresses that genuinely end in a bracket, which some Wikipedia article titles do; that is unavoidable without understanding the sentence. Bare domains with no scheme are not matched, because example.com in prose is far more often a mention than a link.

Hostnames keep their subdomain and lose www. Both are judgement calls and both are the right ones for auditing: blog.example.com and shop.example.com are genuinely different properties and often different teams, while www.example.com and example.com are the same site to everyone except a resolver, and keeping them apart makes a link audit twice as long for no benefit. Reducing a hostname further, to example.co.uk from a subdomain, is a different job. It needs the public suffix list to know that .co.uk is a suffix and not a domain, which is a large data file and not something to guess at.

Phone numbers are the least reliable output on the page, and any tool that claims otherwise is not being straight with you. Formats vary by country, separators vary by writer, and a long reference number or an order ID is indistinguishable from a phone number without context. This is tuned to catch numbers instead of avoiding false positives, on the reasoning that a wrong entry is obvious to a person reading the list while a missing number is not. Read the output; do not trust it. The same caution applies legally as well as technically: extracted numbers and addresses are personal data, and pulling them out of a document is not permission to use them.

Numbers come out as written, and the ambiguity there cannot be resolved by any tool. 3,75 is three and three quarters in Amsterdam and three thousand seven hundred and fifty in London, so both are extracted exactly as typed. A leading minus is kept. A figure embedded in an identifier such as an order number or a postcode is extracted too, because there is no way to tell a quantity from a label without reading the sentence. Sorting the result numerically is usually the quickest way to spot the ones that are not quantities.

Reshaping a column of lines

The four list operations are deliberately simple, and the gap between them and real CSV shows the moment a value contains the separator. Three lines reading alpha, Smith, John and charlie join into alpha,Smith, John,charlie, and splitting that back gives four values instead of three, because nothing in the row records that one of those commas belongs to a name. A real CSV writer would produce alpha,"Smith, John",charlie, and under RFC 4180 a value has to be quoted whenever it contains the separator, a double quote or a line break. This writes no quotes and reads none, on purpose: for a plain list of simple values they are noise, and once your data needs them you want the data format converter instead.

A semicolon is the right separator more often than it looks. Spreadsheets in countries that use a comma as the decimal mark save their files with semicolons, so a CSV that looks broken in one locale is usually fine once you put a semicolon in the separator field.

The JSON array is the one output that escapes, and that is the whole reason to use it instead of adding quotes by hand. A line reading say "hello" has to come out as "say \"hello\"", and back\slash as "back\\slash"; wrap those in quotes yourself and you get invalid JSON whose failure surfaces wherever the file is finally parsed, a long way from where it was built. A tab inside a value becomes \t for the same reason, and the carriage returns a Windows paste brings along are removed with the line breaks instead of being escaped into the values. Accented characters and emoji are written as themselves, so café stays café; that is valid JSON, which is Unicode text, and only a system insisting on plain ASCII will object. Every value is a string, because a flat list of lines carries no type information at all: 42 comes out as "42" and true as "true". If you need real numbers, booleans or objects, start from a header row in the CSV to JSON converter.

Blank lines are dropped and every value is trimmed, which is what a pasted spreadsheet column almost always needs. Numbering the lines is the one operation that adds something, and it is there for the same reason: quoting a list back at someone is much easier when the lines have numbers on them.