How to Convert CSV to JSON Without Losing Leading Zeros or £ Signs

To convert CSV to JSON, take the first row as field names and turn every following row into a JSON object with those names as keys, giving an array of objects. A browser-based converter does it in seconds. What goes wrong are almost always Excel-related: semicolon delimiters, a hidden byte order mark, and phone numbers that lose their leading zero.

CSV is still how most business systems export data: CRMs, e-commerce platforms, accounting software, booking tools and spreadsheets. JSON is what web apps, APIs and automation platforms expect. Moving between the two should be trivial, and it is, as long as you know where the traps are. This guide covers the basic conversion, the quirks of Excel CSV files in the UK and Europe, and how to keep your data intact.

The fastest route is our free CSV to JSON converter. It reads the file in your browser, so customer lists never leave your device. It handles quoted values, auto-detects commas, semicolons and tabs, previews the result as a table, and converts JSON back to CSV for Excel too.

What the conversion looks like

A typical CSV export:

name,phone,postcode,spend
Aisha Khan,07700900123,LS6 1AB,120.50
"Smith, John",07700900456,M1 1AE,85

Converted to JSON, it becomes an array of objects:

[
  {
    "name": "Aisha Khan",
    "phone": "07700900123",
    "postcode": "LS6 1AB",
    "spend": 120.5
  },
  {
    "name": "Smith, John",
    "phone": "07700900456",
    "postcode": "M1 1AE",
    "spend": 85
  }
]

Notice three things. The quoted name "Smith, John" stayed as one value even though it contains a comma. The phone numbers stayed as strings with their leading zero. And the spend column became numbers, which is right for amounts you'll calculate with. Each of those is a decision a good converter makes for you, and a bad one gets wrong.

To tidy or validate the result, paste it into a JSON formatter. Our guide to JSON formatting and validation explains common syntax errors.

The CSV rules (such as they are)

There's no single binding CSV standard. The closest thing is RFC 4180, an informational IETF document that describes common practice and registers the text/csv media type. Its main rules:

• Each record is on its own line, separated by line breaks.
• An optional header row comes first, with the same number of fields as the data rows.
• Fields are separated by commas.
• Fields containing commas, double quotes or line breaks should be wrapped in double quotes.
• A double quote inside a quoted field is escaped by doubling it: "She said ""hello""".

Real files bend these rules all the time, which is why splitting each line on commas never works for long. An address with a comma, or a notes field with a line break, breaks a naive split immediately. Always use a proper CSV parser.

Excel CSV quirks in the UK and Europe

Delimiters: comma or semicolon?

Excel doesn't always use commas. According to Microsoft's guide to importing and exporting text files, the default list separator is a comma, but it follows the Windows Region settings. Microsoft also notes that setting the decimal separator to a comma forces Excel to use a semicolon as the list separator.

That's why it matters for businesses that trade across borders. A UK or US machine uses a full stop for decimals and a comma as the delimiter. In many European countries, including Germany, France, Spain and the Netherlands, the decimal separator is a comma, so Excel there typically saves "CSV" files with semicolons:

naam;bedrag
Jansen;12,50

If you work with European suppliers, colleagues or customers, expect semicolon files. A good converter detects the delimiter automatically. If yours doesn't, set it by hand, and watch the decimal commas: 12,50 needs converting to 12.50 before it can become a JSON number.

Encoding and the UTF-8 byte order mark

Excel offers more than one CSV format when you save:

• CSV UTF-8 (Comma delimited) saves as UTF-8 with a byte order mark (BOM), three invisible bytes at the start of the file that tell programs it's UTF-8. Use this one for anything containing £, €, accented names or non-Latin scripts.
• CSV (Comma delimited) saves in the computer's legacy Windows code page rather than UTF-8. A £ sign may then turn into £ or � when another system reads the file as UTF-8.

The BOM solves one problem and causes another. If a converter doesn't strip it, it becomes part of the first header, so your first key is "name" instead of "name". Everything looks right on screen, but row.name returns undefined. It's one of the most baffling CSV bugs because the character is invisible.

The JSON standard, RFC 8259, says JSON exchanged between systems must be UTF-8, and that implementations must not add a BOM to networked JSON. So strip the BOM from the CSV when you read it, and never write one into the JSON.

Going the other way, Excel often opens UTF-8 CSV files correctly only when they have a BOM. That's why our converter can add one when saving JSON back to CSV for Excel.

Leading zeros, long numbers and dates

Leading zeros

UK mobile numbers start with 07, and many account numbers, product codes, employee IDs and sort codes start with zero. Excel strips them. Microsoft's own guidance on keeping leading zeros explains that Excel automatically removes leading zeros and converts large numbers to scientific notation so that calculations work.

The damage often happens before conversion. If someone opens a CSV in Excel and saves it again, 07700900123 becomes 7700900123 in the file itself. To avoid it:

1. Don't open and resave CSVs in Excel before converting. Convert the original export.
2. If you must use Excel, import rather than open. Use Data > From Text/CSV, and set columns such as phone numbers to Text in Power Query before loading.
3. In the JSON, keep these fields as strings. A phone number is an identifier, not a quantity. You'll never add two phone numbers together.

Numbers longer than 15 digits

Microsoft documents that Excel has a maximum precision of 15 significant digits. Digits after the 15th are changed to zeros. Long order references, card-style numbers and some tracking IDs are silently corrupted.

JSON has a related limit in many languages. RFC 8259 notes that integers are only guaranteed to be interoperable between −(2^53)+1 and (2^53)−1, about nine quadrillion, because many parsers store numbers as double-precision floats. JavaScript is one of them. Any ID that could exceed that range, or that has leading zeros, belongs in a JSON string.

