Converting Excel to CSV JSON XML and YAML in Practice

Spreadsheets are where data is entered and corrected, and other systems want it in other forms. The finance team keeps the price list in a workbook; the website needs JSON; the partner needs XML to a schema; the deployment needs YAML; the warehouse loads CSV. Every one of those conversions is a few lines of code, which is why they are done in a hurry and why they go wrong in the same ways: dates turn into serial numbers, product codes lose their zeros, a column header becomes an invalid tag name, an ampersand breaks an XML file.

This guide covers converting an Excel workbook to the four formats people ask for most: CSV, JSON, XML and YAML. It starts with how to choose, builds one shared reading step that makes all four conversions consistent, explains what each format needs, and ends with the checks that prove a conversion lost nothing. The spokes linked throughout give the full recipes.

Choosing the target

The right format is the one the reader needs, and a few questions settle it.

Who or what reads it? A database loader or an analyst's tool wants CSV or Parquet. A web application or API wants JSON. A government system, a supplier integration or an accounting package may require XML to a published schema. A deployment tool or application configuration wants YAML, often alongside code in a repository.

How big is it? CSV and JSON Lines stream. A single large JSON array or XML document has to be parsed whole unless the reader streams. YAML is slow and awkward past a few thousand lines.

Does it have structure? A flat table suits CSV. Nested data (orders with lines) suits JSON, XML and YAML, and a sheet that repeats the parent on every row has to be grouped into that structure.

Does it need types? CSV has none; every value is text, and the reader must interpret it. JSON has strings, numbers, booleans and null. XML has text and, with a schema, types. YAML has implicit types that can surprise.

Who edits it afterward? If humans will edit the output, YAML and JSON are friendlier than XML; if the workbook remains the source of truth, nobody should edit the output at all.

A simple rule of thumb follows: tables go to CSV or Parquet, API payloads to JSON, schema-bound exchange to XML, hand-maintained settings to YAML. Everything else deserves a conversation with the person receiving it.

The shared problems

Whatever the target, the same issues appear, because they come from the workbook, not the format.

Headers are written for people. Order No, Unit Price ($), 2025 Sales, a trailing space. They make poor JSON keys, invalid XML element names and awkward YAML keys. Convert them once to stable machine names, check that no two collapse into one, and keep a mapping to the originals.

Dates are numbers. Excel stores a date as a serial number with a format. A converter that cannot see the format writes 45730. One that sees it may write a locale-dependent string. Write ISO 8601 (2025-03-14, or 2025-03-14T09:30:00Z with a zone) in every format, and state the time zone for date-times, because Excel has none. Watch the 1904 date system of old Mac files, which shifts dates by four years (converting Excel serial dates to real dates).

Identifiers look like numbers. Product codes, postal codes and account numbers must stay strings. If the cell holds a number, the zeros are already gone; if it holds text, your reader must not convert it. Read identifier columns as text (keeping leading zeros in Excel).

Blank is not one thing. An empty cell may mean unknown, not applicable or zero. CSV writes an empty field; JSON can write null; XML can omit the element, write an empty one or use a nil marker; YAML writes null or omits the key. Decide the rule, and use it consistently.

Numbers are floating point. 0.1 + 0.2 shows the problem. Excel keeps 15 significant digits. Large integers are unsafe as JSON numbers because many parsers use doubles. Round money before writing, and write long identifiers as strings (Excel number precision and the 15 digit limit).

Formulas have cached results or none. A workbook written by a program that does not calculate has formulas with no cached values; conversions read the cache and get blanks. Open and save in Excel or recalculate with LibreOffice first.

Layout is not structure. Merged cells, subtotal rows, title blocks and notes are meaningful to a reader of the sheet and noise to a converter. Clean them first (cleaning Excel data before analysis).

One reading step, four writers

Do the reading and cleaning once and feed the result to whichever writer you need. In Python:

import re
import pandas as pd

def key(h):
    s = re.sub(r"[^0-9a-zA-Z]+", "_", str(h).strip()).strip("_").lower()
    return s or "col"

def load(path, sheet, id_cols=()):
    df = pd.read_excel(path, sheet_name=sheet, dtype={c: str for c in id_cols})
    df.columns = [key(c) for c in df.columns]
    if len(set(df.columns)) != len(df.columns):
        raise ValueError("duplicate keys after cleaning headers")
    for c in df.select_dtypes(include=["datetime64[ns]"]).columns:
        df[c] = df[c].dt.strftime("%Y-%m-%d")
    for c in df.select_dtypes(include=["object", "string"]).columns:
        df[c] = df[c].str.strip()
    return df.dropna(how="all")

