javascriptarraysjoin

How to keep array values in the same column when converting to CSV


I'm trying to convert Object values to CSV but join method separates my array values into different columns in Excel. Any idea on how can I avoid this?

The function:


window.downloadCsv = function(records) {

    console.log(records);

    const array = [Object.keys(records)].concat(records);

    console.log(array);

    let result = array.map(it => {

        let objectValues = Object.values(it);
        for (let i = 0; i < objectValues.length; i++) {
            if (Array.isArray(objectValues[i])) {
                //Keep it as array
            }
        }

        return objectValues;
    }).join('\n');

    console.log(result);

    let hiddenElement = document.createElement('a');
    hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(result);
    hiddenElement.target = '_blank';
    hiddenElement.download = 'records.csv';
    hiddenElement.click();

};

Possible records input:

id: "5e8468e2db05ff589ca61b30"
title: "Example Application Number 1"
status: "Preparing Documents"
principalInvestigator: "Mr. Harry Styles"
coInvestigators: ["Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson"]
partners: null
funder: "EPSRC Standard research"
researchGroup: "MedEng"
scheme: "Travel Grant"
requestedAmount: null
estimatedAmount: 1234
submissionDate: "2020-03-23T00:00:00.000+01:00"
startDate: "2020-03-29T00:00:00.000+01:00"
estimatedDuration: null
endDate: null
facility: null
comments: null
dateCreated: "2020-04-01T12:11:46.783+02:00"
lastUpdated: "2020-04-01T12:11:46.783+02:00"
dateDeleted: null
__proto__: Object

Current output of result:

id,title,status,principalInvestigator,coInvestigators,partners,funder,researchGroup,scheme
5e8468e2db05ff589ca61b30,Example Application Number 1,Preparing Documents,Mr. Harry Styles,Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson

Desired output:

id,title,status,principalInvestigator,coInvestigators,partners,funder,researchGroup,scheme
5e8468e2db05ff589ca61b30,Example Application Number 1,Preparing Documents,Mr. Harry Styles,[Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson],Apple,Microsoft,XresearchGroup,YScheme

It is may easier to understand it in Excel format. Currently, it looks like this after exporting: https://i.sstatic.net/W3cMN.jpg

And the desired look would be: https://i.sstatic.net/JJG0j.jpg

So, pretty much I would like to keep array values in the same column rather than separating them into different ones which shifts all other columns as well in the CSV.


Solution

  • Here is a minimal reproducible example:

    The most important part is this:

    const headers = Object.keys(records).join(',')
    const values = Object.values(records).map(child => {
        if (child instanceof Array){
          //return child.join('; ');
          const str = JSON.stringify(child).replace(/"/g, "'");
          return `"${str}"`;
        }else{
          return child;
        }
        
    }).join(',')
    
    const result = [headers, values].join('\n')
    

    Where we take the keys and the value and put them each in an array and then put them in one array and join them with a new line [headers, values].join('\n')

    Inside the map you can do either this:

    const values = Object.values(records).map(child => {
        if (child instanceof Array){
          const str = JSON.stringify(child).replace(/"/g, "'");
          return `"${str}"`;
        }else{
          return child;
        }
        
    }).join(',')
    

    Which makes the array string show up in Excel like this:

    "['Niall Horan','Liam Payne','Zayn Malik','Louis Tomilson']"
    

    Or you can do the map like this:

    const values = Object.values(records).map(child => {
        if (child instanceof Array){
          return child.join('; ');
        }else{
          return child;
        }
        
    }).join(',')
    

    And then the output in Excel is like this (semicolon is not read as a column separator unless you use that locale - i.e. German locale):

    "Niall Horan; Liam Payne; Zayn Malik; Louis Tomilson"
    

    const recordObj = {
      id: "5e8468e2db05ff589ca61b30",
      title: "Example Application Number 1",
      status: "Preparing Documents",
      principalInvestigator: "Mr. Harry Styles",
      coInvestigators: ["Niall Horan", "Liam Payne", "Zayn Malik", "Louis Tomilson"],
      partners: null,
      funder: "EPSRC Standard research",
      researchGroup: "MedEng",
      scheme: "Travel Grant",
      requestedAmount: null,
      estimatedAmount: 1234,
      submissionDate: "2020-03-23T00:00:00.000+01:00",
      startDate: "2020-03-29T00:00:00.000+01:00",
      estimatedDuration: null,
      endDate: null,
      facility: null,
      comments: null,
      dateCreated: "2020-04-01T12:11:46.783+02:00",
      lastUpdated: "2020-04-01T12:11:46.783+02:00",
      dateDeleted: null
    }
    
    downloadCsv(recordObj)
    
    function downloadCsv(records) {
    
        //console.log(records);
        const headers = Object.keys(records).join(',')
        const values = Object.values(records).map(child => {
            if (child instanceof Array){
              //return child.join('; ');
              const str = JSON.stringify(child).replace(/"/g, "'");
              return `"${str}"`;
            }else{
              return child;
            }
            
        }).join(',')
        
        const result = [headers, values].join('\n')
        //console.log(headers);
        //console.log(values);
        console.log(result);
    
        let hiddenElement = document.createElement('a');
        hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(result);
        hiddenElement.target = '_blank';
        hiddenElement.download = 'records.csv';
        hiddenElement.click();
    
    }
    .as-console-wrapper { max-height: 100% !important; top: 0; }