I am trying to use JavaScript to convert a JSON File to a csv that can be opened in excel.
What I have:
function exportToCsv(filename, rows) {
var processRow = function (row) {
var finalVal = '';
for (var j = 0; j < row.length; j++) {
var innerValue = row[j] === null ? '' : row[j].toString();
if (row[j] instanceof Date) {
innerValue = row[j].toLocaleString();
};
var result = innerValue.replace(/"/g, '""');
if (result.search(/("|,|\n)/g) >= 0)
result = '"' + result + '"';
if (j > 0)
finalVal += ',';
finalVal += result;
}
return finalVal + '\n';
};
var csvFile = '';
for (var i = 0; i < rows.length; i++) {
csvFile += processRow(rows[i]);
}
var blob = new Blob([csvFile], { type: 'text/csv;charset=utf-8;' });
if (navigator.msSaveBlob) { // IE 10+
navigator.msSaveBlob(blob, filename);
} else {
var link = document.createElement("a");
if (link.download !== undefined) { // feature detection
// Browsers that support HTML5 download attribute
var url = URL.createObjectURL(blob);
link.setAttribute("href", url);
link.setAttribute("download", filename);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
}
}
exportToCsv('export.csv', 'FILENAME.JSON')
FILENAME.JSON (very detailed- nested example)
[{
"countyCode": 18,
"excludedFlag": null,
"fees": {
"annualTotalFees": 35.6,
"annualTotalFeesMail": 36.35,
"annualTotalFeesOnline": 38.35,
"biennialTotalFees": 71.2,
"biennialTotalFeesMail": 71.95,
"biennialTotalFeesOnline": 73.95,
"branchFeeFlag": false,
"delinquentFeeAmount": 5,
"mhBackTax": 0,
"mhBackTaxMonths": 0
},
"fileId": 876,
"id": "2398743-32403274-2398",
"messages": [
{
"messageCode": "RN2",
"messageText": "Plate must be replaced if a 2 year renewal is desired.",
"messageType": "I"
},
{
"messageCode": "RN40",
"messageText": "You must complete the affidavit on the reverse side or provide a copy of your Florida insurance identification card.",
"messageType": "I"
},
{
"messageCode": "RN41",
"messageText": "Insurance is on file.",
"messageType": "II"
}
],
"registrationDetail": {
"annualPlateReplacementFlag": false,
"arfCredit": 25.6,
"biennialPlateReplacementFlag": true,
"customerStopFlag": null,
"delinquentDate": "2018-02-11T00:00:00",
"expirationDate": "2018-01-09T00:00:00",
"foreignAddressFlag": false,
"honorayConsulPlateFlag": null,
"hovDecalNumber": null,
"hovDecalYear": null,
"hovExpirationDate": null,
"inventorySubtype": "RP",
"lastActivityCounty": 15,
"legislativePlateDueForReplacementFlag": false,
"licensePlateCode": "RPR",
"licensePlateNumber": "Plate",
"mailToAddressFlag": false,
"mailToCustomerNumber": null,
"mhLocationCode": null,
"militaryOwnerFlag": false,
"numberOfRegistrants": 1,
"onlineRenewalEligibilityFlag": true,
"pinNumber": 329874938,
"plateExpirationDate": "2018-03-18T00:00:00",
"plateIssueDate": "2008-09-18T00:00:00",
"possibleNgExemptionFlag": false,
"registrationNumber": 3945874398,
"registrationOnlyFlag": null,
"registrationType": "R",
"registrationUse": "PR",
"renewalCountyCode": 16,
"rentalParkFlag": null,
"seminoleMiccosukeeIndianFlag": false,
"taxCollectorRenewalEligibilityFlag": true,
"vehicleClassCode": 1
},
"registrationOwners": [
{
"customer": {
"addresses": [
{
"addressType": "R",
"city": "BRADENTON",
"countryCode": "US",
"countyCode": 16,
"foreignPostalCode": null,
"state": "FL",
"streetAddress": "24545 6th ave w",
"zipCode": 34205,
"zipPlus": null
},
{
"addressType": "M",
"city": "BRADENTON",
"countryCode": "US",
"countyCode": 16,
"foreignPostalCode": null,
"state": "FL",
"streetAddress": "24545 6th ave w",
"zipCode": 34205,
"zipPlus": 8344
}
],
"companyName": null,
"customerNumber": 3490436,
"customerStopFlag": false,
"customerType": "I",
"dateOfBirth": "1971-01-09T00:00:00",
"dlExpirationDate": "2025-01-09T00:00:00",
"dlRenewalEligibilityFlag": true,
"driverLicenseNumber": "N0932433902",
"emailAddress": null,
"feidNumber": null,
"firstName": "Test",
"lastName": "Name",
"middleName": "Name",
"militaryExemptionFlag": false,
"nameSuffix": null,
"sex": "F"
},
"registrationOwnershipNumber": 1
}
],
"shippingAddress": {
"address": {
"addressType": "S",
"city": "BRADENTON",
"countryCode": "US",
"countyCode": 15,
"foreignPostalCode": null,
"state": "FL",
"streetAddress": "24545 6th ave w",
"zipCode": 34205,
"zipPlus": 8344
},
"shippingCompanyName": null,
"shippingName1": "Test Name",
"shippingName2": null
},
"stops": null,
"vehicle": {
"address": null,
"bodyCode": "4D",
"brakeHorsePower": null,
"cubicCentimeters": null,
"grossWeight": null,
"insuranceAffidavitCode": null,
"leaseOwnerFlag": false,
"lengthFeet": null,
"lengthInches": null,
"majorColorCode": "GLD",
"makeCode": "CHEV",
"minorColorCode": null,
"netWeight": 3200,
"numberOfAxles": null,
"ownerUnitNumber": null,
"titleNumber": 32434324,
"vehicleIdentificationNumber": "1F5ZD5EU5BF32434234",
"vehicleNumber": 324334,
"vehicleType": "AU",
"vesselCode": null,
"vesselManufacturerDesc": null,
"vesselResidentStatus": "Y",
"vesselWaterType": null,
"widthFeet": null,
"widthInches": null,
"yearMake": 2011
}
}
]
I am trying to figure out how I can drop a json file into the bottom where it says FILENAME.JSON.
My end goal is to allow a user to select a file to convert a "large" json file to a csv file. Every month will have a new json file so I am trying to make it easy on the user to allow them to select the file that will be converted. Is there an easy way to tie this JavaScript to allow user to select a file?
Any help with this would be greatly appreciated.
EDIT (What Im trying below that has not worked yet. It spins as if it is doing something but never downloads the file or creates it.)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>json2csv</title>
<meta id="viewport" name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<meta name="apple-mobile-web-app-capable" content="yes">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div id="content"></div>
</body>
<script type="text/javascript">
fetch('http://path/to/json/file')
.then(function(response) {
function flatObjectToString(obj){
var s = ""
Object.keys(obj).map(key=>{
if(obj[key] === null){
s+= key +":"
}else if(obj[key].toLocaleDateString){
s+=key+": " +obj[key].toLocaleDateString() +"\n"
}else if(obj[key] instanceof Array){
s+= key+":\n" +listToFlatString(obj[key])
}else if(typeof obj[key] == "object"){
s+= key+":\n" +flatObjectToString(obj[key])
}else{
s+= key+":" +obj[key]
}
s+= "\n"
})
return s
}
function listToFlatString(list) {
var s = "";
list.map(item => {
Object.keys(item).map(key => {
s+="\n"
if (item[key] instanceof Array) {
s += key + "\n" + listToFlatString(item[key])
}else if(typeof item[key] == "object" && item[key] !== null){
s+= key+": " + flatObjectToString(item[key])
} else {
s += key + ": " + (item[key] === null ? "" : item[key].toLocaleDateString ? item[key].toLocaleDateString : item[key].toString())
}
})
})
return s;
}
function flatten(object, addToList, prefix) {
Object.keys(object).map(key => {
if (object[key] === null) {
addToList[prefix + key] = ""
} else
if (object[key] instanceof Array) {
addToList[prefix + key] = listToFlatString(object[key])
} else if (typeof object[key] == 'object' && !object[key].toLocaleDateString) {
flatten(object[key], addToList, prefix + key + '.')
}else{
addToList[prefix + key] = object[key]
}
})
return addToList
}
exportToCsv("download.csv", data.map(record=>flatten(record, {}, '')))
})
</script>
</html>
If all you want is to drop a JSON file, you have a few options:
You can paste in the JSON structure.
JSON.parse(`{"name":"Sample","Age":1}`);
You can load the file in using fetch
fetch('http://example.com/mylist.json')
.then(function(response) {
return exportToCsv("myfilename", response.json());
})
You can use this to flatten the listing:
function flatObjectToString(obj){
var s = ""
Object.keys(obj).map(key=>{
if(obj[key] === null){
s+= key +":"
}else if(obj[key].toLocaleDateString){
s+=key+": " +obj[key].toLocaleDateString() +"\n"
}else if(obj[key] instanceof Array){
s+= key+":\n" +listToFlatString(obj[key])
}else if(typeof obj[key] == "object"){
s+= key+":\n" +flatObjectToString(obj[key])
}else{
s+= key+":" +obj[key]
}
s+= "\n"
})
return s
}
function listToFlatString(list) {
var s = "";
list.map(item => {
Object.keys(item).map(key => {
s+="\n"
if (item[key] instanceof Array) {
s += key + "\n" + listToFlatString(item[key])
}else if(typeof item[key] == "object" && item[key] !== null){
s+= key+": " + flatObjectToString(item[key])
} else {
s += key + ": " + (item[key] === null ? "" : item[key].toLocaleDateString ? item[key].toLocaleDateString : item[key].toString())
}
})
})
return s;
}
function flatten(object, addToList, prefix) {
Object.keys(object).map(key => {
if (object[key] === null) {
addToList[prefix + key] = ""
} else
if (object[key] instanceof Array) {
addToList[prefix + key] = listToFlatString(object[key])
} else if (typeof object[key] == 'object' && !object[key].toLocaleDateString) {
flatten(object[key], addToList, prefix + key + '.')
}else{
addToList[prefix + key] = object[key]
}
})
return addToList
}
exportToCsv("download.csv", data.map(record=>flatten(record, {}, '')))