Convert spreadsheet tables without breaking quotes or lines

Moving a table from a spreadsheet into a README, a web page, a database or a paper sounds trivial: it is rows and columns. Then one cell contains a comma, another a quote, a third a line break someone typed with Alt+Enter, and the result has an extra column, a row split in two, or a Markdown table that renders as a paragraph. This article shows exactly where conversions break, how each format is supposed to protect the awkward characters, and a worked example produced with our table converter.

The test table

Three rows of a café menu, each with one problem:

The first note has a comma and quotes, the second a line break inside the cell, the third product name a pipe character, and the third note is empty. Vietnamese accents are there for good measure.

What a spreadsheet actually copies

Select cells in Excel or Google Sheets and copy: the clipboard receives tab-separated text (TSV), one line per row. A cell that contains a tab, a line break or a quote is wrapped in double quotes and its quotes are doubled, exactly as in CSV. That is why pasting into a plain text box often looks wrong: a line break inside a cell appears as a new line, and only a parser that understands the quotes knows it is still the same row.

CSV: quote, and double the quotes

RFC 4180, the closest thing CSV has to a standard, says: a field containing the delimiter, a quote or a line break is enclosed in double quotes, and a quote inside it is written twice. Our menu becomes:

Product,Price,Note
Cà phê sữa,35000,"Iced, ""less sugar"""
Bánh mì,25000,"Pâté
no chili"
Trà | đào,45000,

Four records, but five lines of text. Anything that counts rows by counting lines, or splits each line on commas, gets this wrong. The empty note is simply nothing after the last comma.

Two practical points. Excel in many European and Latin American locales, including Brazil, expects semicolons, because the comma is their decimal mark; pick the delimiter that matches the people opening the file. And Excel on Windows usually reads a CSV as UTF-8 only when it starts with a byte order mark, so the converter adds one to CSV and TSV downloads; without it "Cà phê sữa" can open as "Cà phê sữa".

Markdown: escape pipes, replace line breaks

A Markdown table separates cells with |, so a pipe inside a cell must be written \|. A table row must be a single line, so a line break becomes <br>, which GitHub renders as a break:

| Product    | Price | Note               |
| ---------- | ----- | ------------------ |
| Cà phê sữa | 35000 | Iced, "less sugar" |
| Bánh mì    | 25000 | Pâté<br>no chili   |
| Trà \| đào | 45000 |                    |

Quotes and commas need nothing. The padding is optional, but it makes the table readable as plain text. Markdown also requires a header row; a table without one gets generic column names.

HTML: entities and `<br>`

In HTML the dangerous characters are <, & and, inside attributes, ". Escaping them as entities keeps a cell like a<b from being read as a tag (and keeps pasted text from injecting markup). The quote in our note becomes ", the line break <br>, and the empty cell an empty <td></td>.

SQL: double the single quote

SQL string literals use single quotes, so a single quote inside is written twice: O'Brien becomes 'O''Brien'. Our menu, for standard SQL with empty cells as NULL and numbers unquoted:

INSERT INTO "menu" ("Product", "Price", "Note") VALUES
  ('Cà phê sữa', 35000, 'Iced, "less sugar"'),
  ('Bánh mì', 25000, 'Pâté
no chili'),
  ('Trà | đào', 45000, NULL);

Dialects differ in the details. MySQL also treats a backslash as an escape character, so a literal backslash must be doubled, and names are quoted with backticks. SQL Server quotes names with brackets and needs N'…' strings so non-Latin text is not converted to the wrong code page. Decide deliberately whether an empty cell means NULL (unknown) or '' (known to be empty); they behave differently in queries.

LaTeX: ten special characters

LaTeX reserves \ & % $ # _ { } ~ ^. A cell with "50% & $5" must become 50\% \& \$5, or the document fails to compile. A line break inside a cell is not allowed in a plain l column, so the converter uses \shortstack[l]{Pâté\\no chili}, which works in standard LaTeX without extra packages.

Round trips: the real test

A conversion you can trust reads its own output back to the same table. The converter's tests do that for every format with a deliberately hostile table: quotes, commas, a line break, a pipe, every LaTeX special character, Vietnamese, Japanese and an emoji, and empty cells. All eight formats return exactly the original cells.

Common mistakes

• Splitting on commas in a script or a formula. Use a CSV parser that understands quotes.
• Counting rows with line counts (wc -l). Line breaks inside cells make the count wrong.
• Letting a spreadsheet "fix" your data. Opening a CSV in Excel turns 007 into 7, long IDs into rounded scientific notation (1.23457E+15) and some codes into dates. Import with columns set to Text, or convert without opening the file.
• Forgetting the header decision. JSON objects, SQL column lists and Markdown all need one; say whether the first row is data or names.
• Assuming JSON numbers are safe. A 20-digit ID read as a JavaScript number loses digits. Keep IDs as strings; the converter only writes numbers when you ask, and never for values longer than 15 digits.

For large CSV or JSON files, with nested data, the CSV and JSON converter is the better fit; the table converter is for tables you want to see and edit.