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 plete 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
}
],
"panyName": 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=".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>
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 plete 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
}
],
"panyName": 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./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>
- Are you asking how to implement the UI/View layer of your application to accept a drag/drop file? Or is the code you posted to do the conversion not working? – Joshua Manns Commented Mar 21, 2018 at 17:30
- I am trying to figure out both unfortunately. I am new to all of this – David Brierton Commented Mar 21, 2018 at 17:40
2 Answers
Reset to default 1If 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./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, {}, '')))
This has definitely been asked before, but here is a good example:
https://medium./@danny.pule/export-json-to-csv-file-using-javascript-a0b7bc5b00d2
rename your source JSON to a variable such as itemsNotFormatted and then convert them to a new Array to match headers
itemsFormatted = []
// format the data
itemsNotFormatted.forEach((item) => {
itemsFormatted.push({
col1: item.col1.replace(/,/g, ''), // remove mas to avoid errors,
col2: item.col2,
col3: item.col3,
col4: item.col4
});
});
Then when you call you actual exportToCsv() you have the formatted array as an argument.