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

Question

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.

Nimantha · Accepted Answer · 2025-07-09 05:40:24Z

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; }

elvira.genkel · Accepted Answer · 2020-04-01 14:05:14Z

1

Try to convert an array to string and then replace commas with another separator, semicolon for example - this way csv interpretator won't split values in array and will count it as single item

let records = {id: "5e8468e2db05ff589ca61b30",
title: "Example Application Number 1",
status: "Preparing Documents",
principalInvestigator: "Mr. Harry Styles",
coInvestigators: ["Niall Horan, Liam Payne, Zayn Malik, Louis Tomilson"]}


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

   let result = array.map(it => {

        let objectValues = Object.values(it);
        for (let i = 0; i < objectValues.length; i++) {
            if (Array.isArray(objectValues[i])) {
               objectValues[i]= objectValues[i]=(`[${objectValues[i].join(';')}]`).replace(/,/g, ';')
            }
        }

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

    console.log(result);

answered Apr 1, 2020 at 14:05

elvira.genkel

1,3331 gold badge6 silver badges12 bronze badges

1 Comment

Dzsonah Over a year ago

Also, a really nice solution, thank you! So the problem was with Excel interpreting "," as a single item indicator, therefore separated everything into different columns

Aioros · Accepted Answer · 2020-04-01 14:28:47Z

0

I would personally suggest changing your solution a bit. You don't need to concatenate keys and values, it makes the .map() unnecessarily complicated. Here's what I would do:

var keys = Object.keys(records);
// we take the array of values, but we transform any array
// in a string of values separated by +
var values = Object.values(records).map(record => {
    if (Array.isArray(record))
        return record.join("+");
    return record;
});

// Now we can just put everything together
var result = keys.join(",") + "\n" + values.join(",");

console.log(result);

edited Apr 1, 2020 at 14:28

answered Apr 1, 2020 at 14:10

Aioros

4,3931 gold badge21 silver badges22 bronze badges

3 Comments

Dzsonah Over a year ago

Hi, this works really well, however it still puts array items in separated columns in CSV.

Aioros Over a year ago

I'm not sure what you mean. The names of the coInvestigators will be separated by a + instead of a comma, so they are effectively in the same column by any CSV standard. Excel should keep them together with their + signs, and if it doesn't, you should be able to tell him that the data is comma-separated.

Dzsonah Over a year ago

Hm, for some reason it is not, even though this also looks like a good solution, it seems like it needs the replace function as in the answers above to replace the "," separator with anything else. Thank you for your answer though!

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related