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"
}
]
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"
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"
*/
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'
*/
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:
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:
However, when opening by clicking the csv file in your explorer or through chrome, Excel chooses another encoding, presumably this:
Exported with \uFEFF
This is how the file opens from start if we specify BOM using data:text/csv;charset=utf-8,\uFEFF
:
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)