Convert Json to CSV with specific headers and format

Question

My code recives json from an endpoint and converts it to CSV file.

Can I set specific headers and specific orders I want int this CSV file?

function downloadJSONAsCSV(endpoint) {
    // Fetch JSON data from the endpoint
    fetch(endpoint)
        .then(response => response.json())
        .then(jsonData => {
            // Convert JSON data to CSV
            let csvData = jsonToCsv(jsonData.items.data); // Add .items.data
            // Create a CSV file and allow the user to download it
            let blob = new Blob([csvData], { type: 'text/csv' });
            let url = window.URL.createObjectURL(blob);
            let a = document.createElement('a');
            a.href = url;
            a.download = 'data.csv';
            document.body.appendChild(a);
            a.click();
        })
        .catch(error => console.error(error));
}
function jsonToCsv(jsonData) {
    let csv = '';
    // Get the headers
    let headers = Object.keys(jsonData[0]);
    csv += headers.join(',') + '\n';
    // Add the data
    jsonData.forEach(function (row) {
        let data = headers.map(header => JSON.stringify(row[header])).join(','); // Add JSON.stringify statement
        csv += data + '\n';
    });
    return csv;
}

Desired CSV:

"Batch Code","Order Id","Batch Status","Batch Expiration Date","Total","Used","Remaining","Redemption Method","Type","Organization Group","Employee ID","Selection Type"
"B-E7BE5602-2F9B-E3","11608501","Active","2023-06-29","1","0","1","ID CARD","OPEN","Yes","Yes","FLOWER"
"B-480A8929-57D5-97","11608502","Active","2023-06-29","1","0","1","ID CARD","OPEN","No","No","FLOWER"

Json input that I need to convert into CSV:

{
    "numOfItems": {
        "records": 2,
        "data": [{
                "numOfIDs": 1,
                "productId": null,
                "askOrgId": "Yes",
                "orderId": 11608501,
                "orgSelectionType": "FLOWER",
                "batchCode": "B-E7BE5602-2F9B-E3",
                "Type": "OPEN",
                "batchId": 413,
                "creationDate": "2022-06-29",
                "isOnline": "Yes",
                "productName": null,
                "batchProductArray": [{
                        "ID": 663255,
                        "TYPE": "PRODUCT",
                        "NAME": "SomeName"
                    }
                ],
                "numOfUsedPassports": 0,
                "redemptionMethod": "ID Card",
                "askSSN": "No",
                "askEmployeeId": "Yes",
                "batchStatus": "Active",
                "productType": null,
                "expirationDate": "2023-06-29"
            }
        ],
        "draw": 1,
        "recordsTotal": 2
    }
}

I tried to map this json into a specific format but I did not succeded with that.

There is no point in showing the retrieval code. Show some sample jsonData instead — Andrew Parks, Jan 27 '23 at 09:19
Do you have any rules to tell, *which* fields you want to have in *what* order? Right now, you are just getting *all* headers in a (more or less, ie just depending on the original JSON) random order. — derpirscher, Jan 27 '23 at 09:24
And for the mapping part. Remove that `JSON.stringify` and just use `headers.map(h => \`"${row[h]}"\`).join(",")` Of course this will break your CSV structure if one of your `row[h]` is a complex object. But this is also the case with `JSON.stringify` CSV generation is not always an easy task. If you have complex requirements, I suggest to use one of the multiple CSV packages available which handle things like escaping and much more ... — derpirscher, Jan 27 '23 at 09:32
I don't have any mapping defined - how can I define that in JS? — Lenny, Jan 27 '23 at 09:54

Jan Pfeifer · Accepted Answer · 2023-01-27T09:58:58.880

I would suggest to prepare one array to define property names and order and one array of actual column names. Then code may be something like this:

const data = [{
    "numOfIDs": 1,
    "productId": null,
    "askOrgId": "Yes",
    "orderId": 11608501,
    "orgSelectionType": "FLOWER",
    "batchCode": "B-E7BE5602-2F9B-E3",
    "Type": "OPEN",
    "batchId": 413,
    "creationDate": "2022-06-29",
    "isOnline": "Yes",
    "productName": null,
    "batchProductArray": [{
        "ID": 663255,
        "TYPE": "PRODUCT",
        "NAME": "SomeName"
    }
    ],
    "numOfUsedPassports": 0,
    "redemptionMethod": "ID Card",
    "askSSN": "No",
    "askEmployeeId": "Yes",
    "batchStatus": "Active",
    "productType": null,
    "expirationDate": "2023-06-29"
}
];

const header = ["Batch Code","Order Id"];
const headerProps = ["batchCode", "orderId"]
let csv = header.map(s => "\"" + s + "\"").join(",") + "\n";

data.forEach( r => {
    headerProps.forEach((c,i) => csv += (i>0 ? ", " : "")+ "\"" +r[c]+ "\"")
    csv += "\n";
})

console.log(csv);

UPDATE: Or as derpirscher pointed out, a simpler version:

data.forEach( r => csv += headerProps.map(h => `"${r[h]}"`).join(",") + "\n")

Why implementing the comma separation yourself when there is the handy [`Array.join`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/join) which does exacly that? And `headersProps.map(h => \`"${row[h]}"\`).join(",")` seems a bit more readable than your `forEach` — derpirscher, Jan 27 '23 at 09:46
@JanPfeifer I get undefined for the fields inside batchProductArray array. How can I modify this to get these values? — Lenny, Jan 29 '23 at 16:53
@Lenny Well that is completely different question. With nested array it dependes on what do you need exactly? Always the first object or do you need to get it by index? Check https://stackoverflow.com/questions/6906108/in-javascript-how-can-i-dynamically-get-a-nested-property-of-an-object — Jan Pfeifer, Jan 30 '23 at 08:10

Convert Json to CSV with specific headers and format

1 Answers1