If you have ever received a “simple spreadsheet” that needs to become an API payload, a reporting file, or a clean import for another system, you already know the hard part is not clicking Export. The hard part is preserving dates, IDs, special characters, nested fields, and column meaning without creating a file that looks valid but breaks later. This cheat sheet gives you practical conversion rules for CSV, JSON, XML, and Excel so you can choose the right format, avoid silent data damage, and troubleshoot bad exports quickly.
Quick format chooser: use the format that matches the job
Each of these formats can store tabular data, but they are not interchangeable. Pick based on how the file will be used next.
Use CSV for simple table exchange
CSV is best when your data is a flat table: rows and columns, no nested objects, no formulas, no formatting. It is the safest choice for bulk imports into CRMs, email platforms, accounting tools, database loaders, and many admin dashboards.
Use CSV when:
Common examples: customer lists, order exports, product catalogs, transaction logs, mailing lists.
Best practical settings:
If your CSV contains commas inside values, quote those fields:
```csv id,name,notes 101,"Smith, Anna","Prefers email contact" ```
Do not manually remove quotes just because they look messy. They may be the only thing keeping the file valid.
Use JSON for APIs and structured data
JSON is best for web apps, APIs, configuration, and data that has nested structure. It can represent arrays, objects, numbers, booleans, nulls, and strings clearly.
Use JSON when:
Example JSON:
```json [ { "id": 101, "name": "Anna Smith", "active": true, "tags": ["retail", "newsletter"] } ] ```
If you are starting from a spreadsheet, clean your headers first, then convert. Header names become JSON keys, so avoid spaces, punctuation, and duplicates. Prefer:
```text customer_id first_name email created_at ```
Instead of:
```text Customer ID First Name! Email Address Date Created ```
For a fast table-to-API conversion, paste your cleaned CSV into CSV to JSON and check the first few objects before using the output in code or an API request.
Use XML for older systems, feeds, and document-style structure
XML is still common in legacy integrations, publishing workflows, tax/accounting exports, product feeds, and enterprise tools. It is more verbose than JSON but supports attributes, namespaces, comments, and strict validation through schemas.
Use XML when:
Example XML:
```xml
XML is less forgiving than CSV. A single unescaped ampersand can break the whole file. This is invalid:
```xml
This is valid:
```xml
Also escape `<` as `<` and `>` as `>` inside text values when needed.
Use Excel for human review, formulas, and multi-sheet workbooks
Excel files are best when people need to inspect, edit, filter, format, calculate, or approve data before it goes somewhere else. Excel is not just a data format; it is a workbook format.
Use Excel when:
Use `.xlsx` unless you have a specific legacy reason to use `.xls`. The older `.xls` format has row and column limits and is more likely to cause compatibility problems.
For final sharing, especially with clients or non-editors, convert the workbook to PDF only after checking print areas, page orientation, and scaling. If you need a fixed, shareable version of a spreadsheet report, use Excel to PDF after setting the sheet layout correctly.
Conversion rules that prevent silent data damage
The worst conversion problems are not obvious errors. They are files that open fine but contain changed values.
Treat IDs, ZIP codes, and SKU numbers as text
Spreadsheet software often tries to be helpful by converting values. That is dangerous for identifiers.
These values should usually be text:
If Excel sees `000456789`, it may display `456789`. If it sees a long account number, it may convert it to scientific notation. Before importing a CSV into Excel, use the import wizard and set these columns to Text. Do not just double-click the CSV file.
In Excel, a practical method is:
If you already opened and saved the file after Excel changed the values, the leading zeros may be gone. Re-export from the original source if possible.
Normalize dates before conversion
Dates are a common source of bad imports because `03/04/2026` can mean March 4 or April 3 depending on locale.
For data exchange, use ISO-style dates:
```text 2026-03-04 ```
For timestamps, use:
```text 2026-03-04T14:30:00Z ```
Use `Z` only if the time is UTC. If the time is local and you know the offset, use:
```text 2026-03-04T14:30:00-05:00 ```
Avoid month names in machine imports unless the target system specifically asks for them. `4 Mar 2026` is readable, but `2026-03-04` is safer for conversion.
Clean column headers before exporting
Headers become field names in CSV imports, JSON keys, XML tags, and database columns. Messy headers create avoidable mapping work.
Good header rules:
Good:
```text product_id unit_price_usd created_at shipping_weight_kg ```
Risky:
```text Product ID Price ($) Date Weight ```
If two columns are both named `Date`, a JSON conversion may overwrite one value or rename keys unpredictably. Rename them before converting, such as `order_date` and `ship_date`.
Decide how blanks should be represented
Blank values are not the same in every format.
In CSV, a blank field looks like this:
```csv name,email,phone Anna,[email protected], ```
In JSON, decide whether blank means an empty string:
```json "phone": "" ```
Or a missing/unknown value:
```json "phone": null ```
For APIs, `null` and `""` can behave differently. Some systems interpret an empty string as “erase this value” and `null` as “unknown.” Check the import documentation. If there is no documentation, test with two or three records before uploading the full file.
Practical conversion paths
CSV to JSON
Use this when you have a spreadsheet export and need an API-ready array of objects.
Before converting:
Example CSV:
```csv customer_id,first_name,email,active 101,Anna,[email protected],true 102,Marco,[email protected],false ```
Expected JSON:
```json [ { "customer_id": "101", "first_name": "Anna", "email": "[email protected]", "active": "true" }, { "customer_id": "102", "first_name": "Marco", "email": "[email protected]", "active": "false" } ] ```
Notice that many CSV converters treat everything as strings. If the receiving API needs real booleans, change `"true"` to `true` and `"false"` to `false`. If it needs numbers, change `"quantity": "5"` to `"quantity": 5`, but do not convert ID fields to numbers if leading zeros matter.
Excel to CSV
Use this when a human-maintained workbook needs to be imported into another system.
Steps:
One common mistake is exporting the wrong sheet. Excel saves the active sheet as CSV, not the entire workbook. If your workbook has `Instructions`, `Products`, and `Archive`, click the actual data sheet before saving.
JSON to CSV
Use this when you need to review API data in a spreadsheet or send it to a business user.
Flat JSON converts cleanly:
```json [ {"id": 1, "name": "Anna"}, {"id": 2, "name": "Marco"} ] ```
Nested JSON needs a decision. For this:
```json { "order_id": 5001, "customer": { "name": "Anna", "email": "[email protected]" } } ```
Flatten keys like this:
```text order_id customer_name customer_email ```
Arrays are harder. If an order has multiple line items, do not cram all products into one cell unless the target system accepts that. Usually you need one CSV for orders and another CSV for order lines.
Orders CSV:
```csv order_id,customer_email 5001,[email protected] ```
Order lines CSV:
```csv order_id,sku,quantity 5001,RED-SHIRT,2 5001,BLUE-HAT,1 ```
This preserves the relationship without creating unreadable cells.
XML to CSV or JSON
XML conversion depends heavily on structure. First identify the repeating element. In a customer file, it might be `
If the XML looks like this:
```xml
Then each `
Watch for attributes:
```xml
In CSV, map attributes to columns:
```text id status email ```
Namespaces can complicate parsing:
```xml
If your converter outputs empty results, namespaces are often the reason. Use a parser or converter that recognizes namespaces, or strip them only if the receiving workflow does not require them.
Troubleshooting common conversion failures
“My CSV opens with broken characters”
If names like `José` or `Müller` appear as strange symbols, the encoding is wrong. Export again as UTF-8. In Excel, choose “CSV UTF-8” rather than plain “CSV” when available. If importing, use the text import option and select UTF-8 manually.
“Rows are split in the wrong place”
This usually means a field contains a comma, quote, or line break that was not escaped correctly. A valid CSV field with a line break must be quoted:
```csv id,notes 101,"Customer requested: leave package at side door" ```
If the receiving system cannot handle line breaks inside fields, replace internal line breaks with a space before export.
“Excel changed my numbers”
This happens with long numbers, leading zeros, and values that look like dates. Import instead of opening directly. Set sensitive columns to Text. For existing Excel files, add an apostrophe before a value only as a last resort; it can become part of the exported data in some workflows.
“The JSON is valid, but the API rejects it”
Valid JSON is not the same as correct JSON. Check:
Test one record first. Then test five records with edge cases: blank email, special characters, long ID, non-English name, and a date.
“The XML file will not load”
Look for unescaped characters first. Ampersands are the usual culprit. Also check that every opening tag has a closing tag, tags are properly nested, and there is only one root element.
Invalid nesting:
```xml
Valid nesting:
```xml
If there is an XML schema file, validate against it before sending the file. A file can be well-formed XML but still fail the required schema.
A field checklist before you send the converted file
Before uploading or emailing a converted file, check these items:
For Excel specifically, also check:
A reliable conversion is mostly preparation: clean headers, preserve IDs as text, standardize dates, and test with a small sample. If your next step is turning a cleaned CSV into API-friendly JSON, try the BestAIFinds CSV to JSON tool and review the output before sending it to production.