Converting CSV to Markdown Tables for Documentation
pandas, Miller and csvlook turn a CSV into a Markdown table, and each breaks on a pipe or a newline. pandas wrote 12.50 as 12.5 and csvlook 02134 as 2,134.
A small CSV pasted into a README or a wiki page should become a table, and the conversion looks trivial: put pipes between the fields and add a row of dashes. It works until the data contains a pipe, a newline or something that the tool you used decided was a number. This article converts a nine-row file with every awkward feature through three common tools and a short script, counts the cells in each row of the result, and notes which changes to the data each tool made on the way.
What breaks a Markdown table
The file has nine records: a comma, quotes, a newline in a field, an empty cell, NO, non-ASCII text, a number with a trailing zero (12.50, 1.10), zip codes with leading zeros, and one note with HTML and a pipe: <b>bold</b> a|b & "q". A Markdown table has rules that CSV does not:
A row is one line. A newline inside a cell ends the row.
The pipe is the separator. A
|inside a cell starts another cell unless it is escaped as\|.Cell content is Markdown. Asterisks, underscores, backticks and HTML pass through to the renderer.
There is no type. Everything is text, so any number formatting a tool applies is a change to the data.
The check I used counts the cells in every line, so a pipe or a newline shows up as a line with the wrong count:
"""Count the cells in each line of a Markdown table, treating \\| as an escaped pipe."""
import re, sys
text = sys.stdin.read()
widths = []
for n, line in enumerate(text.split("\n"), 1):
if not line.strip():
continue
cells = re.split(r"(?<!\\)\|", line.strip())
widths.append((n, len(cells) - 2)) # leading and trailing pipes give two empty ends
want = widths[0][1]
bad = [(n, w) for n, w in widths if w != want]
print(f"{len(widths)} lines; header has {want} cells; lines with a different count: {bad if bad else 'none'}")pandas
DataFrame.to_markdown is the one-liner most people reach for. It uses the tabulate library:
python -c "
import pandas as pd
df = pd.read_csv('lab/data/conv/people.csv', dtype=str, keep_default_na=False)
print(df.iloc[[0, 1, 3, 8]].to_markdown(index=False))
" | tee work/conv/md_pandas.md
echo; python lab/conv/md_check.py < work/conv/md_pandas.md| id | name | zip | score | active | joined | note |
|-----:|:------------|------:|--------:|:---------|:-----------|:----------------------|
| 1 | Ana Silva | 02134 | 12.5 | true | 2025-03-01 | plain |
| 2 | O'Neil, Pat | 10001 | 0 | false | 2025-03-02 | has, a comma |
| 4 | Cy | 90210 | | true | 2025-03-04 | line one |
| | | | | | | line two |
| 9 | Gus | 12345 | 1.1 | true | 2025-03-09 | <b>bold</b> a|b & "q" |
7 lines; header has 7 cells; lines with a different count: [(7, 8)]For a selection of four rows, the output looks like a table, and the checker disagrees. The result has 7 lines in this selection, and line 7 has 8 cells: a|b in the note of row 9 is read as a separator. And row 4's note, which contains a newline, became two lines: | 4 | Cy | 90210 | | true | ... | line one | and a second line that is empty in every column except the last (line two). A renderer shows the first as a row and the second as a row with a blank first column, or breaks the table, depending on how it handles lines.
There is a second change, which no structural check catches. The column score holds the strings 12.50 and 1.10, and the table shows 12.5 and 1.1: tabulate parses anything that looks like a number and reformats it, even when pandas holds it as a string. Passing disable_numparse=True stops it:
echo "Miller, the pipe row:"; mlr --icsv --omd cat lab/data/conv/people.csv | tail -n 1
echo "csvlook -I (no inference), first two rows:"; csvlook -I lab/data/conv/people.csv | sed -n 1,4p
echo "pandas with disable_numparse:"; python -c "
import pandas as pd
df = pd.read_csv('lab/data/conv/people.csv', dtype=str, keep_default_na=False)
print(df.iloc[[0, 8]].to_markdown(index=False, disable_numparse=True))"Miller, the pipe row:
| 9 | Gus | 12345 | 1.10 | true | 2025-03-09 | <b>bold</b> a\|b & "q" |
csvlook -I (no inference), first two rows:
| id | name | zip | score | active | joined | note |
| -- | ------------- | ----- | ------- | ------ | ---------- | --------------------- |
| 1 | Ana Silva | 02134 | 12.50 | true | 2025-03-01 | plain |
| 2 | O'Neil, Pat | 10001 | 0 | false | 2025-03-02 | has, a comma |
pandas with disable_numparse:
| id | name | zip | score | active | joined | note |
|:-----|:----------|:------|:--------|:---------|:-----------|:----------------------|
| 1 | Ana Silva | 02134 | 12.50 | true | 2025-03-01 | plain |
| 9 | Gus | 12345 | 1.10 | true | 2025-03-09 | <b>bold</b> a|b & "q" |The last block of that output shows 12.50 and 1.10 preserved. It also shows the pipe unfixed (a|b is still raw), so to_markdown needs the escaping done before it, on the data.
Miller
Miller has a Markdown output format:
mlr --icsv --omd cat lab/data/conv/people.csv | tee work/conv/md_mlr.md | sed -n '1,4p;5,8p'
echo; python lab/conv/md_check.py < work/conv/md_mlr.md| id | name | zip | score | active | joined | note |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Ana Silva | 02134 | 12.50 | true | 2025-03-01 | plain |
| 2 | O'Neil, Pat | 10001 | 0 | false | 2025-03-02 | has, a comma |
| 3 | Bo "Bee" Chen | 07030 | 7 | true | 2025-03-03 | says "hi" |
| 4 | Cy | 90210 | | true | 2025-03-04 | line one
line two |
| 5 | Di | 01002 | 3.14 | false | 2025-03-05 | |
12 lines; header has 7 cells; lines with a different count: [(6, 6), (7, 0)]mlr --icsv --omd cat printed the header, the separator row of --- and the data. The numbers are kept (12.50) and the zips keep their zeros, since Miller only formats numbers it modifies. The pipe row, shown in the extra output above, is <b>bold</b> a\|b & "q": Miller escapes the pipe. It does not handle the newline, which it writes as a real line break, so the checker found lines with the wrong count (a line of 6 cells where the newline cut a row, and a line of 0 cells for the continuation). Of the three tools it is the closest, and one fix away from correct.
csvlook is not Markdown
csvkit's csvlook prints a table that looks like Markdown and is made for the terminal:
csvlook lab/data/conv/people.csv | tee work/conv/md_csvlook.txt | sed -n 1,6p| id | name | zip | score | active | joined | note |
| -- | ------------- | ------- | -------- | ------ | ---------- | --------------------- |
| 1 | Ana Silva | 2,134 | 12.50 | True | 2025-03-01 | plain |
| 2 | O'Neil, Pat | 10,001 | 0.00 | False | 2025-03-02 | has, a comma |
| 3 | Bo "Bee" Chen | 7,030 | 7.00 | True | 2025-03-03 | says "hi" |
| 4 | Cy | 90,210 | | True | 2025-03-04 | line one↵line two |Look at the zip column: 02134 was printed as **2,134**, 07030 as 7,030 and 90210 as 90,210. csvlook infers types and formats numbers with thousands separators; a zip code became a number with a comma. It also wrote True for true, padded every column with spaces to a uniform width, and replaced the newline with a ↵ symbol. Run with -I (no inference), it kept 02134 and the original text, as the second block of the extra output shows. Treat csvlook as a viewer, as in previewing a CSV in the terminal. If you paste its output into a document, check that the numbers are still the numbers you had.
A short script
Three rules fix the data problems, applied to each cell before it is placed in the table: replace | with \|, replace newlines with <br> (an HTML line break, which GFM renderers accept inside a cell), and leave everything else as it is.
"""CSV to a GitHub-style Markdown table: escape the pipe, turn a newline into <br>, say what was changed."""
import csv, sys
PIPE = "\\|"
def cell(v):
return v.replace("|", PIPE).replace("\r\n", "<br>").replace("\n", "<br>")
def table(rows, align=None):
head, body = rows[0], rows[1:]
align = align or {}
out = ["| " + " | ".join(cell(h) for h in head) + " |",
"| " + " | ".join(("---:" if align.get(h) == "right" else "---") for h in head) + " |"]
out += ["| " + " | ".join(cell(v) for v in r) + " |" for r in body]
return "\n".join(out)
if __name__ == "__main__":
rows = list(csv.reader(open(sys.argv[1], newline="", encoding="utf-8")))
print(table(rows, {"id": "right", "score": "right"}))python lab/conv/md_table.py lab/data/conv/people.csv | tee work/conv/md_script.md | sed -n '1,4p;5,8p;10,11p'
echo; python lab/conv/md_check.py < work/conv/md_script.md| id | name | zip | score | active | joined | note |
| ---: | --- | --- | ---: | --- | --- | --- |
| 1 | Ana Silva | 02134 | 12.50 | true | 2025-03-01 | plain |
| 2 | O'Neil, Pat | 10001 | 0 | false | 2025-03-02 | has, a comma |
| 3 | Bo "Bee" Chen | 07030 | 7 | true | 2025-03-03 | says "hi" |
| 4 | Cy | 90210 | | true | 2025-03-04 | line one<br>line two |
| 5 | Di | 01002 | 3.14 | false | 2025-03-05 | |
| 6 | Ed | 60601 | 100 | | 2025-03-06 | NO |
| 8 | Fay | 1e5 | -5.25 | yes | 2025-03-08 | padded |
| 9 | Gus | 12345 | 1.10 | true | 2025-03-09 | <b>bold</b> a\|b & "q" |
11 lines; header has 7 cells; lines with a different count: noneThe output has 11 lines (a header, the alignment row and nine records), and the checker found 7 cells on every one: lines with a different count: none. The id and score columns are right-aligned with ---: in the separator row. Row 4's note is line one<br>line two, on one line. Row 9's note is <b>bold</b> a\|b & "q". The leading and trailing spaces of Fay's note remain (| padded |), which a renderer will trim.
The same rules apply when you write JSON from the same file, as in converting CSV to JSON. A smaller example shows the form of the output exactly:
printf 'id,name,score\n1,Ana,12.50\n2,"O'"'"'Neil, Pat",0\n' > work/conv/small.csv
python lab/conv/md_table.py work/conv/small.csv| id | name | score |
| ---: | --- | ---: |
| 1 | Ana | 12.50 |
| 2 | O'Neil, Pat | 0 |What the script does not do
It does not escape the characters that Markdown itself treats as markup. A cell that contains *not bold*, snake_case_name, a backtick, or a leading # or > can render differently from the text, and the <b>bold</b> in row 9 is passed through as HTML, which most renderers will show as bold text. If the table is documentation for people and the data is trusted, that is usually fine and sometimes what you want. If the data comes from outside (names, notes, free text), escape it, or put it in code spans, and decide whether HTML is allowed in your renderer. I did not test what any renderer does with these.
It also does not pad the columns for alignment in the source. A pretty-printed source table (pandas, csvlook) is easier to read in a raw README; an unpadded one is easier to diff, because a changed cell does not re-flow the whole table. For a table that changes often, I prefer the unpadded form.
How to tell it worked
Run the cell counter over the result. Compare every number in the table with the CSV, character by character: this catches 12.5 for 12.50 and 2,134 for 02134. Include the awkward rows in any fixture, as these nine do. Render the table in the place it will be published, because this check is structural and I did not do the rendering. And keep the CSV in the repository next to the Markdown, with a script to regenerate it, so the table is never edited by hand.
When the renderer decides
Markdown tables are a feature of extended Markdown flavors; the GitHub Flavored Markdown specification defines the pipe table, the alignment colons and the pipe escape used here, and renderers vary on everything outside it. A very wide table, or one with long cell text, is unreadable in most renderers, and a table with thousands of rows is a bad idea in a README at all. For those, link to the CSV and show the first few rows. The conversion hub compares this with the other targets, and the validation guide covers what to check in the CSV before any of them.
