How to Approach Parsing CSV in Javascript

Question

(My first post, I apologise for any mistakes)

I'm working with a small set of data in CSV files, which I need to read, process, and then export as a text file.

The format of the CSV data is:

REGO,STATUS,SHIFT,LOCATION,LOADED
CCA4110,StatusON,5:43,Brisbane,1
CCA4112,StatusON,5:44,Syndey,0
CCA4118,StatusON,6:11,Melbourne,1

I want to be able to take each line after the header row, and check

a) if the 'LOADED' value equals 0 or 1 (skip to next row if 1).

b) If 'LOADED' is 0, then check if the 'REGO' value matches a pre-defined list of 'REGO' values.

c) If a match, change the 'SHIFT' time.

d) If no match, move on to next row.

After that, I want to export all of the rows, with only the 'REGO' and 'SHIFT' values, to look like:

CCA4110,5:43
CCA4112,5:33
...

Because this feels a little complex to me, I'm having trouble visualising the best way to approach this problem. I was wondering if someone could help me think about this in a way that isn't just hacking together a dozen nested for loops.

Thanks very much,

Liam

Edit: a question about checking multiple conditions:

Say I have two CSV files:

List_to_Change.csv

REGO,STATUS,SHIFT,LOCATION,LOADED       
CCA2420,StatusOn,11:24,BRISBANE,1
CCA2744,StatusOn,4:00,SYDNEY,1
CCA2009,StatusOn,4:00,MELBOURNE,0

List_to_Compare.csv

REGO,CORRECT_SHIFT
CCA2420,6:00
CCA2660,6:00
CCA2009,5:30

An algorithm:

1. Check value in 'List_to_Check.csv' 'LOADED' column

A. If value equals '0' go to step 2.

B. If value equals '1' skip this row and go to next.

2. Check if 'REGO' value in 'List_to_Check.csv' shows up in 'List_to_Compare.csv'

A. If true go to step 3.

B. If false skip this row and go to next.

3. Change 'SHIFT' value in 'List_to_Change.csv' with value shown in 'List_to_Compare.csv'

4. Stringify each row that was changed and export to text file.

This may help you to get some hint: https://stackoverflow.com/questions/7431268/how-to-read-data-from-csv-file-using-javascript . In any case, it's just about splitting a string, did you try anything or are you looking for a theorical approach? — briosheje, Oct 15 '18 at 11:26
@briosheje Thanks very much for the resource, I'll check it out now. I have had a few attempts myself, splitting the input based on the carriage return, as well as trying (and failing) to understand how to use Papa Parse to handle the file. I guess I'm more wondering if there's a more intelligent way to look at the problem. Thanks once again — Liam Johnson, Oct 15 '18 at 11:35
Please share above the attempt (so, the code) you're talking about, it would be a good starting point and a pleasure to help you starting from **your** input ;) — briosheje, Oct 15 '18 at 11:42

user3297291 · Answer 1 · 2018-10-15T12:08:14.260

My advice would be to split the work flow in to three steps:

Parse all rows to javascript objects
Perform the logic on the array of objects
Stringify the objects back to CSV

// This creates an object based on an order of columns:
const Entry = ([rego, status, shift, location, loaded]) =>
  ({ rego, status, shift, location, loaded });
  
// Which entries are we interested in?
const shouldHandleEntry = ({ loaded }) => loaded === "1";

// How do we update those entries?
const updateEntry = entry => ({
  ...entry,
  shift: ["CCA4118"].includes(entry.rego)
    ? "5:33"
    : entry.shift
});

// What do we export?
const exportEntry = ({ rego, shift }) => `${rego},${shift}`;

// Chain the steps to create a new table:
console.log(
  csvBody(getCSV())
    .map(Entry)
    .filter(shouldHandleEntry)
    .map(updateEntry)
    .map(exportEntry)
    .join("\n")
)


// (This needs work if you're using it for production code)
function csvBody(csvString) {
  return csvString
    .split("\n")
    .map(line => line.trim().split(","))
    .slice(1);
};

function getCSV() { return `REGO,STATUS,SHIFT,LOCATION,LOADED
CCA4110,StatusON,5:43,Brisbane,1
CCA4112,StatusON,5:44,Sydney,0
CCA4118,StatusON,6:11,Melbourne,1`; }

This is incredible, and incredibly close to what I was trying to get across. Thank you very much for your time with that. I can learn a lot already from reading what you wrote. That makes a lot of sense if I'm looking to check for one condition, e.g. "Location = 1." Say I wanted to check for multiple conditions, what would be the best way to approach that? I've edited the main post with an example for clarity's sake. Would you mind taking a look? — Liam Johnson, Oct 15 '18 at 22:23
This asks for a replacement of the `updateEntry` step. Instead of a static function, we'll need to create an update function that is based on the changes in the other file. A pointer: Create an object of `REGO`: `CORRECT_SHIFT`, for every entry you need to check, check if there's a corrected value in this object and update accordingly. — user3297291, Oct 18 '18 at 08:16

How to Approach Parsing CSV in Javascript

1 Answers1

Linked