You have data in one format and another system is asking for a different one: a CSV export from a spreadsheet, a JSON payload for an API, or an XML feed for an older business tool. After reading this, you should be able to choose the right conversion path, avoid broken fields and encoding problems, and produce files that import cleanly instead of failing with vague “invalid format” errors.
Know what each format is good at before converting
CSV, JSON, and XML can all hold structured data, but they do not describe that data in the same way. Most conversion problems happen because people treat them as interchangeable containers. They are not.
CSV is best for flat tables: one row per record, one column per field. A customer list is a good CSV candidate:
```csv id,name,email,status 101,Ana Lopez,[email protected],active 102,Samir Khan,[email protected],inactive ```
CSV becomes awkward when a field contains multiple values, such as several phone numbers, nested addresses, or a list of purchased items. You can force those into columns like `phone_1`, `phone_2`, and `phone_3`, but that is a workaround, not a natural fit.
JSON is better for applications and APIs because it supports nested objects and arrays. The same customer can have multiple phone numbers without inventing extra columns:
```json { "id": 101, "name": "Ana Lopez", "email": "[email protected]", "status": "active", "phones": ["+1-555-0101", "+1-555-0102"] } ```
XML is common in enterprise software, publishing systems, product feeds, finance tools, and older integrations. It is more verbose than JSON, but it supports attributes, namespaces, and document-style structure:
```xml
The practical rule is simple: use CSV for spreadsheet-style tables, JSON for APIs and app configuration, and XML when the receiving system specifically asks for XML or provides an XML schema.
Convert CSV to JSON without damaging your data
CSV to JSON is one of the most common conversions because many web apps accept JSON but business users often prepare data in spreadsheets. The cleanest approach is to fix the CSV first, then convert it.
Start by opening the CSV in a plain-text editor or a spreadsheet tool and check these items:
A simple CSV like this:
```csv customer_id,name,email,active 101,Ana Lopez,[email protected],true 102,Samir Khan,[email protected],false ```
usually converts to an array of JSON objects:
```json [ { "customer_id": "101", "name": "Ana Lopez", "email": "[email protected]", "active": "true" }, { "customer_id": "102", "name": "Samir Khan", "email": "[email protected]", "active": "false" } ] ```
Notice that values may become strings. Some tools infer data types and convert `101` to a number and `true` to a Boolean. Others keep every value as text. Neither is automatically wrong, but it matters if you are sending the JSON to an API.
If an API expects:
```json { "customer_id": 101, "active": true } ```
then this may fail:
```json { "customer_id": "101", "active": "true" } ```
Before uploading the final JSON, check the API documentation or a sample request. If numbers and Booleans must be real JSON types, use a converter that supports type detection, or edit the JSON afterward.
For a quick browser-based conversion, use CSV to JSON. Paste or upload your CSV, convert it, then inspect the output before using it in production. I usually check the first record, the last record, and one row with special characters, such as a comma in a company name or an accented character in a person’s name.
Handle commas, quotes, and line breaks correctly
CSV files often break because of commas inside values. This row is unsafe:
```csv company,city Smith, Jones Ltd,London ```
A parser may read that as three fields instead of two. The correct version wraps the company name in double quotes:
```csv company,city "Smith, Jones Ltd",London ```
If a value itself contains double quotes, double them inside the quoted field:
```csv product,description Mug,"12 oz mug with ""Best Seller"" label" ```
Line breaks inside cells are also allowed in CSV, but they must be inside quoted fields:
```csv id,note 1,"First line Second line" ```
If your converted JSON suddenly has shifted columns, missing values, or strange extra keys, inspect the CSV around the first bad row. The problem is usually an unescaped comma, an unmatched quote, or a line break pasted from an email.
Convert JSON to CSV for spreadsheets and reporting
JSON to CSV sounds simple until the JSON contains nesting. A flat JSON array converts neatly:
```json [ { "id": 1, "name": "Desk", "price": 199 }, { "id": 2, "name": "Chair", "price": 89 } ] ```
This becomes:
```csv id,name,price 1,Desk,199 2,Chair,89 ```
Nested JSON requires a decision. Suppose you have:
```json [ { "id": 1, "name": "Desk", "dimensions": { "width": 120, "height": 75 } } ] ```
A common CSV output flattens nested keys with dots or underscores:
```csv id,name,dimensions.width,dimensions.height 1,Desk,120,75 ```
That is usually fine for reporting. But arrays are trickier:
```json { "id": 1, "name": "Desk", "tags": ["office", "wood", "large"] } ```
You have three practical options:
For spreadsheet review, I prefer option 1 with a delimiter that does not appear in the data, such as a pipe character `|`. For database import, option 3 is often cleaner because it preserves the one-to-many relationship.
If JSON objects do not all have the same fields, your CSV will have blank cells. For example:
```json [ { "id": 1, "name": "Desk", "color": "black" }, { "id": 2, "name": "Chair" } ] ```
The CSV should include the union of all fields:
```csv id,name,color 1,Desk,black 2,Chair, ```
Do not delete empty columns too quickly. A blank value may be meaningful to the receiving system, especially during imports where “blank” means “clear this field.”
Convert CSV or JSON to XML carefully
XML is stricter about structure and naming than CSV. Element names cannot contain spaces, cannot start with a number, and should avoid punctuation. A CSV header like `Product Name` should become something like `product_name` or `productName`.
A CSV like this:
```csv sku,name,price A100,Desk Lamp,34.99 A101,Wall Clock,18.50 ```
can become XML like this:
```xml
Before generating XML, choose the root element and repeated item element deliberately. For product feeds, use a plural root like `
If the target system provides a sample XML file, copy its structure exactly. Pay attention to whether data belongs in elements or attributes.
Element style:
```xml
Attribute style:
```xml
Do not switch between these unless the receiving system allows it. XML importers can be very literal.
Escape special XML characters
XML reserves certain characters. If they appear in text values, they must be escaped:
```text & becomes & < becomes < > becomes > " becomes " inside attributes ' becomes ' inside attributes ```
This product name:
```text Salt & Pepper Set ```
must become:
```xml
If you see an XML error mentioning “entity” or “not well-formed,” look for an unescaped ampersand first. It is one of the most common XML conversion issues.
Convert XML to JSON or CSV without losing meaning
XML to JSON conversion depends on how attributes, repeated elements, and text nodes are represented. There is no single universal mapping.
This XML:
```xml
might become JSON like this:
```json { "product": { "@sku": "A100", "name": "Desk Lamp", "price": { "@currency": "USD", "#text": "34.99" } } } ```
Another converter might use `_attributes` and `_text` instead of `@` and `#text`. That is normal, but it matters if another tool expects a specific convention.
For XML to CSV, first identify the repeating record. In this file, the repeating record is `
```xml
That maps cleanly to:
```csv sku,name A100,Desk Lamp A101,Wall Clock ```
But this XML has nested repeated items:
```xml
A single CSV row cannot naturally store multiple item rows unless you flatten them. For order exports, I usually create one CSV row per item:
```csv order_id,sku,qty 5001,A100,2 5001,A101,1 ```
That repeats the `order_id`, but it keeps the data usable for spreadsheets and imports. Trying to cram all items into one cell may look tidy, but it often creates extra cleanup later.
Troubleshooting failed conversions
If a converted file fails to import, do not start by redoing the whole conversion. Check the exact point where the data first becomes invalid.
For CSV problems, look for:
If your spreadsheet uses semicolons because of regional settings, the file may look like this:
```csv id;name;email 1;Ana;[email protected] ```
A comma-based parser will treat each row as one field. In that case, choose semicolon as the delimiter during import, or replace semicolons with commas only after confirming semicolons are not used inside values.
For JSON problems, validate the syntax. Common mistakes include trailing commas, single quotes, unquoted keys, and comments:
Invalid JSON:
```json { name: 'Ana', "active": true, } ```
Valid JSON:
```json { "name": "Ana", "active": true } ```
For XML problems, check that there is exactly one root element, every opening tag has a matching closing tag, and special characters are escaped. This is invalid because it has two root elements:
```xml
Wrap it:
```xml
Also watch for data types. CSV has no real native type system, so dates, ZIP codes, product codes, and large IDs can be changed by spreadsheet software. A ZIP code like `02110` may become `2110`. A product code like `00123` may become `123`. To avoid that, format those columns as text before saving the CSV, or prefix values carefully if the receiving system supports it.
A practical conversion checklist
Before you send the converted file to a client, vendor, API, or internal system, run through this short checklist:
If you are converting CSV to JSON for an API, start with a small sample and inspect the output types before uploading the full file. Try the BestAIFinds CSV to JSON tool when you need a quick conversion, then use the checks above to make sure the result matches the system you are sending it to.