I'm a newbie about php and javascript and sorry for my bad English. I've searched and read many threads about this but nothing works for me.
I need to understand how I can export some fields of a json files into a CSV or excel with some of that fields and not all, ready to download.
I managed to get a json output with an array of data and print it in the console by clicking on a button. This is a script inside a php file. Now I need to convert this output in CSV and able to download it.
Here js code where I stored the array in var json :
<a id="csv_btn" class= "btn btn-primary btn-sm pull-right"
onclick="download_csv()">Download CSV</a>
<div class="js-search-box"></div>
<script type="text/javascript">
var json = <?php echo json_encode($outjson); ?>;
function download_csv () {
var formId = document.getElementById("csv_btn");
window.alert("Do yow want to download CSV?");
console.log(json);
}
</script>
In the images attached, there is the output after clicking on download.
Thanks in advance
enter image description here enter image description here
EDIT:
After many attempts, I've found a code working quite well; thanks to Danny Pule found at this link
Now, I'm trying to figure out how to create a filter to exclude or include certain fields and get the CSV with the wanted fields. Also, if someone could explain me how to extract data from a field that print [object Object] inside this json file (i.e. at "machine" field there is another array)
The code below:
<!--gabri-->
<a id="csv_btn" class= "btn btn-primary btn-sm pull-right" onclick="exportCSVFile(headers, itemsFormatted, fileTitle)">Download CSV</a>
<div class="js-search-box"></div>
<script type="text/javascript">
var datajson = <?php echo json_encode($outjson); ?>;
//download_csv(datajson);
function convertToCSV(objArray) {
var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
window.alert("Do yow want to download CSV?");
var str = '';
for (var i = 0; i < array.length; i++) {
var line = '';
for (var index in array[i]) {
if (line != '') line += ','
line += array[i][index];
}
str += line + '\r\n';
}
return str;
}
function exportCSVFile(headers, items, fileTitle) {
if (headers) {
items.unshift(headers);
}
// Convert Object to JSON
var jsonObject = JSON.stringify(items);
var csv = this.convertToCSV(jsonObject);
var exportedFilenmae = fileTitle + '.csv' || 'export.csv';
var blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
if (navigator.msSaveBlob) { // IE 10+
navigator.msSaveBlob(blob, exportedFilenmae);
} 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", exportedFilenmae);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
console.log(datajson);
}
}
}
var headers = {
label: 'Project Name'.replace(/,/g, ''), // remove commas to avoid errors
id: "id".replace(/,/g, ''),
active: "active",
start_date: "Start Date".replace(/,/g, ''),
year:"Year",
coord: "Coord",
perc: "perc",
plants:"Total Plants",
done: "Plants done",
machine: "Machine"
};
//itemsNotFormatted = [];
var obj = JSON.parse([datajson]);
var values = Object.keys(obj).map(function (key) { return obj[key]; });
//var values = Object.keys(obj).forEach(key => { console.log(key, obj[key]);});
console.log(values);
var itemsFormatted = values;
// format the data
/*itemsNotFormatted.forEach((item) => {
itemsFormatted.push({
label: item.label.replace(/,/g, ''), // remove commas to avoid errors,
id: item.id,
start_date: item.start_date,
coord: item.coord,
perc: item.perc,
plants: item.plants,
done: item.done,
machine: item.machine
});
});
*/
var fileTitle = 'monitor-report'; // or 'my-unique-title'
//exportCSVFile(headers, itemsFormatted, fileTitle); // call the exportCSVFile() function to process the JSON and trigger the download
</script>
This is the datajson I get from php
[{"label":"garbuiodiadoragrande","id":2176216,"active":0,"start_date":"23 mag, 2018","end_date":null,"perc":0.061996280223187,"plants":1613,"done":1,"machines":[{"label":"Trattore test 02 2016","id":3003}],"client":null,"client_id":null,"source":{"label":"Trattore test 02 2016","id":3003},"center_lat":45.777920164083,"center_lon":12.007139535756,"page":"\/customer\/projects\/2176216\/"},{"label":"prova","id":2176008,"active":0,"start_date":"21 mag, 2018","end_date":null,"perc":0.44247787610619,"plants":3842,"done":17,"machines":[{"label":"Trattore test 02 2016","id":3003}],"client":null,"client_id":null,"source":{"label":"Trattore test 02 2016","id":3003},"center_lat":43.830309706033,"center_lon":11.206148940511,"page":"\/customer\/projects\/2176008\/"}
and in the image attached below is the result I got from Object.map in the console.log (values), but I'm not sure to be in the right way and I'm quite confused.
enter image description here Thanks for any suggestions
EDIT: Ok, I got a code working and filtering the desired field
<script type="text/javascript">
Date.prototype.today = function () {
return ((this.getDate() < 10)?"0":"") + this.getDate() +"/"+(((this.getMonth()+1) < 10)?"0":"") + (this.getMonth()+1) +"/"+ this.getFullYear();
}
// For the time now
Date.prototype.timeNow = function () {
return ((this.getHours() < 10)?"0":"") + this.getHours() +":"+ ((this.getMinutes() < 10)?"0":"") + this.getMinutes() +":"+ ((this.getSeconds() < 10)?"0":"") + this.getSeconds();
}
var datetime = " @ " + new Date().today() + " @ " + new Date().timeNow();
var fileTitle = 'monitor-report'+ datetime; // or 'my-unique-title'
var datajson = <?php echo json_encode($outjson); ?>;
var itemsFormatted = JSON.parse([datajson]);
//download_csv(datajson);
function convertToCSV(objArray) {
var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
window.alert("Do yow want to download CSV?");
var str = '';
for (var i = 0; i < array.length; i++) {
var line = '';
for (var index in array[i]) {
if (line != '') line += ','
line += array[i][index];
}
str += line + '\r\n';
}
return str;
}
function exportCSVFile(headers, items, fileTitle) {
if (headers) {
items.unshift(headers);
}
// Convert Object to JSON
var jsonObject = JSON.stringify(items,
['label',
'id',
'start_date',
'year',
'end_date',
'perc',
'plants',
'done']);
var csv = this.convertToCSV(jsonObject);
var exportedFilenmae = fileTitle + '.csv' || 'export.csv';
var blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
if (navigator.msSaveBlob) { // IE 10+
navigator.msSaveBlob(blob, exportedFilenmae);
} 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", exportedFilenmae);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
console.log(jsonObject);
}
}
}
var headers = {
label: 'Project Name'.replace(/,/g, ''), // remove commas to avoid errors
id: "id",
active: "active",
start_date: "Start Date".replace(/,/g, ''),
year:"Year",
end_date:"End Date",
perc: "perc",
coord: "Coord",
plants:"Total Plants",
done: "Plants done",
};
//console.log(datajson);
</script>
But I can't figured out how to extract another array inside the main array. Here in "machines":[{"label":"Trattore test 02 2016","id":3003}], I need label and id. Anyone could help me)
[{"label":"garbuio diadoragrande","id":2176216,"active":0,"start_date":"23 mag,2018",
"end_date":null,"perc":0.061996280223187,"plants":1613,"done":1,
"machines":[{"label":"Trattore test 02 2016","id":3003}],
"client":null,"client_id":null,"source":{"label":"Trattore test 02 2016","id":3003},"center_lat":45.777920164083,"center_lon":12.007139535756,"page":"\/customer\/projects\/2176216\/"},
Finally I got a working code. Probably it's not perfect but it works! Here is the code if it might be useful for someone.
Thanks to the community
<script type="text/javascript">
Date.prototype.today = function () {
return ((this.getDate() < 10)?"0":"") + this.getDate() +"/"+(((this.getMonth()+1) < 10)?"0":"") + (this.getMonth()+1) +"/"+ this.getFullYear();
}
// For the time now
Date.prototype.timeNow = function () {
return ((this.getHours() < 10)?"0":"") + this.getHours() +":"+ ((this.getMinutes() < 10)?"0":"") + this.getMinutes() +":"+ ((this.getSeconds() < 10)?"0":"") + this.getSeconds();
}
var datetime = " @ " + new Date().today() + " @ " + new Date().timeNow();
var fileTitle = 'Report_Monitor'+ datetime; // or 'my-unique-title'
var datajson = <?php echo json_encode($outjson); ?>;
var itemsFormatted = JSON.parse([datajson]);
//var itemsFormatted = obj;
function convertToCSV(objArray) {
var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;
window.alert("Do yow want to download CSV?");
var str = '';
for (var i = 0; i < array.length; i++) {
var line = '';
for (var index in array[i]) {
if (line != '') line += ','
line += array[i][index];
}
str += line + '\r\n';
}
return str;
}
function exportCSVFile(headers, items, fileTitle) {
if (headers) {
items.unshift(headers);
}
// Convert Object to JSON
var jsonObject = JSON.stringify(items,
['label',
'id',
"start_date".replace(/,/g, ' '),
'end_date',
'perc',
'plants',
'done',
'center_lat',
'center_lon']);
var jsonObjectFiltered = jsonObject.replace(/,(?!["{}[\]])/g, "");
console.log(jsonObjectFiltered);
var csv = this.convertToCSV(jsonObjectFiltered).replace(/null/g,"0");
var exportedFilenmae = fileTitle + '.csv' || 'export.csv';
var blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
if (navigator.msSaveBlob) { // IE 10+
navigator.msSaveBlob(blob, exportedFilenmae);
} 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", exportedFilenmae);
link.style.visibility = 'hidden';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
console.log(jsonObject);
}
}
}
var headers = {
label: 'Project Name', // remove commas to avoid errors
id: 'id',
active: 'active',
start_date: 'Start Date',
//year:'Year',
end_date:'End Date',
perc: 'Percentage',
plants:'Total Stakes',
done: 'Stakes Done',
center_lat: 'Lat',
center_lon: 'Lon'
};
//console.log(datajson);
</script>