From a spreadsheet column to an array in JavaScript, Python or SQL IN

"Can you check these 300 IDs?"

Someone from support sends a spreadsheet with a column of customer IDs and asks which ones had a failed payment. Or a product manager wants to know which of these emails already have an account. You need that column inside a query or a script, and copying values one by one with quotes and commas is slow and error-prone.

Turning a column into a list is one of those small tasks developers do every week. This guide shows the formats you usually need, the details that break them, such as quotes, duplicates and line endings, and what to do when the list is long.

What you get when you copy a column

When you copy a column from Excel, Google Sheets or LibreOffice, the clipboard receives one value per line. A few details come along that matter later:

• Line endings are often Windows-style (\r\n), even on other systems, and the last line may end with a line break too.
• Empty cells become empty lines.
• Spaces before or after a value come along, invisible in the spreadsheet.
• Formatting is lost: what you see in the cell, such as $1,234.56 or 11/05/2026, is copied as text, not as the underlying number or date.

A good conversion trims spaces, drops empty lines and treats \r\n and \n the same way, so 1043 and 1043 do not end up as two different values.

The formats you usually need

Given this column:

SP
RJ
MG
SP
O'Brien

A JavaScript or JSON array, with double quotes:

[
  "SP",
  "RJ",
  "MG",
  "SP",
  "O'Brien"
]

A Python list, indented with four spaces as PEP 8 suggests:

[
    "SP",
    "RJ",
    "MG",
    "SP",
    "O'Brien"
]

A SQL IN clause, with single quotes:

IN ('SP', 'RJ', 'MG', 'SP', 'O''Brien')

Note the last item. Inside a SQL string, a single quote is written twice, so O'Brien becomes 'O''Brien'. Forgetting this either breaks the query or, if the values come from someone else, opens the door to SQL injection. In JSON and JavaScript the equivalent risk is a double quote inside a value, which must become \".

CSV, on a single line: SP,RJ,MG,SP,O'Brien.

The Column to Array converter on this site produces all of these, escapes quotes correctly for each format and keeps everything in your browser, which matters when the column holds customer data.

Numbers, quotes and types

IDs are a classic trap. A column of numeric IDs can go into SQL without quotes:

IN (1042, 1043, 1077)

That is faster and compares numbers with numbers. But only drop the quotes when every value really is a number. Codes like ZIP codes, phone numbers or document numbers often start with zero, 01000-000 for instance, and must stay strings; as numbers they lose the leading zeros or fail to parse.

The same applies in JavaScript: an ID like 12345678901234567890 is larger than what a JavaScript number can represent exactly and should stay a string.

Duplicates, empty lines and sorting

Real columns are messy. Before using the list, decide:

• Remove duplicates? For an IN clause, duplicates are harmless but noisy. For a list you will count, they change the answer. Keep in mind that [email protected] and [email protected] are different strings, so normalize case first if it matters.
• Remove empty lines? Almost always yes. An empty string in IN ('', ...) rarely means what you want.
• Sort? Sorting makes the list easier to review and diff. Prefer a natural sort, which puts 9 before 10, over a plain text sort, which puts 10 before 9.

Long lists

Pasting 10 values into an IN clause is fine. Pasting 50,000 is a different problem:

• Databases have limits. Oracle accepts at most 1,000 expressions in an IN list, and SQL Server allows around 2,100 parameters per query when you use parameterized lists.
• Big literal lists are slow to parse and each different list produces a different query, so the database cannot reuse its plan.

For large sets, load the values into a temporary table and join against it:

CREATE TEMPORARY TABLE ids (id bigint PRIMARY KEY);
-- insert the values, for example with COPY or a bulk insert
SELECT o.*
FROM orders o
JOIN ids ON ids.id = o.customer_id;

In PostgreSQL you can also pass a single array parameter and write WHERE customer_id = ANY($1), which keeps one query plan no matter how many values you send.

In application code, use parameters

Converting a column into a literal list is perfect for ad hoc queries, scripts and debugging. In application code, values that come from users or files should never be glued into SQL. Use your driver's parameters instead:

cursor.execute(
    "SELECT * FROM customers WHERE state = ANY(%s)",
    (["SP", "RJ", "MG"],),
)

The driver escapes everything for you, and the query stays the same regardless of the values.

Common mistakes

• Forgetting to escape quotes inside values, such as O'Brien in SQL.
• Removing quotes from codes that start with zero.
• Leaving trailing spaces, so the same value appears twice.
• Keeping empty lines that turn into empty strings.
• Pasting tens of thousands of values into an IN clause instead of using a temporary table.
• Concatenating lists into SQL in application code instead of using parameters.

Summary

A spreadsheet column is just lines of text, with spaces, empty cells and Windows line endings mixed in. Clean it up by trimming and removing empty lines, decide on duplicates and sorting, and pick the right quotes for the target: double quotes in JavaScript, JSON and Python, single quotes with doubled apostrophes in SQL. Keep codes as strings, and switch to a temporary table or an array parameter when the list gets long.