Generate a csv file from a javascript array of objects

Question

I know this is a question already answered but couldn't find the solution that works for me.

I've a HTML button that, when clicked, generate an array of objects in typescript(which basically javascript) and i want to generate a csv file that will be downloaded.

Here is the example of the javascript array of objects:

    var items = [
              { "name": "Item 1", "color": "Green", "size": "X-Large" },
              { "name": "Item 2", "color": "Green", "size": "X-Large" },
              { "name": "Item 3", "color": "Green", "size": "X-Large" }];

    // Loop the array of objects
for(let row = 0; row < items.length; row++){
    let keysAmount = Object.keys(items[row]).length
    let keysCounter = 0

    // If this is the first row, generate the headings
    if(row === 0){

       // Loop each property of the object
       for(let key in items[row]){

                           // This is to not add a comma at the last cell
                           // The '\n' adds a new line
           csv += key + (keysCounter+1 < keysAmount ? ',' : '\r\n' )
           keysCounter++
       }
    }else{
       for(let key in items[row]){
           csv += items[row][key] + (keysCounter+1 < keysAmount ? ',' : '\r\n' )
           keysCounter++
       }
    }

    keysCounter = 0
}
console.log(csv)

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

I tried this example code but in my case it create the csv file, but doesn't insert the csv string correctly in the csv file, the csv string is has rows of element separated by a comma and each row is determined by a new line, but here all the data of each row sits in one cell of the csv file, how can I make so that each value of each row sits in a different cell?

here is the csv string that it:

    name,color,size
Item 2,Green,X-Large
Item 3,Green,X-Large

Coycoyle

this.ConvertToCSV(data) is a custom function that you made? Can you show us what it does? – Olimpiaolin

what is ConvertToCSV? what does its output look like? – Colunga

Also, If the problem is apparent in the console.log why not exclude the rest of the download code from the question? It seems irrelevant. – Tony

here is the function convertToCSV(data), i just added it, the function transform the array of object into a csv string – Coycoyle

I updated the code and i'm making progress, but still i don't yet the result i want – Coycoyle

Answer 1

CSV is just a file with each cell separated by a comma and each row separated by a new line. You want to name each column as the key of the object. If I understood correctly:

{ "name": "Item 1", "color": "Green", "size": "X-Large" }

Will be:

---------------------------
| name   | color |   size  |
---------------------------
| Item 1 | Green | X-Large |
---------------------------

UPDATE: This is the updated version.

So you can loop your array and generate the csv accordingly using the File API from HTML5:

let csv

// Loop the array of objects
for(let row = 0; row < items.length; row++){
    let keysAmount = Object.keys(items[row]).length
    let keysCounter = 0

    // If this is the first row, generate the headings
    if(row === 0){

       // Loop each property of the object
       for(let key in items[row]){

                           // This is to not add a comma at the last cell
                           // The '\r\n' adds a new line
           csv += key + (keysCounter+1 < keysAmount ? ',' : '\r\n' )
           keysCounter++
       }
    }else{
       for(let key in items[row]){
           csv += items[row][key] + (keysCounter+1 < keysAmount ? ',' : '\r\n' )
           keysCounter++
       }
    }

    keysCounter = 0
}

// Once we are done looping, download the .csv by creating a link
let link = document.createElement('a')
link.id = 'download-csv'
link.setAttribute('href', 'data:text/plain;charset=utf-8,' + encodeURIComponent(csv));
link.setAttribute('download', 'yourfiletextgoeshere.csv');
document.body.appendChild(link)
document.querySelector('#download-csv').click()

BTW If you are using TypeScript, then the last line document.querySelector('#download-csv').click() MUST be <HTMLElement>document.querySelector('#download-csv').click() Let me know if it worked for you.

Fiddle for TypeScript and javascript (it changes just the last line): https://jsfiddle.net/0p8revuh/4/

UPDATE: To see the file propertly formated in excel, a cell for each comma separated value, do the following:

1. You will see your data in several rows. Just click on the 'A' column to select all the rows:

2. Go to your excel and click on Data at the top:

3. Click on Text to columns:

4. Select delimited:

5. Click on next and active comma:

6. Click on next and finish. Now you should be able to see your data propertly formated.

Olimpiaolin

First thanks for your help, I tried the code and I get an error saying that "click doesn't exist on type element", the last line of code – Coycoyle

can you execute document.querySelector("#download-csv") on chrome dev tools and see if you get the link? – Olimpiaolin

yes it works and i get the link, that is what i get <a id="download-csv" download="MyReport.csv" href="#"></a> – Coycoyle

Then you should be able to click it with document.querySelector("#download-csv").click() to get the download. It worked for me. Also try typeof document.querySelector('#download-csv') – Olimpiaolin

It says it is an object – Coycoyle

What browser are you using? It's working for me on google chrome. Btw are you using TypeScript? – Olimpiaolin

i'm using Chrome too, and yeah i'm using typescript, is this the problem? because i thought that typescript is basically like javascript – Coycoyle