The function reads the sheet, keeps identifier columns as text, converts headers to keys and fails if two collapse, formats dates as ISO text, trims whitespace in text columns and removes completely empty rows. Everything downstream starts from a clean, predictable frame. Any improvement to the cleaning, such as non-breaking space handling, goes in one place (trimming spaces and non breaking spaces).

Then each writer is short:

df = load("orders.xlsx", "Orders", id_cols=["Order No"])

df.to_csv("orders.csv", index=False, encoding="utf-8")                       # CSV
df.to_json("orders.json", orient="records", force_ascii=False, indent=2)     # JSON
df.to_json("orders.jsonl", orient="records", lines=True, force_ascii=False)  # JSON Lines
df.to_xml("orders.xml", index=False, root_name="orders", row_name="order", parser="etree")  # XML
import yaml
yaml.safe_dump(df.to_dict(orient="records"), open("orders.yaml", "w", encoding="utf-8"),
               sort_keys=False, allow_unicode=True)                          # YAML

These are the one-liners. Each has details covered below and in the spokes. They are a starting point, and for XML and YAML especially, you will usually build the structure yourself.

CSV

CSV is the simplest target and the easiest to get subtly wrong. Excel's own Save As writes only the active sheet, uses a code page or a UTF-8 variant with a byte order mark, and writes cells as displayed, which makes dates and numbers locale-dependent. A script that reads values and writes explicit text avoids all that.

Choices to make: UTF-8 without a BOM unless an old Excel must open it; a comma delimiter (or the one the receiver names); quoting for fields containing the delimiter, quotes or newlines (the library does it); ISO dates; dots as decimal separators; empty fields for blanks. One CSV per sheet, with sanitized file names and a manifest of sheets, rows and hashes (converting Excel to CSV for every sheet in a workbook).

When the CSV will be opened by people in Excel again, the round trip has its own traps: leading zeros dropped, long numbers shown in scientific notation, dates reinterpreted by locale (Excel and CSV files shared with other tools). And user-supplied text that starts with =, +, - or @ becomes a formula when opened, which is a security issue to neutralize when exporting (escaping formula characters in a CSV export).

JSON

JSON maps a sheet to an array of objects, one per row. Its strengths are types (numbers, booleans, null) and nesting; its weaknesses are no date type and the precision of large numbers.

Make the keys from the headers, write dates as ISO strings, write identifiers and large integers as strings, use a nullable integer type so that blank quantities become null rather than turning the column into floats, and never allow NaN, which is not valid JSON. Group repeated parents into nested objects deliberately, checking that parent fields are consistent across the rows of one parent. Use JSON Lines for large conversions, one object per line, which streams and splits (converting Excel to JSON with Python for clean output).

XML

XML demands more agreement than the others. Decide the root, the row element, which columns are attributes and which are elements, how empty cells appear, and whether a namespace applies. If the receiver provides an XSD, follow it, including element order.

The two common failures are invalid names and invalid characters. Element names cannot start with a digit or contain spaces, and XML 1.0 forbids most control characters, so both need a cleaning function. Build the document with a library (ElementTree, lxml) so that ampersands and angle brackets are escaped for you, write UTF-8 with a declaration and validate the output against the schema. Stream large sheets with an incremental writer (converting Excel to XML with a Python script safely).

YAML

YAML is chosen for configuration and fixtures that humans maintain, and its implicit typing is the hazard. Under YAML 1.1 loaders, yes, no, on and off are booleans, NO (Norway) is false, 0123 is octal, and dates and sexagesimal numbers are converted. Dump with safe_dump, convert values to the intended Python types first, keep versions and codes as strings so the dumper quotes them, and load the result with the application's loader to confirm it reads back as intended. Expand dotted setting names into nested mappings with conflict checks, and keep secrets out of the sheet (converting Excel to YAML config files without surprises and the Norway problem and YAML boolean surprises).

Tools other than Python

You do not need to write code for a one-off.

LibreOffice headless converts workbooks to CSV, including one file per sheet in recent versions, and to other formats, from a command line. It suits batches and servers and loads the whole document, so it is a fit for modest files.

Power Query in Excel can reshape a sheet and export through connections, and is good for recurring cleaning inside Excel.

