A CSV file exported from Excel along with double-quotes
copyraw
	
label1,label2 item1a,item2a item1c,"item2c,c" item1b,item2b
- label1,label2
 - item1a,item2a
 - item1c,"item2c,c"
 - item1b,item2b
 
What I want:
To read the file (stored on the server) and convert to a JavaScript array of objects
copyraw
	
What I want again: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' }
];
	- 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' }
 - ];
 
- 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
	- 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
 
Additional Information(s):
- StackOverflow: Split string by comma, but ignore commas inside quotes
 - WhatWG.org - XMLHttpRequest Standard
 
Category: Excel :: Article: 635
	

						  
                
						  
                
						  
                
						  
                
						  
                

Add comment