如何用 Python 將 CSV 和 JSON 轉成 LLM 提示詞及聊天機械人知識用的 Markdown 表格（處理跳脱、欄位不齊和巢狀欄位）

Pasting a spreadsheet export into a prompt, a README or a chatbot knowledge file usually goes one of two ways: raw CSV that is hard for a person to review, or a hand-made Markdown table that breaks the moment a cell contains a pipe character. This post walks through a small, dependency-free Python script that turns CSV files and JSON arrays into Markdown tables and handles the cases that break naive converters.

Disclosure: Appie.run publishes this post. Appie has free CSV and JSON to Markdown converters, mentioned near the end. The script below has no dependency on them, and it uses only the Python standard library.

Earlier posts in this series cover converting PDFs to Markdown for RAG and converting Notion to Markdown.

What breaks naive converters

The one-liner "| " + " | ".join(row) + " |" works until your data meets the real world:

1. Pipes inside cells. A value like has a | pipe adds a column and shifts everything after it.
2. Newlines inside quoted CSV cells. A multi-line note ends the table row early.
3. Ragged rows. Exports often have trailing rows with fewer fields, or completely empty rows.
4. Different delimiters. European spreadsheet exports often use semicolons.
5. A byte order mark. Excel's "CSV UTF-8" export starts with a BOM, which ends up glued to your first header.
6. JSON records with different keys. The second object has a field the first one lacks.
7. Nested values. A list of tags or an address object inside a record.

The script

Save this as to_md.py. It was tested with Python 3.13 on the cases below.

import csv
import json
import sys
from pathlib import Path


def escape_cell(value):
    """Make one value safe for a Markdown table cell."""
    if value is None:
        return ""
    if isinstance(value, (dict, list)):
        value = json.dumps(value, ensure_ascii=False)
    text = str(value)
    text = text.replace("\\", "\\\\").replace("|", "\\|")
    return " ".join(text.split())  # newlines and tabs become single spaces


def rows_to_markdown(header, rows):
    lines = [
        "| " + " | ".join(escape_cell(h) for h in header) + " |",
        "| " + " | ".join("---" for _ in header) + " |",
    ]
    for row in rows:
        cells = list(row) + [""] * (len(header) - len(row))  # pad short rows
        lines.append("| " + " | ".join(escape_cell(c) for c in cells[: len(header)]) + " |")
    return "\n".join(lines)


def csv_to_markdown(path, max_rows=None):
    with open(path, newline="", encoding="utf-8-sig") as f:
        sample = f.read(4096)
        f.seek(0)
        try:
            dialect = csv.Sniffer().sniff(sample, delimiters=",;\t")
        except csv.Error:
            dialect = csv.excel
        reader = csv.reader(f, dialect)
        header = next(reader)
        rows = []
        for i, row in enumerate(reader):
            if max_rows is not None and i >= max_rows:
                break
            if any(cell.strip() for cell in row):
                rows.append(row)
    return rows_to_markdown(header, rows)


def json_to_markdown(path, max_rows=None):
    data = json.loads(Path(path).read_text(encoding="utf-8"))
    if isinstance(data, dict):
        # a dict of lists: render each list under its own heading
        parts = []
        for key, value in data.items():
            parts.append(f"## {key}\n")
            if isinstance(value, list) and value and all(isinstance(v, dict) for v in value):
                parts.append(records_to_markdown(value, max_rows))
            else:
                parts.append(escape_cell(value))
            parts.append("")
        return "\n".join(parts).strip()
    return records_to_markdown(data, max_rows)


def records_to_markdown(records, max_rows=None):
    if max_rows is not None:
        records = records[:max_rows]
    header = []
    for rec in records:  # union of keys, first-seen order
        for key in rec:
            if key not in header:
                header.append(key)
    rows = [[rec.get(k) for k in header] for rec in records]
    return rows_to_markdown(header, rows)


if __name__ == "__main__":
    src = sys.argv[1]
    limit = int(sys.argv[2]) if len(sys.argv) > 2 else None
    if src.lower().endswith(".json"):
        print(json_to_markdown(src, limit))
    else:
        print(csv_to_markdown(src, limit))

Run it with a file and an optional row limit:

python3 to_md.py plans.csv
python3 to_md.py leads.json 25

How each problem is handled

• Pipes and backslashes are escaped in escape_cell, so a | b renders as one cell. Backslashes are escaped first so an existing \ does not swallow the escape.
• Newlines and tabs collapse to single spaces with " ".join(text.split()). Markdown table cells cannot hold line breaks without HTML, and a prompt rarely needs them.
• Ragged rows are padded with empty cells up to the header length and trimmed if they are longer. Rows where every cell is blank are skipped.
• Delimiters are detected by csv.Sniffer from the first 4 KB, limited to comma, semicolon and tab, with a fallback to standard CSV if detection fails.
• The BOM is removed by opening the file with encoding="utf-8-sig".
• Different keys in JSON records are merged into one header in first-seen order, and missing values become empty cells.
• Nested values (lists and objects) are written as compact JSON inside the cell. A top-level JSON object of lists becomes one ## heading per key, with a table under each list of records.