Typescript is "typesafe" so the querySelector returns an HTMLElement which doesn't have the click() method. So you gotta convert it to an HTMLInputElement. Simple execute: (<HTMLInputElement>document.querySelector('#download-csv')).click(). Let me know if it worked. – Olimpiaolin

It says it cannot find the name click, but on chrome dev tools, i get the same link and of type object – Coycoyle

Try to cast it to a HTMLScriptElement like: (<HTMLScriptElement>document.querySelector('#download-csv')).click() – Olimpiaolin

Do it without the external parenthesis because it may think it's a function like this: <HTMLScriptElement>document.querySelector('#download-csv')​.click() – Olimpiaolin

It still says the same error, and i also have another question, not related to this one: before the for loop, you let a variable called mycsv but inside the loop you're using csv, i guess this is just an error or typing, and also once the csv string is created, how does the browser know that it should create a file that will contain the data of that csv string – Coycoyle

You are correct that was a mistake by my part. Now I changed the variable name and set up the csv variable to download in the href with the new HTML5 syntax. – Olimpiaolin

So please how does it look like now? – Coycoyle

Ok finally got it working. You just need the <HTMLElement>. Check this 2 lines fiddle jsfiddle.net/k7uty4ss So basically do <HTMLElement>document.querySelector('#download-csv').click() – Olimpiaolin

Btw I've updated the answer html to loop the file. Check it and let me know if it worked for you. – Olimpiaolin

it says the property link doesn't exist on type element – Coycoyle

I have updated my code but still get the same error for the link which says that the property link doesn't exist on type element – Coycoyle

Check this fiddle jsfiddle.net/0p8revuh do you have the same code? – Olimpiaolin

I have exactly the same code, but doesn't work on my side – Coycoyle

In which line does the error appear? Can you try removing the last part that inserts the link and see if the error is coming from there? I've updated the code with the cast conversion in the last line for typescript. Check the 2 last lines from this fiddle: jsfiddle.net/0p8revuh/4 – Olimpiaolin

The error occurs only when i add .click() to the code – Coycoyle

Can you show me your entire javascript code in a jsfiddle? That error is weird because there are no link properties on that element – Olimpiaolin

var hiddenElement = document.createElement('a'); hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURI(csv); hiddenElement.target = '_blank'; hiddenElement.download = 'people.csv'; hiddenElement.click(); – Coycoyle

i tried this and i don't have any error, it creates and download the csv file , but just that each line of the csv string is contained in one cell in the csv file, it doesn't split the data of the line in different cells of that row – Coycoyle

So it generates the csv file? Are you using excel to open it? The problems is that excel doesn't format the csv file automatically. You have to make some changes to separate each element by a comma. Take a look at my updated answer. I repeat. The problem is not in the code, it's the excel that doesn't know how to format Comma Separated Values. Take a look at my updated answer. – Olimpiaolin

It worked, so it wasn't a problem of the code as i was thinking, instead the code was right, it was just a problem of excel – Coycoyle

Yeah... Well it worked tho. Mark this as the valid answer if I helped you. – Olimpiaolin

This was fine it is creating two documents . But what we have to do when we want array of objects ? How can i use keys in csv? – Pimple

Guys, I think the first row or array[0] will ever be included in the csv file. First row will extract the headers but not the values. So first row values missing? – Scruffy

This answer always misses one row, and the fix to get the data to present properly also does not work. – Eng

To Fix the missing first row, in the main for loop I started the row = -1 and then match that -1 to do the header row but you have to modify the Object.keys(items[0]).length to call the actual first item and in the header row loop each property of the first object, for(let key in items[0]). I also initialize the csv variable as an empty string to avoid an undefined in header on first pass. – Quitrent

I ran into the missing first row problem. Couldn't fix it, so I looked for another solution and found this https://dev.to/samueldjones/convert-an-array-of-objects-to-csv-string-in-javascript-337d – Westbrook

Answer 2

It works! But the way of code looks more complex and not easily understandable. I think the following code might be a little bit clear.

Also, I notified we need to keep the object key properly example if we want all in the capital we need to keep the object name in the capital but the property of the object key I'm not like to be capital. Most of the time It should be lowercase.In this case, also this solution might be helpful.

  let csvText = '';

    data.forEach((row, ind) => {
      if (!ind) {
        return (csvText += `${[
          'STOCK',
          'MAXPAIN',
          'CASH RATE',
          'FUTURE RATE',
          'PE',
          'PE OI',
          'CE',
          'CE OI',
          'PC RATIO MAX',
          'PC RATIO TOTAL'
        ].join(',')}\r\n`);
      }

      const properValues = [row.stock, row.option, row.strike, 0, row.put, row.put_oi, row.call, row.call_oi, 0, 0];

      return (csvText += `${properValues.join(',')}\r\n`);
    });

    console.log(csvText);

Ensure that heading value and not heading value return like the same order; otherwise, it'll be petrified.

Moneyer