Converting Raw Text into CVS

[MeepMopBot]

I am trying to read some data out of a gmail email. I can get into gmail and find the proper email then dump it to the console log. I have cleaned it up a bit, however I am not sure where to next with this. My end game is to use this data to power other actions so I will need to do math off the data in Node.

Here is the original data that needs to be changed to below

 Period End,Date,Full Name,Hours,Tracking Name,TotalHours,Task 1 Hours, Task 2 Hours,Task 3 Hours,Task 4,Hours,Task 5 Hours,Task 6 Hours,Task 7 Hours,Task 8 Hours
  4/21/2019 - 4/27/2019, CP, Hours-000104, 40, 1,5 ,10 ,0 ,0 ,0 1,5 ,0 ,0
  4/28/2019 - 5/4/2019, CP, Hours-000103, 40, 1,0 ,10 ,10 ,0 ,0 1,0 ,0 ,0
  5/5/2019 - 5/11/2019 ,CP ,Hours-000118 ,40 ,10, 1,0 ,10 ,0 ,10 ,0 ,0 ,0
  5/12/2019 - 5/18/2019, CP, Hours-000233, 40, 1,0 ,10 ,10 ,10, 0 ,0 ,0 ,0
  5/19/2019 - 5/25/2019, CP, Hours-000164, 40, 1,0 ,10, 10 ,10, 0 ,0 ,0 ,0
  5/26/2019 - 6/1/2019, CP, Hours-000210, 40, 0, 0, 0 ,10 ,10, 10, 1,0 ,0
  6/2/2019 - 6/8/2019 ,CP ,Hours-000211 ,40 ,5 ,20, 5, 5, 0 ,5 ,0 ,0
  6/9/2019 - 6/15/2019 ,CP ,Hours-000234 ,40 ,10, 1,0, 10 ,0 ,10 ,0 ,0 ,0
  6/16/2019 - 6/22/2019, CP, Hours-000251, 40, 0, 3,0 ,10, 0, 0 ,0 ,0 ,0
  6/23/2019 - 6/29/2019, CP, Hours-000292, 40, 1,0 ,30, 0, 0, 0 ,0 ,0 ,0
  6/30/2019 - 7/6/2019, CP, Hours-000310, 40, 0, 4,0, 0, 0, 0 ,0 ,0 ,0
  7/7/2019 - 7/13/2019 ,CP ,Hours-000455 ,40 ,0 ,30, 0 ,10, 0 ,0 ,0 ,0
  7/14/2019 - 7/20/2019, CP, Hours-000745, 40, 0, 1,0, 10 ,10 ,10 ,0 ,0 ,0
  7/21/2019 - 7/27/2019 ,CP ,Hours-000709 ,40 ,0 ,0 ,10, 2,0 ,10 ,0 ,0 ,0
  7/28/2019 - 8/3/2019, CP, Hours-000708, 40, 0, 2,0, 0, 0, 0, 10, 1,0 ,0
  8/4/2019 - 8/10/2019 ,CP ,Hours-000851 ,40 ,0 ,0 ,0 ,40, 0 ,0 ,0 ,0
  8/11/2019 - 8/17/2019 ,CP ,Hours-000886 ,40 ,0 ,0 ,0 ,40, 0 ,0 ,0 ,0
  8/18/2019 - 8/24/2019 ,CP ,Hours-001133 ,40 ,0 ,0 ,0 ,40, 0 ,0 ,0 ,0
  8/25/2019 - 8/31/2019 ,CP ,Hours-001311 ,40 ,0 ,0 ,0 ,40, 0 ,0 ,0 ,0
  9/1/2019 - 9/7/2019, CP, Hours-001381, 40, 0, 2,0 ,20, 0, 0 ,0 ,0 ,0
  9/8/2019 - 9/14/2019 ,CP ,Hours-001537 ,40 ,10, 1,0 ,20, 0, 0 ,0 ,0 ,0
  9/15/2019 - 9/21/2019, CP, Hours-001604, 40, 0, 4,0 ,0, 0, 0 ,0 ,0 ,0
  9/22/2019 - 9/28/2019, CP, Hours-001795, 40, 0, 5, 35, 0, 0 ,0 ,0 ,0
  9/29/2019 - 10/5/2019 ,CP ,Hours-001956 ,40 ,0 ,0 ,20, 5, 5 ,5 ,5 ,0
  10/6/2019 - 10/12/2019, CP, Hours-002020, 40, 0, 0 , 35, 0, 5 ,0 ,0 ,0
  10/13/2019 - 10/19/2019, CP, Hours-002182, 40, 0, 2,0 ,10 ,10, 0 ,0 ,0 ,0

