Using PowerShell to extract lines from multiple files

Question

I have come to a situation that I need to analyze data from log/text files. There are hundreds of files, and some times tens of thousands of lines of data. I am using PowerShell only because its available on my computer and from what I can tell should be adequate. Just from using Google and finding topics here, I have managed to create a script that will parse multiple files for one specific label and extract that entire line of data.

The problem is I would like to extract multiple lines of different data from the file, mainly the actual data I am looking for and the date. It would also be nice if I could get the data extracted to an Excel file. Right now it's extracted to a text file and I just cut and paste to an Excel, then change the format from text to column with space delimited.

Here is a sample of the text I am looking at.

3I  "MAC" "DATE" := 2016, 8, 1
3I  "MAC" "TIME" := 3, 42, 56
15F  "MAC" "ORGB" := -1.656704e-04, -1.878277e-04, -1.873876e-04, -1.659016e-04, -1.429739e-04, -1.786126e-04, -1.590039e-04, -2.246118e-04, -1.951066e-04, -2.158172e-04, -1.526934e-04, -1.560605e-04, -1.856570e-04, -2.192611e-04, -1.747964e-04
15F  "MAC" "ORGP" := -1.657223e-04, -1.878391e-04, -1.874067e-04, -1.659254e-04, -1.429638e-04, -1.786519e-04, -1.590114e-04, -2.245719e-04, -1.950584e-04, -2.158372e-04, -1.526876e-04, -1.561122e-04, -1.855181e-04, -2.192713e-04, -1.748256e-04
15F  "MAC" "OFF3" := -7.424393e-08, -1.599836e-07, 1.178269e-07, 3.231106e-07, -4.113245e-07, -4.851174e-07, 4.043978e-07, 3.279856e-07, 6.228656e-07, 1.257285e-07, 1.290027e-07, -1.727165e-07, 7.661874e-07, 1.182343e-07, 1.484092e-06
15F  "MAC" "POST" := -1.897504e-06, 1.557098e-05, -1.367209e-05, -8.604270e-06, -1.810627e-06, 1.041628e-05, -6.231011e-06, 1.683000e-05, -1.059830e-05, 8.980048e-06, -1.064588e-06, -7.914769e-06, -1.680518e-05, 2.467031e-05, -7.863747e-06
15F  "MAC" "OFF4" := -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00
15F  "MAC" "PRID" := -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00, -1.000000e+00
15I  "MAC" "SOPC" := -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1

And here is the script I am using, which takes forever like about 30min, and ends up consuming all my RAM because its probably not the best way to do what I am trying to do.

$log = Get-Content "C:\Users\derekru\Documents\WS FTP Pro\MACBAC\MACSENS_*.txt;1"
foreach ($line in $log) { 
    if ($line -like "*OFF3*") {
        $line | Out-File -FilePath "D:\Work\MACSENS\Graph\STP09\160801-171022.txt" -Append
    }
}

Ultimately I would like to be able to extract the "Date", "Time", "ORGB", "ORGP", and "OFF3" from multiple files into an excel so it's formated

Date, Time, ORGB, the 15 data points, ORGP, the 15 points of data, OFF3, the 15 points of data...

on one Excel row.

Ansgar Wiechers · Accepted Answer · 2017-10-25 10:08:45Z

2

First and foremost: process your files individually, particularly if you have large files. Reading the content of all files into one variable prior to processing is bound to bog down your system.

Get-ChildItem 'C:\path\to\file_*.txt' | ForEach-Object {
    ...
} | Set-Content 'D:\path\to\output.txt'

For each file read the first 2 lines and extract date and time:

$d1 = Get-Content $_.FullName -TotalCount 2 |
      ForEach-Object { ($_ -split '\s*:=\s*')[1] }
$d2 = $d1 -join ', '

$pattern = 'yyyy, M, d, h, m, s'
$culture = [Globalization.CultureInfo]::InvariantCulture
$timestamp = [DateTime]::ParseExact($d2, $pattern, $culture)

The extract the data from the rest of the file:

$data = Get-Content $_.FullName |
        Select-Object -Skip 2 |
        Where-Object { $_ -match '"(.*?)"\s*:=\s*(.*)' } |
        ForEach-Object { '{0},{1}' -f $matches[1,2] }

and output like this:

$timestamp.ToString('yyyy-MM-dd,HH:mm:ss') + ',' + ($data -join ',')

Then import the text file into Excel.

answered Oct 25, 2017 at 10:08

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Derek Over a year ago

Thanks for the input. I will try to absorb this and see what I can make of it and get back to you. Even if anything else, processing the files individually will be a big help. Like I said in the fluff, I know nothing about programming so it may take a bit. One thing I would note, this example of the text repeats itself over and over thousands of times in the same file, just with different data after the specific tags. So finding the dates is not as simple as processing the first two lines.

colsw · Accepted Answer · 2017-10-25 09:59:51Z

0

You can do this faster (and more readably imo) using Select-String.

Select-String -Path $File -Pattern 'OFF3' -SimpleMatch | Out-File -FilePath $Path -Append

If runtime is really important you'll be better off using either underlying .net commands, a compiled language, a third party tool designed for this kind of thing, or a parallel/threaded approach.

If you want to combine multiple lines into one, and there's always a block of exactly 9 lines per block, you can use

Select-String '"DATE"' -Context 0,9

then loop through the resulting object and use the Context object to match out your information, although this can be slow and complex.

answered Oct 25, 2017 at 9:59

colsw

3,3661 gold badge18 silver badges29 bronze badges

5 Comments

Derek Over a year ago

The edit Ansgar made removed 'fluff', but I thought it was important to state I know nothing about coding. So no offense, but I have no idea what you are saying. I will have to try and see what these things mean and get back to you.

colsw Over a year ago

@Derek in all honesty if you're an absolute newbie to coding you should probably look at breaking this down into it's component steps and then working each of them out individually, or learning powershell from scratch.

Derek Over a year ago

That would honestly be a great idea. But its something that kind of just got dropped in my lap so I am just trying to make the best of it. We got an IT dept and guys that do coding, but trying to get them to help is like pulling teeth. So I gave up asking and decided to try and give it a shot.

Ansgar Wiechers Over a year ago

@Derek "I know nothing about coding" is not accepted as an excuse on SO. You're expected to have at least basic knowledge of your chosen language (meaning you have worked your way through a beginner's level tutorial).

Derek Over a year ago

@AnsgarWiechers That may be your expectation. I don't see this expectation actually stated anywhere on SO.

Collectives™ on Stack Overflow

Using PowerShell to extract lines from multiple files

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related