11

How can i convert the data inside an CSV file to a 2d array?

array[row][column]

I already have an upload script that will display all the data inside a CSV file.

Little snippet of my code, here's the full code: http://jsfiddle.net/Meesz/wj6q7c30/

reader.onload = function (e) {
  var table = document.createElement("table");
  var rows = e.target.result.split("\n");
  for (var i = 0; i < rows.length; i++) {
    var row = table.insertRow(-1);
    var cells = rows[i].split(",");
    for (var j = 0; j < cells.length; j++) {
        var cell = row.insertCell(-1);
        cell.innerHTML = cells[j];
    }
  }
  var dvCSV = document.getElementById("dvCSV");
  dvCSV.innerHTML = "";
  dvCSV.appendChild(table);
}

4 Answers 4

15

Real answer: Use Papa Parse . Save yourself the hassle of escaped/quoted fields, fields with delimiters in them, variations in the CSV format, etc...

The "do it yourself" way: csvStr.split("\n").map(function(row){return row.split(",");})

Sign up to request clarification or add additional context in comments.

3 Comments

The DIY way is a bad idea. Use a real parser.
Agreed. You can only lead a horse to water though
You should use return in the map function.
8

Use String.split() and Array.map()

Because "the map() method creates a new array with the results of calling a provided function on every element in this array" (source: MDN), it is ideally suited somewhat suitable for creating a two dimensional array from a very basic CSV string that has already been converted into an array via the split() method.

function csvToArray (csv) {
    rows = csv.split("\n");

    return rows.map(function (row) {
    	return row.split(",");
    });
};

// Hard-coded for brevity, but you can set this variable with FileReader
var csv = "the,quick,brown,fox\n" +
          "jumps,over,the,lazy,dog";

var array = csvToArray(csv);

console.log(array);
Open your console.

Update:

This basic solution falls apart if your cells contain quotes, commas or other escaped characters. To address more complex CSV strings, you'd have to implement a RegEx solution (see accepted answer to How can I parse a CSV string with Javascript?); and to support multiple common formats, you'd be better off just using a library.

2 Comments

What if the row is "Hello, how are you","Smith,John",34?
Touché. You'd have to implement a RegEx test within the map() callback. And then it can get hairier from there.
3

I needed something like this today so I found snippet originally done by by Bennadel and cleaned it up a bit also adding in edge cases.

/*
 *  shortened version of:
 *   http://www.bennadel.com/blog/1504-ask-ben-parsing-csv-strings-with-javascript-exec-regular-expression-command.htm
 *
 *  Supports columns that are quoted/non-quoted. Takes care of quoted columns that may also have \n's inside
 *  them instead of using a naive approach of split('\n')
 *
 *  @param csvString {string} CSV file of rows separated by new lines.  
 *  @param delimiter {string} delimiter used to split columns 
 *
 *  return {rows} Array of rows with columns containing parsed CSV data
 */

function CSVParse(csvString, delimiter = ",") {

  if (!csvString || !csvString.length)
    return [];

  const pattern = new RegExp(
   ( "(\\" + delimiter + "|\\r?\\n|\\r|^)" +
     "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
     "([^\"\\" + delimiter + "\\r\\n]*))"
   ), "gi"
  );

  let rows = [[]]; 
  let matches = false;

  while (matches = pattern.exec(csvString)) {

    const matched_delimiter = matches[1];
    const matched_cellQuote = matches[2];
    const matched_cellNoQuote = matches[3];

    /*
     * Edge case: Data that starts with a delimiter
     */
    if (matches.index == 0 && matched_delimiter)
      rows[rows.length - 1].push("");

    /*
     * Fix empty lines
     */
    if(!matches[2] && !matches[3])
      continue;

    if (matched_delimiter.length && matched_delimiter !== delimiter)
      rows.push([]);

    const matched_value = (matched_cellQuote)
      ? matched_cellQuote.replace(
          new RegExp("\"\"", "g"), "\""
        )
      : matched_cellNoQuote;

    rows[rows.length - 1].push(matched_value);

   }

   return rows;
}

3 Comments

fantastic. any drawbacks?
I think the only possible drawback is the accuracy of the regex. I needed a vanilla JS CSV parser to use in Google AppScript and I really didn't want to use a third party parser if possible. This is doing the job perfectly so far during testing: if I find any exceptions I'll just tweak the regex to cover anything that comes up. Looks solid so far though.
Just found an error that it won't parse ,, (double comma) as a blank cell, the cell is removed altogther.
2

The logic is similar to (but simpler than) what you're already doing to display the results:

var data = [];
var rows = e.target.result.split("\n");

for (var i = 0; i < rows.length; i++) {
  var cells = rows[i].split(",");
  data.push( cells );
}

var data = [];
var csv = "one,two,three\n" +
          "four,five,six";

var rows = csv.split("\n");

for (var i = 0; i < rows.length; i++) {
  var cells = rows[i].split(",");
  data.push( cells );
}

console.dir(data);

5 Comments

But how can i select one row and a column like here in JSP: stackoverflow.com/questions/33034833/…
Just like you'd think: data[1][2] would be the third column in the second row.
What if the row is "Hello, how are you","Smith,John",34?
Then see Fabio's answer, and get a real CSV parser.
Then declare data outside the function, populate it in the function, and use it wherever you like.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.