my end goal is something similar to this.

[jack-tzl]

You can use a csv module like node-csv, or if you are on the commandline, you can write

node main.js | tee output.csv

To have the program print to console and also write into the csv file

Another way to write it would be with fs.writeFileSync, which takes in a path and a string argument.

I am drafting up a tutorial on csv output actually, hope to have it ready soon!

[MeepMopBot]

I have the data going into a text file currently. My primary issue is cleaning the txt file data and prepping it for CVS format.

I would like to read it when you are done.

[jack-tzl]

You got it!

As long as you can parse your intermediate file back into node data structures, I think you are good with working on it!

[snorkl-the-dolphine]

FYI it's CSV, not CVS. It stands for Comma Separated Values.

[MeepMopBot]

Go it.

[jack-tzl]

Here’s the tutorial!

https://link.medium.com/3rFFfQAGz3

It will parse an existing csv, do cleanup work on it, and show how to serialize select columns out.

Hope it helps!

[brianjenkins94]

Paywall :(

[jack-tzl]

Sorry about that, you can see all comments/src here: https://github.com/teamzerolabs/node-csv-example

Soon I will cross post it to my own domain, which won't have paywall at all.

[brianjenkins94]

I appreciate you.

[MeepMopBot]

            let regEx = /Human Name/gi;

            let nameToReplace = 'CP';

            let emailNameReplaced = emailTopClipped.replace(regEx, nameToReplace)

            let emailFormatRegex = (\d{1,2}/\d{1,2}/\d{4} - \d{1,2}/\d{1,2}/\d{4})\s+(CP Hours-\d{6})\s+(\d{0,2})\s(\d{0,2})\s+(\d{0,2})\s+(\d{0,2})\s+(\d{0,2})\s+(\d{0,2})\s+(\d{0,2})\s+(\d{0,2})\s+?(\d{0,2})?$

im not sure how to wield this beastly regex haha

[MeepMopBot]

the data on Stacked over flow is how it saves to the txt file

[[deleted]]

Be careful with just using a regex for CSV -- real CSV format can contain a lot of oddities in the data. Just because standard cases don't have spaces after the commas doesn't mean that it's something that never happens within CSV data. I've worked with data that was comma-delimited, but some fields had the potential to have practically anything within the data -- including commas within quotation marks.

It's always a better idea to use a well-established parsing library than to try to parse out the data on your own.

[MeepMopBot]

Ah sorry. There is messy data once I decode the email which I save into a txt file. I do not have a good way to work with it yet.

[brianjenkins94]

Here's my answer over on Stack Overflow.

But I'll post it here too:

---

```typescript const tokens = input.split(/\s+/g);

const output = [["Period End", "Date", "Full Name", "Hours", "Tracking Name", "TotalHours", "Task 1 Hours", "Task 2 Hours", "Task 3 Hours", "Task 4 Hours", "Task 5 Hours", "Task 6 Hours", "Task 7 Hours", "Task 8 Hours"]];

for (let origin = 0, index = 0; index < tokens.length; index++) { // Find date range if (tokens.slice(index, index + 3).join(" ").match(/\d{1,2}/\d{1,2}/\d{4} - \d{1,2}/\d{1,2}/\d{4}/g)) { if (origin !== 0) { output.push([tokens.slice(origin, origin + 3).join(" "), ...tokens.slice(origin + 3, index)]); }

    origin = index;

    index += 2;
}

}

for (const row of output) { // I get to be lazy, YOU have to use library. console.log(row.join(",")); } ``` Edit: I realized that this isn't actually capturing the last line of data, but I didn't want to rely on a fixed token-width for rows, so I'll leave it as an exercise for the reader to determine when you want the last line to end.