How to iterate over a javascript line delimited string, check the semi-colon delimited first value, and join lines where value is equal?

Question

I have a CSV that I'm using as a data source. I need to split the lines on the newline character ('\n') into a variable called 'lines', and I need to split the semi-colon (';') delimited values in each line into a variable called 'items'.

I want to be able to check the first value on a line and, if it equals the input value in an input box, I want to add this line, with the semi-colon delimiter between the values, to a new variable called newData. If it's not equal to the value in the input box, I want to exclude it.

I basically want to filter out each line where the first value doesn't equal the value I'm searching for.

Here's a sample of the data (assume a newline ('\n') character after each line):

"ID";"Date";"Time";"Status"
"1";"2013.01.01";"10:03 AM";"Active"
"1";"2013.01.05";"07:45 AM";"Active"
"2";"2013.01.03";"9:12 PM";"Active"
"2";"2013.01.11";"6:37 AM";"Inactive"
"1";"2013.01.22";"12:57 PM";"Inactive"

I can split off the first header line and that won't be included in the results I'm seeking. If my search is on the ID column and the search value is 1, I want to only return the lines where the ID equals 1 and want it to match the format above (sans header line). Conversely, if the search value is 7, I would only return either no lines or just the header line.

All I've been able to do so far is split the lines from the data I'm importing (all of this code I found, I'm really not that familiar with javascript or jQuery):

$.get("myFile.csv", function(data) {
    data = data.replace(/"/g, "");

    var lines = data.split('\n');

    var first = lines.shift().split(';');
});

// HTML Input Box
<input id="search_box" type="text">
<button id="btn_search">Search</button>

Thanks.

Solution 1

here's some code that does that, assuming you know how to get the data to be like it is in the lines variable (which you said you did). You can search by any field and any value.

jsFiddle of this code

$(document).ready(function() {

    var headers = '"ID";"Date";"Time";"Status"';
    var lines = [
    '"1";"2013.01.01";"10:03 AM";"Active"',
    '"1";"2013.01.05";"07:45 AM";"Active"',
    '"2";"2013.01.03";"9:12 PM";"Active"',
    '"2";"2013.01.11";"6:37 AM";"Inactive"',
    '"1";"2013.01.22";"12:57 PM";"Inactive"'
        ];

    // these contain the name of the field you want to search in
    //  as defined by your header and the value to search for.
    var searchField = 'ID';
    var searchForThisValue = 1; // this would be a parameter in your code
    var endResult =[]; // this is the stored result
    var fieldIndex = -1;

    $.each(headers.split(';'), function(index) {
        if( this.replace(/["']/g, "") == searchField ) {
            fieldIndex = index;
        }
    });

    $.each(lines, function() {
        var fields = this.split(';'); // split up by semicolon

        // strip the quotes around around the numbers
        if(fields[fieldIndex].replace(/["']/g, "") == searchForThisValue) {
            endResult.push(this);
        } 
    });

    // do whatever you want with your result, here I have just thrown 
    // them into an element with the id of 'blah'
    $.each(endResult, function() {
        $('#blah').append(this + '<br/>');
    });
});

Solution 2

Here's a simple solution using pure JavaScript:

// Assuming csvString is the string given in the question
var inputArray = csvString.split('\n');
var filteredArray = inputArray.filter(function (rowString) {
  return (rowString.split(";")[0] === '"1"');
});
var filteredString = filteredArray.join('\n');

At the end of this, filteredString will be:

"1";"2013.01.01";"10:03 AM";"Active"
"1";"2013.01.05";"07:45 AM";"Active"
"1";"2013.01.22";"12:57 PM";"Inactive"

For more info on how the methods used in this solution work, see:

• Array filter method.
• Array join method
• String split method

Solution 3

This might seem a bit overblown, but I think the best thing for this kind of problem is to first turn your esoteric data format (CSV) into something Javascript can understand holistically (JSON-type data — arrays and objects). I couldn't make use of AJAX (but you've already got that part sorted), so I knocked up an example where I extract the CSV data from an element in the HTML.

Working demo

jsFiddle example.

Background code

function findEntriesWithID(ID){
  var entries = [];

  for(var i = 0; i < csvArray.length; ++i){
    var row = csvArray[i];

    if(row.ID === ID){
      entries.push(row);
    }
  }

  return entries;
}

function csvToArray(csvString){
  // The array we're going to build
  var csvArray   = [];
  // Break it into rows to start
  var csvRows    = csvString.split(/\n/);
  // Take off the first line to get the headers, then split that into an array
  var csvHeaders = csvRows.shift().split(';');

  // Loop through remaining rows
  for(var rowIndex = 0; rowIndex < csvRows.length; ++rowIndex){
    var rowArray  = csvRows[rowIndex].split(';');

    // Create a new row object to store our data.
    var rowObject = csvArray[rowIndex] = {};

    // Then iterate through the remaining properties and use the headers as keys
    for(var propIndex = 0; propIndex < rowArray.length; ++propIndex){
      // Grab the value from the row array we're looping through...
      var propValue =   rowArray[propIndex].replace(/^"|"$/g,'');
      // ...also grab the relevant header (the RegExp in both of these removes quotes)
      var propLabel = csvHeaders[propIndex].replace(/^"|"$/g,'');;

      rowObject[propLabel] = propValue;
    }
  }

  return csvArray;
}

Explanation

1. csvToArray takes CSV as a string and outputs an array of objects where the first line (headers) is used to determine the properties of each subsequent row. This is the meat of the code and can be applied in a wide variety of situations to get the data in a manageable form. Elaborate maybe, but fairly solid for your use case. The rest of the code is then easier to understand:

2. findEntriesWithID takes a given ID and returns all the objects (formerly rows) with that ID property, and returns them as a new array.

3. The jQuery function at the top binds the user interaction, then spits out the results in a pane as stringified JSON. It might seem stupid to go to these lengths to convert CSV string to an esoteric object only to convert it back into a string with different syntax, but the advantage is you can swap this part of the function out for whatever it is you really want to do with your results.

Comments

[ruakh]

@user2063351: I suggest you learn JavaScript, then. A good starting-point for this task is to look through the MDN documentation for String.

[user2063351]

Should the last line in your code say, "= filteredArray.join('\n');' instead of '= filtered.join('\n');'? I didn't see 'filtered' as a keyword in the 'Array filter method' link you provided, so I'm guessing you're referring to the variable you created earlier.

[user2063351]

I can't mark this as useful because I don't have enough reputation, but I think your answer will be very helpful once I have the time to implement it. I chose another answer because it resolves my issue closer to the code I've already written. Thank you for providing such a helpful response though.