Test it on the awkward cases

printf 'plan,price_usd,notes\nStarter,19,"2,000 credits"\nStandard,49,"has a | pipe\nand a newline"\n,,\nFree,0\n' > plans.csv
python3 to_md.py plans.csv

Output:

| plan | price_usd | notes |
| --- | --- | --- |
| Starter | 19 | 2,000 credits |
| Standard | 49 | has a \| pipe and a newline |
| Free | 0 |  |

The quoted comma stayed in its cell, the pipe was escaped, the newline became a space, the blank row was dropped and the short Free row was padded.

A semicolon file:

printf 'name;score\nA;1\nB;2\n' > semi.csv
python3 to_md.py semi.csv

| name | score |
| --- | --- |
| A | 1 |
| B | 2 |

JSON records with different keys and a nested list:

echo '[{"name":"Ana","company":"Acme","tags":["saas","eu"]},{"name":"Ben","title":"PM"}]' > leads.json
python3 to_md.py leads.json

| name | company | tags | title |
| --- | --- | --- | --- |
| Ana | Acme | ["saas", "eu"] |  |
| Ben |  |  | PM |

A JSON object of lists:

echo '{"plans":[{"name":"Starter","credits":2000},{"name":"Standard","credits":5000}],"currency":"USD"}' > nested.json
python3 to_md.py nested.json

## plans

| name | credits |
| --- | --- |
| Starter | 2000 |
| Standard | 5000 |

## currency

USD

Is Markdown the right format for a prompt?

It depends on what you are optimising. We measured one case on our own machine: 50 records with four short fields came to 3,280 characters as JSON, 1,202 as CSV and 1,686 as a Markdown table. You can reproduce it with this snippet next to to_md.py:

import csv, io, json
from to_md import records_to_markdown

rows = [{"id": i, "name": f"user{i}", "plan": "Starter", "credits": 2000} for i in range(50)]
buf = io.StringIO()
w = csv.DictWriter(buf, fieldnames=rows[0].keys())
w.writeheader()
w.writerows(rows)
print(len(json.dumps(rows)), len(buf.getvalue()), len(records_to_markdown(rows)))

So for this data, CSV was the most compact and JSON the largest. Characters are not tokens, and your numbers will differ with your data and tokenizer, so measure your own case. Practical rules we use:

• Use Markdown when the table sits inside prose (a README, a doc page, a chatbot knowledge file a person will review), or when you want the model and the reader to see the same thing.
• Use CSV when the table is large and you only care about size.
• Keep JSON when values are deeply nested or when the model must return the same structure.
• Limit rows. The optional row limit exists because the right answer for a 20,000-row export is usually a sample plus a summary, not the whole file.

When you do not want to run a script

Appie has two free browser tools for one-off conversions:

• Convert CSV to Markdown: paste a CSV export or upload the file, and the first row becomes the table header.
• Convert JSON to Markdown: paste JSON or upload a .json file. Arrays of objects become tables and nested objects become headings.

Both ask for a Cloudflare Turnstile check before each run, and the pages say the file or paste is converted for that request and not saved to an Appie account. For anything repeatable, or for data you would rather not paste into a website, use the script.

If the Markdown is headed for a chatbot, Appie's AI Chatbots app can also take CSV files, text and URLs as knowledge directly, and you can add content from Claude Code or Cursor through the add_chatbot_content tool on Appie's MCP server.

Honest limits of Appie's converters

• One file per run, by hand. There is no batch mode on the tool pages, and each run needs a fresh captcha check.
• No control over escaping or row limits is described on the pages. If you need a specific output format, the script gives you that control.

FAQ

Why not use pandas to_markdown()?
It works well if pandas is already installed; it needs the extra tabulate package. The script above has no dependencies, which matters in small containers, CI jobs and serverless functions.

How do I keep line breaks inside a cell?
Replace the newline with <br> instead of a space in escape_cell. Many Markdown renderers, including GitHub's, show it as a line break inside the cell. LLMs read it fine, but it adds characters.

What about very wide tables?
Split them. A table with 30 columns is hard for people and models to read. Pick the columns the prompt actually needs.

Does the script handle Excel files?
No. Export the sheet as CSV first, or read it with openpyxl and pass the rows to rows_to_markdown.

If you want a website chatbot trained on files like these, you can try Appie's AI Chatbots on the Free plan (500 credits, no credit card) at appie.run.

RG, Founder, Appie.run