I want to convert this object into CSV file. The column names should be keys, this is small piece of array. And the last array will be only one of kind(keys), all other array will have same keys but different values.
[{
Comment: "Good",
Experince Months: "4",
Experince Years: "4",
Score: "3",
Subject: "CPP",
Topic: "Scripting (mention details)"
},
{
Comment: "Excilent",
Experince Months: "6",
Experince Years: "6",
Score: "6",
Subject: "CSharp",
Topic: "WPF"
},
{
Anything else worth highlighting: "Web Specialist",
Result: "Selected",
Total Business Analysis Experience: false,
Total Project Management Experience: false,
Total Score: 75,
Total Server Side Development Experience: true,
Total Server Side Support Experience: true,
Total UI Development Experience: true,
Total UI Support Experience: true
}]
I want to convert this object into CSV file. The column names should be keys, this is small piece of array. And the last array will be only one of kind(keys), all other array will have same keys but different values.
[{
Comment: "Good",
Experince Months: "4",
Experince Years: "4",
Score: "3",
Subject: "CPP",
Topic: "Scripting (mention details)"
},
{
Comment: "Excilent",
Experince Months: "6",
Experince Years: "6",
Score: "6",
Subject: "CSharp",
Topic: "WPF"
},
{
Anything else worth highlighting: "Web Specialist",
Result: "Selected",
Total Business Analysis Experience: false,
Total Project Management Experience: false,
Total Score: 75,
Total Server Side Development Experience: true,
Total Server Side Support Experience: true,
Total UI Development Experience: true,
Total UI Support Experience: true
}]
- You might want to see this link. – chris97ong Commented Apr 1, 2014 at 11:45
- i want to make a Excel file of that object – User1038 Commented Apr 1, 2014 at 11:49
- Do you want to Create Excel file by Javascript??? – Sajitha Rathnayake Commented Apr 1, 2014 at 11:51
- 1 yes output should be in CSV format file, i want to open it in excel – User1038 Commented Apr 1, 2014 at 11:56
- 1 @rdonatoiop actually my object format was right – User1038 Commented Apr 1, 2014 at 11:58
4 Answers
Reset to default 16This is a simple implementation for TSV (for csv, see the comment on this answer):
// Returns a csv from an array of objects with
// values separated by tabs and rows separated by newlines
function CSV(array) {
// Use first element to choose the keys and the order
var keys = Object.keys(array[0]);
// Build header
var result = keys.join("\t") + "\n";
// Add the rows
array.forEach(function(obj){
result += keys.map(k => obj[k]).join("\t") + "\n";
});
return result;
}
If you want to convert objects where the keys are not always the same or present, here's a modified version of barshopen's answer
function convertToCSV(array) {
// Collect all the different keys
let keys = Object.keys(Object.assign({}, ...array));
// Build header
var result = keys.join(",") + "\n";
// Add the rows
array.forEach(function (obj) {
//If there's a field, use the field, else, just add a blank field.
result += keys.map((k) => {
let item = "";
if (obj[k]) item = obj[k];
return item
}).join(",") + "\n";
});
return result;
}
To use the csv format as I have, make sure that all fields are surrounded by quotes too.
This is my dynamic and more complex approach to the matter. It should cover what's possible with support for simple values, objects (i.e., one row) and arrays (i.e., multiple rows with headers).
Note: as far as Dates are concerned I recommend to convert them to timestamps first as the various different formats in the world are confusing.
function toCsv(obj, columnDelimiter, lineDelimiter) {
// configure this according to your location and project needs:
const COLUMN_SEPARATOR = ",";
const NUMERIC_COMMA = ".";
function convertSimpleValue(value, columnDelimiter) {
if (value == null || value == undefined) {
return "";
}
let type = typeof(value);
columnDelimiter ||= COLUMN_SEPARATOR;
value = String(value);
if (type == "number" && NUMERIC_COMMA != ".") {
value = value.replace(".", NUMERIC_COMMA);
}
// converting \n to \\n is not part of CSV!
if (value.includes("\"")) {
value = value.replace(/"/g, "\"\"");
}
if (value.includes("\"") || value.includes(columnDelimiter) || value.includes("\n")) {
value = `"${value}"`;
}
return value;
}
function buildKeys(...objs) {
let keys = [];
for (let obj of objs) {
for (let key in obj) {
if (!keys.includes(key)) {
keys.push(key);
}
}
}
return keys;
}
function convertObject(obj, columnDelimiter, keys) {
if (obj == null || obj == undefined) {
return "";
}
if (typeof(obj) != "object") {
return convertSimpleValue(obj, columnDelimiter);
}
columnDelimiter ||= COLUMN_SEPARATOR;
keys ||= buildKeys(obj);
let values = [];
// for..of works differently compared to Object.values() and Object.entries()
for (let key of keys) {
values.push(convertSimpleValue(obj[key], columnDelimiter));
}
return values.join(columnDelimiter);
}
function convertArray(arr, columnDelimiter, lineDelimiter) {
if (arr == null || arr == undefined || !arr.length) {
return "";
}
columnDelimiter ||= COLUMN_SEPARATOR;
lineDelimiter ||= "\n";
let keys = buildKeys(...arr);
let lines = [
keys.map(convertSimpleValue).join(columnDelimiter),
...arr.map(obj => convertObject(obj, columnDelimiter, keys))
];
return lines.join(lineDelimiter);
}
if (Array.isArray(obj)) {
return convertArray(obj, columnDelimiter, lineDelimiter);
}
return convertObject(obj, columnDelimiter);
}
I'm still learning the latest ECMA-stuff so some things may be shortened.
To test:
console.log("string", toCsv("string"));
console.log("str-com", toCsv("string,part2"));
console.log("str-dq", toCsv("string\"part2"));
console.log("number", toCsv(123));
console.log("float", toCsv(123.456));
console.log("object", toCsv({ x: "val1", num: 2 }));
console.log("array", toCsv([
{ x: "val1", num: 21 },
{ x: "val2", num: 22 },
{ x: "val3", num: 23 },
{ x: "right\"there", s: "t;t", "y\"z": "line1\nline2" }
]));
Anha!! actually I have a PHP Class which works pretty well with same json object key (you are using here diffrent json key in third set). So, if you like then you can modify my PHP Export Class as you want (for different object key). Here I am going to explain example and going to share my class with you too. Hope, after modify this class your wish come true :)
PHP Export Class [Class.Export.php]
<?php
/**
* Class Export
*
* Send JSON data and make an array to save as Excel file
*
* @author neeraj.singh
* @version 1.0
*
*/
// Class Start Here
class Export {
/**
* Set Excel file name
*
* @var string
*/
public $filename = 'excel-doc';
/**
* Set Excel field title
*
* @var string
*/
public $custom_titles;
/**
* Get JSON data and convert in Excel file
*/
public function saveAsExcel() {
$CSV = trim ( $_POST ['exportdata'] );
if (function_exists ( 'json_decode' )) {
$data = json_decode ( $CSV, true );
if (count ( $data ) > 0) {
// call excel export
$this->_createExcelByArray ( $data );
} else {
die ( "Sorry!! array not build." );
}
} else {
die ( "Sorry!! json_decode not working on this server." );
}
}
/**
* Take an array and create
* Excel file
*
* @param array $dataArray
*/
private function _createExcelByArray($dataArray) {
// set excel file name
$this->filename = 'DEMO-Excel' . '-' . date ( 'd-m-Y-H-s' );
// get array field by first element of array
foreach ( $dataArray [0] as $k => $v ) {
$field [] = $k;
}
// get total no of field in array
$totalFields = count ( $field );
$i = $j = 0;
// get array values
foreach ( $dataArray as $v ) {
for($j; $j < $totalFields; $j ++) {
$value [$i] [] = $v [$field [$j]];
}
$i ++;
$j = 0;
}
$this->initExcel ( $field, $value );
}
/**
* Creating an Excel file with array data
*
* @param array $titles
* @param array $array
*/
public function initExcel($titles, $array) {
$data = NULL;
if (! is_array ( $array )) {
die ( 'The data supplied is not a valid array' );
} else {
$headers = $this->titles ( $titles );
if (is_array ( $array )) {
foreach ( $array as $row ) {
$line = '';
foreach ( $row as $value ) {
if (! isset ( $value ) or $value == '') {
$value = "\t";
} else {
$value = str_replace ( '"', '""', $value );
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= iconv ( "UTF-8", "GB18030//IGNORE", trim ( $line ) ) . "\n";
}
$data = str_replace ( "\r", "", $data );
$this->generate ( $headers, $data );
}
}
}
/**
* Create excel header and
* write data into file
*
* @param string $headers
* @param string $data
*/
private function generate($headers, $data) {
$this->set_headers ();
echo "$headers\n$data";
}
/**
* Set Excel file field header
*
* @param array $titles
* @return string
*/
public function titles($titles) {
if (is_array ( $titles )) {
$headers = array ();
if (is_null ( $this->custom_titles )) {
if (is_array ( $titles )) {
foreach ( $titles as $title ) {
$headers [] = iconv ( "UTF-8", "GB18030//IGNORE", $title );
}
} else {
foreach ( $titles as $title ) {
$headers [] = iconv ( "UTF-8", "GB18030//IGNORE", $title->name );
}
}
} else {
$keys = array ();
foreach ( $titles as $title ) {
$keys [] = iconv ( "UTF-8", "GB18030//IGNORE", $title->name );
}
foreach ( $keys as $key ) {
$headers [] = iconv ( "UTF-8", "GB18030//IGNORE", $this->custom_titles [array_search ( $key, $keys )] );
}
}
return implode ( "\t", $headers );
}
}
/**
* Set Response Header
*/
private function set_headers() {
$ua = $_SERVER ["HTTP_USER_AGENT"];
$filename = $this->filename . ".xls";
$encoded_filename = urlencode ( $filename );
$encoded_filename = str_replace ( "+", "%20", $encoded_filename );
header ( "Pragma: public" );
header ( "Expires: 0" );
header ( "Cache-Control: must-revalidate, post-check=0, pre-check=0" );
header ( "Content-Type: application/vnd.ms-excel; charset=UTF-8" );
header ( "Content-Type: application/force-download" );
header ( "Content-Type: application/octet-stream" );
header ( "Content-Type: application/download" );
if (preg_match ( "/MSIE/", $ua )) {
header ( 'Content-Disposition: attachment; filename="' . $encoded_filename . '"' );
} else if (preg_match ( "/Firefox/", $ua )) {
header ( 'Content-Disposition: attachment; filename*="utf8\'\'' . $filename . '"' );
} else {
header ( 'Content-Disposition: attachment; filename="' . $filename . '"' );
}
header ( "Content-Transfer-Encoding: binary" );
}
}
// Class End Here
Okay, here is HTML and PHP Code to demonstrate how it works.
HTML Code:
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>JSON to Excel POC</title>
<script type="text/javascript">
<!--
function excelExport() {
// this is your json data as string or you can
// use as object too
var jsonObject = '[{ "Comment" : "Good", "ExperinceMonths" : "4", "ExperinceYears" : "4", "Score" : "3", "Subject" : "CPP", "Topic" : "Scripting (mention details)" }, { "Comment" : "Excilent", "ExperinceMonths" : "6", "ExperinceYears" : "6", "Score" : "6", "Subject" : "CSharp", "Topic" : "WPF" }]';
// create a form
var form = document.createElement('FORM');
form.name = 'exportform';
form.id = 'exportform';
form.method = 'POST';
form.action = './export.php';
// create a hidden input inside form
var hiddenInput = document.createElement('INPUT');
hiddenInput.type = 'HIDDEN';
hiddenInput.name = 'exportdata';
hiddenInput.id = 'exportdata';
hiddenInput.value = jsonObject;
// insert hidden element inside form
form.appendChild(hiddenInput);
// insert form inside body
document.body.appendChild(form);
// submit the form
form.submit();
// remoce the form
document.body.removeChild(form);
return true;
}
//-->
</script>
</head>
<body>
<input type="button" value="Export" onClick='excelExport(); return false;'>
</body>
</html>
and finally Here is
PHP Code [export.php]
<?php
// add Class Export File
require_once 'Class.Export.php';
// make a new onject of Class Export
$export = new Export ();
// Send POSt data to make
// Excel File
if(isset($_POST['exportdata'])){
$export->saveAsExcel();
}
?>
Hope, this chunk of code will help people Because, Sharing is always Caring :) Cheers!!