DEV Community

Andrew Elans
Andrew Elans

Posted on • Edited on

JSON to CSV for Excel: simple object

#csv #javascript

Let's now convert simple JSON data to CSV with JavaScript, export and open CSV file in Excel.

JSON data

Note characters æ å ø, " double quotes and , comma in supplier_name.

fa stands for frame agreement.

const dataArr = [ { "fa_number": "2024.01.001", "fa_name": "Butterfly Valves", "fa_exp_date": "2026-12-31", "supplier_name": "Valves Company Inc", "country": "US" }, { "fa_number": "2024.01.002", "fa_name": "Fasteners", "fa_exp_date": "2024-10-15", "supplier_name": "Best Fasteners, \"Ever\" LLC", "country": "US" }, { "fa_number": "2023.08.021", "fa_name": "Cleaning Services", "fa_exp_date": "2025-07-25", "supplier_name": "Øyvind Åssheims Næring AS", "country": "Norway" } ]
Enter fullscreen mode Exit fullscreen mode

CSV header 

const headerStr = Object.keys(dataArr[0]).map( (key) => `"${key}"` ).join(',') + '\r\n' // headerStr (type: string) => // "fa_number","fa_name","fa_exp_date","supplier_name","country"
Enter fullscreen mode Exit fullscreen mode

Knowing that all objects of the given array have identical structure, we take the very first object of the data array dataArr[0] and extract column names using Object.keys() method. We then produce an array of column names enclosed in " double quotes using Array.map() that we finally convert to a string, concatenating values with comma using Array.join("") method and adding line break at the end.

I choose to wrap all values in double quotes to avoid extra coding for escaping commas. But this is not obligatory as per RFC4180 - ref. previous post.

CSV body 

const bodyStr = dataArr.map( (faObj) => { const row = Object.values(faObj).map( (value) => `"${value.replaceAll('"', '""')}"` ) return row + '\r\n' } ).join('') /* bodyStr (type: string) => "2024.01.001","Butterfly Valves","2026-12-31","Valves Company Inc","US" "2024.01.002","Fasteners","2024-10-15","Best Fasteners, ""Ever"" LLC","US" "2023.08.021","Cleaning Services","2025-07-25","Øyvind Åssheims Næring AS","Norway" */
Enter fullscreen mode Exit fullscreen mode

Here we apply similar approach as for the header to all dataArr objects, extracting values, replacing single " with double "" as per RFC4180, enclosing values in double quotes and adding line break for each row.

Export CSV file 

 // with BOM window.open("data:text/csv;charset=utf-8,\uFEFF" + headerStr + bodyStr) // without BOM // window.open("data:text/csv;charset=utf-8," + headerStr + bodyStr) /* \uFEFF can be replaced with %EF%BB%BF encodeURIComponent('\uFEFF') => '%EF%BB%BF' */
Enter fullscreen mode Exit fullscreen mode

This will export a file download.csv.

download is user-agent provided name and cannot be changed when using window.open(). I will show another method in the next post.

Difference between with/without \uFEFF

We don't need to use Byte Order Mark if we don't use Excel, but if we do, then some Latin-1 characters will not be readable.

Exported without \uFEFF

Here is example of the export done without BOM using data:text/csv;charset=utf-8,. That's what I get when clicking to open the file:

Image description

Notice that Øyvind Åssheims Næring AS is generated as Øyvind Ã…ssheims Næring AS. Why? Because we haven't provided Byte Order Mark.

Let's convert this file manually in Excel through Data tab => From Text/CSV => Choose the generated file download.csv => click Import. By opening the file in this way, Excel picks up the right encoding UTF-8 from start as follows:

Image description

However, when opening by clicking the csv file in your explorer or through chrome, Excel chooses another encoding, presumably this:

Image description

Exported with \uFEFF

This is how the file opens from start if we specify BOM using data:text/csv;charset=utf-8,\uFEFF:

Image description

Export complex nested JSON to CSV

Next, I will give example of converting a more complex JSON with nested objects into CSV which I use in production and show another exporting method to specify the file name...

Top comments (0)