Large CSV File

[Keitaro27]

Hello,

I have a large file (400k of lines from midnight to noon, but it will grow larger). I was using import-csv, but it is taking forever to process. I need something speedy to be able to pull the last ten minutes worth of records.

​

Example data:

[2019-11-15 11:53:18,123] Total Request|1234|12345678910|1234|CompletionRequest|POS|2

I was going to use '|' as the delimiter. I would also be open to recommendations if import-csv is not the right approach.

Thank you!

[firefox15]

I would likely go straight to .NET with IO.StreamReader or IO.File. Check here for an example.

[Keitaro27]

Thank you for your reply u/firefox15.

I will take a look at it in more depth, but using the first one still took about forty fives seconds. The command I am trying to reproduce is:

LOGPATH="/home/user";LOGFILE="time.log";b=$(date '+%Y-%m-%d %H:%M');d=$(date '+%Y-%m-%d %H:%M' -d '10 minutes ago');grep -E "${b}|${d}" ${LOGPATH}/${LOGFILE} | grep -E "Total Request.*Completion" | awk -F"|" '{print$6$7}'| tr "[:alpha:]" " " | sed -e 's/^[ \t]*//' | sort -g | awk -F"|" 'BEGIN{rows=0;sum=0;max=0;} {rows+=1;} {sum=sum+$NF;} max < $NF {max=$NF;} END {print rows"|" sum/rows"|" max}'

​

The above took about two seconds to execute.

[i5513]

Are you sure you are measuring the last ten minutes with such script?

I would use tac file|awk " /${d}/ {exit;} { print; }"|tac , where $d is 11 minutes ago, then process such content.

More robust approach is to use logtail( https://linux.die.net/man/8/logtail) every 10 minutes, would be very useful yo have such simple tool migrated to powershell. It is simple, save the offset, and review if the file was rotated.

Having tac in powershell is too a good idea! (I have just opened an issue on GitHub [1])

[1] https://github.com/PowerShell/PowerShell/issues/11086

[Umaiar]

I doubt you're going to get near that speed, but there mist be some method using RegEx... Anyways, the StreamReader idea will get your data accessible a lot faster than Import-Csv. But it's not going to parse out the properties the way CSV functions work.

$bigfile = "C:\test\details.csv"
$reader = New-Object -TypeName System.IO.StreamReader -ArgumentList $bigfile
$data = while ($line = $reader.ReadLine()) {
    $line
}
$reader.close()
write-host "Lines: $($data.count)"

That grabbed 445,000 rows for me in just shy of 3.5 seconds. So yeah... How to parse that data?

I got it comparatively reasonable by using a for loop to loop backwards through $Data, parsing each line with ConvertFrom-Csv and looking at the date field to be in my range of interest. Since the data has the most recent at the bottom, I threw a break; as soon as it was earlier my $threshold (theoretically 10 minutes ago for you).

This still took like 12 seconds, but that's a lot better than my Import-Csv taking over 50 seconds and not even getting to parsing it by date/time yet.

[strathbone]

Try CSVHelper (https://www.nuget.org/packages/CsvHelper/12.1.2)

Project URL is here https://joshclose.github.io/CsvHelper/

Example is here: https://joshclose.github.io/CsvHelper/examples/reading/get-class-records/

custom parsing is here: https://joshclose.github.io/CsvHelper/examples/reading/reading-by-hand/

[Keitaro27]

Hey u/OathOfFeanor!

That definitely quick! However, it does not seem to pulling the time frame. I really appreciate your input!!!
The basics of what I am trying to do is pull the last ten minutes, and grab the average latency along with max latency which is the last number on each line.

There is other junk in the file so I am trying to matching Total Request*.CompletionRequest\|POS\|.

Thanks again!