Import Excel CSV file as JavaScript array

What I have:
A CSV file exported from Excel along with double-quotes
copyraw
label1,label2
item1a,item2a
item1c,"item2c,c"
item1b,item2b
  1.  label1,label2 
  2.  item1a,item2a 
  3.  item1c,"item2c,c" 
  4.  item1b,item2b 

What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects
copyraw
var my_object_array = [
     { my_col1_val: 'item1a', my_col2_val: 'item2a' },
     { my_col1_val: 'item1b', my_col2_val: 'item2b' },
     { my_col1_val: 'item1c', my_col2_val: 'item2c,c' }
];
  1.  var my_object_array = [ 
  2.       { my_col1_val: 'item1a', my_col2_val: 'item2a' }, 
  3.       { my_col1_val: 'item1b', my_col2_val: 'item2b' }, 
  4.       { my_col1_val: 'item1c', my_col2_val: 'item2c,c' } 
  5.  ]
What I want again:
  • Read a CSV file already uploaded with JavaScript
  • Populate a JS array with each row
  • Account for strings containing double-quotes (and commas to ignore)
  • Sort the resulting object array

How?
The function with comments:
- Uses deprecated XMLHttpRequest to get file contents
- Loops through each row and splits by commas (not within quotes)
- Sorts the final array by a object label
copyraw
function populate_array_from_csv(this_file){

        // using deprecated XMLHttpRequest to read file
        // (works at time of print (2015-10-05))
        var xhr = !window.XMLHttpRequest ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest();

        // open the file
        // use GET for faster performance but use POST (no size limits) for large files
        xhr.open('POST', this_file, false);  

        // set header to CSV mimetype
        xhr.setRequestHeader('Content-Type', 'text/csv');

        // send request to the server
        xhr.send();

        // return contents of target file to JS variable
        var my_csv_file_contents = xhr.responseText;

        // split contents into array of rows
        // store each line of the CSV file into a JS array
        var my_csv_rows_array = my_csv_file_contents.split("\n");

        // declare a blank array to store file contents
        var obj_temp = [];

        // loop through JS array using Array.prototype.forEach()
        my_csv_rows_array.forEach( function (row_content, row_index) {

                // clear and declare the array
                var column_values = [];         
                
                // regex to ignore commas between double-quotes
                var column_values = row_content.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/);

                // prevents JS error where results unmatched
                column_values = column_values || [];    

                // ignore row if first item is blank (allows for blank excel lines)
                if(column_values[0] != ''){

                        // remove any/all double-quotes in this column value  // found this method more reliable than using regex
                        removed_quotes = column_values[1].split('"').join('');

                        // populate my array with an object
                        obj_temp.push({ my_col1_val: column_values[0], my_col2_val: removed_quotes });
                }
        });

        // sorts the array by values in column 2 (string: my_col2_val)
        obj_temp.sort(function(a,b) {
                var x = a.my_col2_val.toLowerCase();
                var y = b.my_col2_val.toLowerCase();
                return x < y ? -1 : x > y ? 1 : 0;
        });

        // return the array of objects
        return obj_temp;
}

// usage:
//      where "this_file_url" is the target CSV to read (relative URL from the HTML file launching this function (not from the if external JS))
//      add parameters to this function to specify rows based on a criteria
var my_array = populate_array_from_csv(this_file_url);

// eg. var my_array = populate_array_from_csv('./mydata/data.csv');
// ---- End-ofFile
  1.  function populate_array_from_csv(this_file){ 
  2.   
  3.          // using deprecated XMLHttpRequest to read file 
  4.          // (works at time of print (2015-10-05)) 
  5.          var xhr = !window.XMLHttpRequest ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest()
  6.   
  7.          // open the file 
  8.          // use GET for faster performance but use POST (no size limits) for large files 
  9.          xhr.open('POST', this_file, false)
  10.   
  11.          // set header to CSV mimetype 
  12.          xhr.setRequestHeader('Content-Type', 'text/csv')
  13.   
  14.          // send request to the server 
  15.          xhr.send()
  16.   
  17.          // return contents of target file to JS variable 
  18.          var my_csv_file_contents = xhr.responseText; 
  19.   
  20.          // split contents into array of rows 
  21.          // store each line of the CSV file into a JS array 
  22.          var my_csv_rows_array = my_csv_file_contents.split("\n")
  23.   
  24.          // declare a blank array to store file contents 
  25.          var obj_temp = []
  26.   
  27.          // loop through JS array using Array.prototype.forEach() 
  28.          my_csv_rows_array.forEach( function (row_content, row_index) { 
  29.   
  30.                  // clear and declare the array 
  31.                  var column_values = []
  32.   
  33.                  // regex to ignore commas between double-quotes 
  34.                  var column_values = row_content.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/)
  35.   
  36.                  // prevents JS error where results unmatched 
  37.                  column_values = column_values || []
  38.   
  39.                  // ignore row if first item is blank (allows for blank excel lines) 
  40.                  if(column_values[0] != ''){ 
  41.   
  42.                          // remove any/all double-quotes in this column value  // found this method more reliable than using regex 
  43.                          removed_quotes = column_values[1].split('"').join('')
  44.   
  45.                          // populate my array with an object 
  46.                          obj_temp.push({ my_col1_val: column_values[0], my_col2_val: removed_quotes })
  47.                  } 
  48.          })
  49.   
  50.          // sorts the array by values in column 2 (string: my_col2_val) 
  51.          obj_temp.sort(function(a,b) { 
  52.                  var x = a.my_col2_val.toLowerCase()
  53.                  var y = b.my_col2_val.toLowerCase()
  54.                  return x < y ? -1 : x > y ? 1 : 0
  55.          })
  56.   
  57.          // return the array of objects 
  58.          return obj_temp; 
  59.  } 
  60.   
  61.  // usage: 
  62.  //      where "this_file_url" is the target CSV to read (relative URL from the HTML file launching this function (not from the if external JS)) 
  63.  //      add parameters to this function to specify rows based on a criteria 
  64.  var my_array = populate_array_from_csv(this_file_url)
  65.   
  66.  // eg. var my_array = populate_array_from_csv('./mydata/data.csv')
  67.  // ---- End-ofFile 

Additional Information(s):
Category: Excel :: Article: 635

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Please publish modules in offcanvas position.