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