Go from CSV to JSON without losing data
Published · Updated 2 min read
A CSV file is a table written as text, one line per record, with the columns separated by a character. JSON is another way of writing data, more practical for programs and websites. Converting from one to the other looks simple, but four details often make you lose or distort data.
The separator
In a CSV, the columns are separated by a comma or, very often in France, by a semicolon. The reason lies in the numbers: in French, 24.5 is written with a comma, which therefore cannot also separate columns. The tool recognizes both separators, so a file saved from a French spreadsheet is read correctly, with 24,5 kept in a single column.
Quotation marks
When a value itself contains the separator, it is written between quotation marks, for example "watering: morning, evening". The tool then keeps the value in one piece. If the text itself contains quotation marks, they are doubled in the CSV and become escaped quotation marks in the JSON, for example "zone \"north\"".
Numbers
All values arrive as text in the JSON, numbers included: 18 becomes "18" and 24,5 becomes "24,5". This is the point to know if a program reads the file afterwards. A program that expects a real number will not understand "24,5": you will need to replace the comma with a point, and remove the quotation marks around numeric values.
Empty fields
An empty CSV field becomes an empty string in the JSON, two quotation marks with nothing between them, and not the value "null". If your program tells "empty" from "missing", plan for this cleaning step.
When to convert
A spreadsheet exported from a shop, a contact list or a series of readings is converted to JSON when a site or a program needs to read it. The reverse conversion is used to open in a spreadsheet data received from an online service.
Check the result
Compare the number of lines in the CSV with that of the JSON, then open the file you get. Look first at the columns that contain decimals, accents or commas: those are where mistakes appear.
To go the other way, see JSON to CSV.
Frequently asked questions
- Why do my numbers become text in the JSON?
- The tool writes all values as text, numbers included. If a program expects a real number, replace the decimal comma with a point and remove the quotation marks around numeric values.
- My CSV uses semicolons, is it read correctly?
- Yes, the tool recognizes the semicolon like the comma, which matches French spreadsheets.
- What happens to an empty cell?
- It becomes an empty string in the JSON, not the value "null".
- How do you keep a comma inside a value?
- It must be between quotation marks in the CSV, like "watering: morning, evening". The tool then keeps the value in one piece.