DuckDB reads a workbook and writes CSV, JSON and Parquet with SQL, in one statement per output, and streams through the data (loading Excel files with DuckDB).

Online converters are convenient for public data and a poor idea for anything private. Do not paste a customer list into a web page you do not control. For small, non-sensitive samples, the site's CSV to JSON converter, JSON to XML converter and YAML to JSON converter run in the browser without sending data anywhere.

Other languages have their own readers and writers (reading and writing Excel from JavaScript, Java and C Sharp); the design above transfers.

Large workbooks

A conversion that works on a thousand rows may fail on a million. The reader is the usual limit, because most libraries load the whole sheet, and an xlsx expands when parsed. Stream with openpyxl read-only mode or convert with DuckDB, write incrementally (CSV rows, JSON Lines, an XML writer) and process in batches (reading a 500 MB Excel file without running out of memory and converting a huge Excel file to CSV or Parquet). A worksheet holds at most 1,048,576 rows, so if the output has to exceed that, it was never one sheet.

Proving the conversion

A conversion is not done until it is checked. The checks are the same in every format.

1. Counts. The number of records in the output equals the number of data rows in the sheet (count records, not lines, for CSV with embedded newlines).

2. A control total. The sum of a numeric column in the output equals Excel's sum for the same rows.

3. Keys and types. Every record has the same keys; dates parse; identifiers are strings with their zeros; no value is NaN.

4. A schema. Validate JSON with JSON Schema, XML with an XSD and YAML data with the same JSON Schema after loading. A schema makes the agreed structure an automatic test.

5. A round trip. Load the output back and compare it with the cleaned frame.

6. Spot checks. Read the first row, the last row and a messy one (accents, an ampersand, a quote, a long text, a blank).

7. A hash and a manifest. Record the source file's checksum, the output files' checksums, the row counts and the date, so a later question has an answer.

Make the checks part of the script and fail the run when one fails.

Keep the conversion repeatable

A one-off conversion is a favor, and a recurring one is a process. Put the script in version control with the schema and a small fixture workbook that contains the awkward cases. Run it in a scheduled job or from the CI system that publishes the output. Log the counts. When the workbook's layout changes, the header check should fail with a message that says which column moved (automating Excel work without clicking covers scheduling and testing).

The workbook stays the source of truth only if the conversion can be rerun at will. The moment people start editing the YAML or the JSON by hand, you have two sources, and a lost-update problem of your own. If edits to the output are unavoidable, make the output the source and retire the workbook, or split which fields each owns.

Security notes

A workbook from outside is untrusted input: limit its size, check that it is a zip, do not run macros and do not follow links. On the way out, neutralize text that begins with a formula character in CSV and workbook outputs; escape for the format (the libraries do it for JSON, XML and YAML); and do not include personal data the receiver should not have. Hidden sheets, comments and document properties may hold data you did not intend to export; converting only the chosen sheets and columns avoids carrying them along.

A worked example end to end

To see the pieces together, take a workbook that a product team maintains: a sheet of products with a code, a name, a price and a launch date, and a sheet of translations. Three consumers exist. The warehouse wants a CSV of products every night. The storefront wants JSON for the product catalog. The deployment needs the translations as YAML in the repository.

The conversion job starts by reading both sheets with the shared function. The product code is read as text, so 00417 survives. The headers become code, name, price and launch_date. Dates are formatted as ISO text. Rows that are completely empty are dropped, and a check confirms that the number of products equals the number of distinct codes. If the check fails, the job stops with the duplicate codes listed, and nobody gets a catalog with two products sharing a code.

The CSV writer produces products.csv in UTF-8 with a header and no index column, plus a manifest line with the row count and the file hash. The JSON writer produces products.json as an array, with price as a number rounded to two decimals and launch_date as a string; a missing launch date is null. The YAML writer reads the translations sheet, groups by key, builds the nested mapping, quotes the codes that YAML 1.1 would misread and writes translations.yaml with the order preserved from the sheet. Each output is then checked: the record counts are compared with the sheet, the price totals are compared with Excel's sum, the JSON is validated against a small schema, the YAML is loaded back with the application's loader and the keys are compared with the sheet.

The job runs from a scheduler. It exits non-zero on any failed check, and posts the counts to the team channel on success. The workbook is still where the team works. Nobody edits the outputs, and the repository's diff for translations.yaml shows exactly the lines that someone changed in the sheet, which is how the translators review each other's work.

