Column to a comma separated list

Turning a column into a comma separated list

You have a column of IDs in a spreadsheet. You need them in a query, a config file, or a script. Copying the column gives you one value per line, and what you need is one line with values separated by commas — quoted, escaped, and wrapped in parentheses if it is going into SQL.

It is worth naming the confusion directly: Excel's Text to Columns does the opposite of this. That feature splits one cell into several. This page joins a column into one line, which is the direction that has no button anywhere and the reason people end up doing it by hand.

It is a thirty-second job done by hand for ten rows and a genuinely annoying one for four hundred. Doing it with find-and-replace works until a value contains an apostrophe, at which point the query breaks in a way that is hard to spot.

The four output formats

Comma gives a plain separated list. Right for pasting into a form field that accepts multiple values, or a spreadsheet formula.

Quoted wraps each value in double quotes and escapes any quotes inside. Right for JavaScript arrays written by hand, and for CSV-ish contexts where values might contain commas.

JSON produces a properly formatted array. Every character that needs escaping is escaped by the JSON serialiser itself rather than by string manipulation, so the output is valid by construction.

SQL IN produces a parenthesised list of single-quoted values ready to drop after WHERE column IN. Apostrophes are doubled, which is the SQL escape — so O'Brien becomes 'O''Brien' rather than breaking the statement.

A word about the SQL output

Escaping quotes correctly makes the output syntactically safe, and that is all it does. Pasting values from an untrusted source into a query is still a bad idea, and the right answer for anything running repeatedly is a parameterised query rather than a literal list.

This format exists for the ad-hoc case: you are in a database client, you have four hundred order numbers, and you want to look at those rows once. That is a legitimate and extremely common thing to do.

Blank rows and whitespace

Blank lines are dropped. A copy-paste out of a spreadsheet almost always brings a trailing newline, and turning that into an empty string in the middle of a SQL clause produces a query that runs and quietly returns the wrong rows.

Each value is trimmed of surrounding whitespace for the same reason: a leading space in an ID will not match anything, and it is invisible in the output.