Dates

Excel turns things that look like dates into dates, so a product code like 1-2 or MAR1 can change in the file. Dates are also ambiguous: 03/04/2026 is 3 April in the UK and 4 March in the US. When you convert to JSON, write dates in ISO 8601 format, 2026-04-03, which sorts correctly and can't be misread. For timestamps, see our guide to Unix timestamps and time zones.

Type detection: numbers, booleans and empty cells

Every CSV value is text. Converters guess types, and the guesses need checking:

The safest approach is to convert everything as strings first, then deliberately convert the columns you know are numbers or booleans. Our converter's optional type detection turns numbers, true/false and empty cells into JSON types, but keeps values with leading zeros, and integers too large to store exactly, as text.

Making nested JSON from flat CSV

CSV is flat. JSON can be nested. A common convention is dot notation in the headers:

name,address.street,address.city
Aisha Khan,1 Park Row,Leeds

Some converters and libraries turn that into {"name": "Aisha Khan", "address": {"street": "1 Park Row", "city": "Leeds"}}. Check whether the system you're feeding expects flat or nested data before choosing.

Converting with code

If you convert the same export regularly, a short script is more reliable than repeated manual steps.

Python (standard library only). The utf-8-sig encoding strips the BOM automatically:

import csv, json

with open("customers.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))  # add delimiter=";" for European files

with open("customers.json", "w", encoding="utf-8") as f:
    json.dump(rows, f, indent=2, ensure_ascii=False)

csv.DictReader keeps every value as a string, so leading zeros survive. Without utf-8-sig, the first key comes out as name.

PowerShell (built into Windows):

$rows = Import-Csv .\customers.csv          # add -Delimiter ';' if needed
ConvertTo-Json -InputObject @($rows) | Out-File customers.json -Encoding utf8

Wrapping the rows in @() makes sure a file with a single row still produces an array. In Windows PowerShell 5.1, -Encoding utf8 writes a BOM; PowerShell 7 writes UTF-8 without one by default.

JavaScript: there's no built-in CSV parser, so use a maintained library such as Papa Parse rather than splitting strings yourself.

Checking the result

Before you import JSON into anything important:

1. Count the records. The JSON array length should equal the CSV row count minus the header.
2. Check the first key for a stray BOM.
3. Spot-check fields that matter: phone numbers, IDs, postcodes, amounts with pence, and anything with £ or accented characters.
4. Look for shifted columns. A row with an unquoted comma pushes values into the wrong keys.
5. Compare against a previous good export if you have one. Our diff checker shows exactly what changed, and the guide to comparing two text files has other options.

Going back from JSON to CSV safely

When you convert JSON back to CSV for someone to open in Excel, watch out for CSV injection. OWASP's CSV injection page explains that cells beginning with =, +, - or @ can be interpreted as formulas by spreadsheet software. If the data came from website forms or customers, a malicious value could run a formula when a colleague opens the file. Escape such values, for example by prefixing a single quote, and be aware that Excel may drop that protection if the file is saved and reopened.

Privacy

CSV exports are often full of personal data: names, emails, phone numbers and order histories. Uploading them to an unknown online converter means sharing that data with a third party, which needs a proper basis under UK or EU GDPR and similar privacy laws. Use a converter that runs in your browser, a script on your own machine, or a trusted processor you have an agreement with.

When to automate it

If you're exporting CSV from one system, converting it and importing it into another every week, that job can usually be automated end to end, with checks for the problems above built in. E-commerce teams do this constantly with product feeds and order exports. Some feeds arrive as XML rather than CSV; our XML to JSON tool handles those. Our page of free tools for e-commerce collects related utilities, and e-commerce automation for UK businesses covers bigger workflows. If you'd like help, Make It Flow starts by mapping where the data comes from and where it needs to go. Talk to us.

FAQ

How do I convert a CSV file to JSON?

Open the CSV in a converter, which uses the header row as keys and turns each row into a JSON object, producing an array. You can also use a script: Python's csv.DictReader with json.dump, or PowerShell's Import-Csv piped to ConvertTo-Json. Check the delimiter, encoding and any fields with leading zeros before you use the result.

Can Excel export JSON directly?

Not with a simple Save As option. Excel saves CSV, and you then convert the CSV to JSON with a separate tool or script. Power Query can read JSON into Excel, and Office Scripts or VBA can write JSON, but for most people exporting CSV and converting it is quicker and less error-prone.

Why does my CSV use semicolons instead of commas?

Because Excel takes its list separator from Windows regional settings. In countries that use a comma as the decimal separator, such as Germany, France and the Netherlands, Excel usually saves CSV files with semicolons. Choose a converter that detects the delimiter automatically, or set it to semicolon manually when you convert.

How do I keep leading zeros when converting CSV to JSON?

Convert the original export without opening and resaving it in Excel, which strips the zeros. Make sure the converter treats those columns as text, so 07700900123 becomes the string "07700900123" rather than a number. Phone numbers, account numbers and codes are identifiers, so they should always be JSON strings.

What is the strange character at the start of my first JSON key?

It's almost certainly a UTF-8 byte order mark (BOM), the invisible  character Excel adds when you save as CSV UTF-8. If the converter doesn't strip it, it attaches to the first header name. Re-convert with a tool that removes it, or read the file with utf-8-sig encoding in Python.

Is it safe to convert CSV files online?

Only if the converter processes the file in your browser rather than uploading it. CSV exports often contain names, emails and phone numbers, and uploading them to an unknown server shares that personal data with a third party. Check the tool's privacy statement, or test it offline after the page loads.