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

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

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.