CSV and Spreadsheet Data Conversion Guide

Data conversion sits at the heart of most analytics workflows: an export from one system in CSV, the next system wants JSON, the analyst wants it in Excel, the engineer wants XML. This guide is the practical playbook for getting between formats without losing your data — and which of our free tools handles each conversion.

CSV to Excel: the easiest conversion method

Three options, each tractable:

1. Just open the CSV in Excel. Yes — modern Excel handles CSV natively. File → Open → select the CSV. Excel detects delimiters automatically. The catch: Excel sometimes munges fields it thinks are dates or numbers (leading zeros stripped, ZIP codes turned into scientific notation). For data integrity, use Power Query (Data → From Text/CSV) instead — gives you column-type controls.
2. Use our CSV to Excel converter. Paste CSV, download a real Excel file (.xls SpreadsheetML 2003 format) that Excel, Google Sheets, and LibreOffice all open natively. Browser-only, no upload, preserves number-vs-string column types. Best when you want to share an Excel file specifically.
3. Google Sheets import. File → Import → Upload CSV. Cleaner type detection than Excel for most cases. Free, but goes through Google’s servers.

The hidden gotcha most people hit: Excel auto-converts certain strings to dates or numbers. If you have a ZIP code column with leading zeros (“01234”), columns of trade IDs that look like numbers, or anything date-like, Excel will silently corrupt them. Always use Power Query (Data → From Text/CSV) for production data — it lets you lock column types before import.

JSON to CSV for data analysis

Use our JSON to CSV converter for the basic flat case (an array of objects with simple fields). The trickier cases:

• Nested objects: the converter flattens nested fields into dotted-path columns (address.city, address.zip). Works for 1–2 levels of nesting; deeper nesting gets unwieldy.
• Arrays of arrays: the converter indexes them (tags[0], tags[1]). For variable-length arrays, the CSV will have NULL columns for shorter rows. Acceptable for most cases; consider unrolling into multiple rows if your downstream analysis tool expects normalized data.
• Mixed schemas: if some objects have fields others don’t, the union of all keys becomes the header set. Empty cells for missing fields. This is usually correct.
• Very large files: browser-side conversion is fine up to ~100 MB. Above that use a CLI tool like jq + a CSV formatter, or Pandas in Python.

XML to CSV step-by-step

Use our XML to CSV converter. The workflow:

1. Identify the row element. What’s the repeating unit in your XML? In a customer export it’s usually <customer> or <record>. Our tool auto-detects the first repeating child of the root, but you can override with a CSS selector.
2. Paste the XML. The tool parses with the browser’s native DOMParser. Errors (mismatched tags, invalid characters) surface immediately.
3. Verify the row + column count. If the count looks wrong (e.g. 1 row when you expected 100), the row selector is probably wrong. Set it manually.
4. Download or copy the CSV. Done.

For deeply nested or hierarchical XML (3+ levels of nested elements with arrays inside arrays), CSV isn’t the right output format — you’ll get a wide sparse table. Convert to JSON first, then process with a real ETL tool.

Preserving file structure when converting formats

The general principle: lossy conversions are unidirectional. Once you go from a richer format to a flatter one (XML → CSV, JSON → CSV, DOCX → plain text), you can’t recover the original structure perfectly. Plan accordingly:

• Keep the original. Always. Storage is cheap, regret is expensive.
• Document the conversion choices. If you flattened nested fields with dotted-path names, write down what you did. Six months later when you need to reconstruct, you’ll thank yourself.
• Use intermediate formats wisely. XML → JSON (rich → rich) preserves structure. JSON → CSV (rich → flat) loses it. If you need to convert XML → CSV → back to XML, expect data loss.
• For multi-step conversions: do as few steps as possible. Each conversion can introduce errors. Direct XML → JSON is better than XML → CSV → JSON.

Common pitfalls in data conversion

• Date formatting: CSV doesn’t have a date type. Different tools interpret “01/02/03” differently (Jan 2 2003 vs Feb 1 2003 vs whatever). Always use ISO 8601 (2003-01-02) for portability.
• Leading zeros: ZIP codes, IDs, account numbers get auto-converted to integers and lose their leading zeros. Quote them as strings in CSV ("01234") or use Power Query in Excel.
• Encoding: non-ASCII characters (accents, emoji, non-Latin scripts) need UTF-8. Excel CSV import sometimes defaults to Windows-1252 — pick “Unicode (UTF-8)” in the Power Query wizard.
• Quoted fields with commas: RFC 4180 says quote them. Some bad CSV writers don’t. If your converter chokes, the input is malformed — clean it before converting.
• Excel’s row limit: Excel handles ~1M rows. Larger files: split into multiple sheets, or use a real database.
• Special characters in column names: some tools don’t allow dots, slashes, or spaces. Sanitize column names before import to BI tools, databases, or anything programmatic.