The code for all of this is under two hundred lines, most of it checks. The format-specific parts are the smallest part of the job.

Round trips and what they teach

A good test of a converter is the round trip: convert the workbook to a format, convert back and compare. The comparison shows what the format cannot hold.

CSV to Excel and back loses types and formatting: a CSV has no idea that a column was a date or an identifier. JSON round-trips numbers, strings, booleans and nulls but not dates, nor key order in a strict sense. XML round-trips text and structure but needs a schema to say that a value is a number. YAML round-trips plain data types, subject to the implicit typing rules of the loader. None of the four preserves Excel's formulas, formats, colors, validation, comments or merged cells.

That is not a failure of the formats. It is a reminder that the output is data, and that if anything else in the workbook matters, such as the formulas that compute the price, it has to be documented somewhere or kept in the workbook. When someone proposes retiring the workbook and keeping only the converted files, ask what logic lived in the formulas, and who will own it afterwards.

Working with the people who own the sheet

Conversions fail more often through misunderstanding than through code. The person who maintains the workbook has habits that the converter does not know about: a note typed in the price column, a row colored red to mean discontinued, a blank row to separate groups, a month typed as text. Talk to them before writing the first line.

Agree the contract: which sheet, which header row, which columns are required, what each means, what values are allowed. Put it in writing, as a short document or as a schema file. Give them a way to check their own work, such as a command that validates the workbook and reports errors by row. People fix their own spreadsheets quickly when the error message says "row 37, price: not a number".

Agree what happens on failure. A failed validation should stop the publication of new outputs, leave the last good ones in place and tell the owner. A system that publishes a half-converted catalog is worse than one that publishes yesterday's.

Delivery and versioning of the outputs

Producing the files is half the job. Consumers need to find them, trust them and notice when they change.

Publish to a fixed location with a predictable name, and keep dated copies. A consumer that fetches products.json every hour should always find a complete file, so write to a temporary name and rename when finished. Put a version or a generation date inside the file where the format allows it: a metadata object in JSON, an attribute on the XML root, a comment line in YAML. For CSV, publish the manifest beside it.

Tell consumers about changes before they happen. A new column is usually harmless; a renamed or removed column breaks a parser. Treat the output format as an interface: add fields freely, deprecate before removing, and keep a changelog. If an output must change shape, run the old and new side by side for a period.

Keep the archive. When a downstream system reports a wrong number from last Tuesday's catalog, the file as it was on Tuesday, with its manifest, is the evidence that settles whether the conversion or the consumer was at fault.

Choosing between one file and many

A workbook with several sheets raises a structural question: one output or several? The answer follows the relationships between the sheets. If the sheets are independent tables, such as products and translations, write one file per sheet, named after it. If they describe one thing in parts, such as an order sheet and an order-lines sheet, consider nesting the lines under the orders in JSON, XML or YAML, joining on the key and checking that every line has an order. If the receiver needs a single document, build it deliberately and document the structure.

Keep the sheet-to-file mapping explicit in code and in the manifest. A converter that writes "every sheet" will one day write the sheet called Scratch that someone left in the workbook. A converter that lists the sheets it expects, and fails when one is missing or a new one appears unannounced, will not.

When converting a workbook is the wrong move

The workbook is the system. If people use the formulas, formats and interactions, the output loses the point. Keep the workbook and export values for others.

The receiver can read the workbook. Many systems import xlsx directly. Converting adds a step and a chance for error.

The data should not be in a workbook at all. If the conversion is a weekly chore, the source should be a database or an API, and the workbook a view of it (when a spreadsheet has become a database).

The layout is a report. A formatted report with merged headers and subtotals needs a data table behind it; ask for that instead of converting the presentation.

The meaning is unclear. If nobody can say what a column holds, a conversion only moves the confusion into another format.

Where to go next

If you need one output now, open its spoke: CSV for every sheet, JSON with Python, XML with a script or YAML config files. If the workbook is large, converting a huge Excel file to CSV or Parquet. If you are converting between the text formats themselves, converting CSV to JSON, XML and YAML and converting JSON to CSV, XML and YAML cover that ground.

The references are the pandas documentation of read_excel and the writers, RFC 4180 for CSV, the JSON specification, RFC 8259, the W3C XML 1.0 specification and the YAML 1.2 specification. To convert a small sample, use the CSV to JSON converter.