Looking up value from CSV and return result

Question

I'm attempting to use Javascript to lookup a value in a CSV file and return it's corresponding value (basically what a VLOOKUP does in Excel)

I've been able to follow some examples separately of pulling the CSV data into an array, and I've seen some examples of looking up data in arrays - but for the life of me I can't figure out how to get both working.

For example, the CSV file stations.csv has the following data:

mac,name
69167f276e9g,LINE1
69167f276e9f,LINE2

What I want to be able to do is lookup the 'mac' value from the CSV, and return the corresponding 'name' value.

So if I look for '69167f276e9f' I want to get the value LINE2 back.

[EDIT: Adding the code I've tried using MauriceNino's suggestion - but getting error Uncaught TypeError: Cannot read property '1' of undefined at the line 'return result[1];' ]:

$.ajax('stations.csv').done(function(data) {

  const lookup = (arr, mac) => {
    let twoDimArr = dataArr.map(el => el.split(',')); // map it to array of arrays of strings (data)
    let result = twoDimArr.filter(el => el[0] == mac)[0]; // Get the first element where the mac matches the first element in the array
    return result[1]; // Return the second element in the array
};

    let dataArr = data.split('\n');
    dataArr .shift(); // Remove the first array element (The header)

    let resultMac = lookup(dataArr, "69167f276e9f");

    console.log(resultMac);

})

Answer 1

Here is a possible solution:

• It first creates a two-dimensional array from your data
• Then it searches the first element that has the specified mac as the first value
• Then it returns the second element of that array

const data = `mac,name
69167f276e9g,LINE1
69167f276e9f,LINE2`;

const lookup = (arr, mac) => {
    let twoDimArr = dataArr.map(el => el.split(',')); // map it to array of arrays of strings (data)
    let result = twoDimArr.filter(el => el[0] == mac)[0]; // Get the first element where the mac matches the first element in the array
    return result[1]; // Return the second element in the array
}

let dataArr = data.split('\n');
dataArr .shift(); // Remove the first array element (The header)

let resultMac = lookup(dataArr, '69167f276e9f');

console.log(resultMac);

Answer 2

I just finished writing a script doing almost the exact same thing. What I did there is I streamed the csv file using the readline and fs libraries:

csvStream = readline.createInterface({
        input: fs.createReadStream(filePath);
});

Then, I manually parsed each line of the csv using the line event of the readline stream library:

decompressedStream.on('line', (line) =>

My csv has kind of a special structure, so I had to do manual parsing and some modifications to values, but basically the idea is splitting the string you get as 'line' using line.split and your delimeter, and then push each value to a cell in the array. what you get is an array that each cell in it represents one value in the csv line, and then you can push it to another big array which represents the whole csv and each cell in it represents a line.

after that, you can use the close event to know when the stream ended:

decompressedStream.on('close', () =>

and then its just a matter of using array.find the whole csv array to find the value you looking for:

let res = csvArray.find(val => val[0] == curMac)

and then

return res[1]

EDIT: i just saw MauriceNino's answer, which is very similar to mine. notice the differences:

1. in his answer he splits only using the delimeter, wheres in my case you have option to add more calculation or modifications to data before pushing it to the array for each line, and also account for special cases like when you have the delimeter inside a value surrounded by "". thats why I needed to use the stream method.

2. His method is perfect for small files, but loading extremely large files directly to memory can be bad. I was working on 15GB csv files, so I had to use the stream. This way I could load a few line, process them and dump my data, and then again load a bulk of lines.

As it seems, his logic is better suited for your case, but I do not know the whole flow or purpose of your program, so I will leave my answer here for you to decide which is better suited for you :)

Answer 3

First convert the CSV to an Array of objects.
Than use Array.prototype.find() to find in that Array the Object which property value matches a desired string.

const csv2array = (csv, dlm = ',') => {
  const [heads, ...lines] = csv.split('\n'), th = heads.split(dlm);
  return lines.map(l => l.split(dlm).reduce((o, c, i) => (o[th[i]] = c.trim(), o), {}));
}
const findInCSV = (csv, p, v) => csv2array(csv).find(k => k[p] === v);


// Getting stuff:

const myCSV = `mac,name
69167f276e9g,LINE1
69167f276e9f,LINE2`;

console.log(findInCSV(myCSV, 'mac', '69167f276e9g'))      // Get entire object by knowing the mac
console.log(findInCSV(myCSV, 'mac', '69167f276e9g').name) // Get name knowing the mac 
console.log(findInCSV(myCSV, 'name', 'LINE1'))            // Get entire object by knowing the name 
console.log(findInCSV(myCSV, 'name', 'LINE1').mac)        // Get mac knowing the name

Answer 4

Sounds like you need a dictionary, for example:

var myMappings = {
'69167f276e9f': 'LINE1',
'69167f276e9g': 'LINE2'
};

var myVal = myMappings['69167f276e9g'];

alert(myVal); // prints LINE2