GeoBaez
Reputation: 31
Extracting a table from a text file using PowerShell
I have a table that I want to extract from a batch of text file. The problem is that the table does not begin at the same line in the every text file. Also, the presentation, format, and reuse of keywords makes it really difficult to write a regex expression (for me at least). I've figured out how extract information from specific lines but this table is just a no go for me. I've researched regex expressions and splits but have come up empty.
The top of the file looks like this:
Summary Call Volume Statistics:
Total Calls = 1000
Total Hours = 486.7
Average Call Frequency = 2.05
Summary Reliability Statistics:
Total Queued Calls = 152
Total Calls = 1000
Total On Time Calls = 710
Total Reliability = 0.7100
Total Raw Demand = 640.00
Total Covered Demand = 437.79
Summary Business Statistics:
Total Servers = 4
Total Sim Time (secs) = 1752079
Total Server Time (secs) = 7008316
Total Server Busy Time (secs) = 0
Total Business = 0.0000
Detail Node Sim Reliability:
Node Calls On Time Percent Queued UnderTm OverTm
-------- -------- -------- -------- -------- -------- --------
0 97 81 0.8351 17 1637404 0
1 115 92 0.8000 25 1637404 0
2 103 90 0.8738 16 1637404 0
3 68 53 0.7794 17 1637404 0
4 63 57 0.9048 6 1637404 0
5 35 29 0.8286 7 1637404 0
6 31 27 0.8710 4 1637404 0
7 40 36 0.9000 6 1637404 0
8 22 17 0.7727 5 1637404 0
9 26 24 0.9231 1 1637404 0
10 24 21 0.8750 3 1637404 0
11 23 0 0.0000 5 1637404 0
12 23 20 0.8696 2 1637404 0
13 15 0 0.0000 2 1637404 0
14 20 19 0.9500 1 1637404 0
15 19 0 0.0000 1 1637404 0
16 23 18 0.7826 4 1637404 0
17 12 9 0.7500 4 1637404 0
18 10 10 1.0000 0 1637404 0
19 11 0 0.0000 1 1637404 0
20 13 0 0.0000 2 1637404 0
21 9 7 0.7778 1 1637404 0
22 11 9 0.8182 1 1637404 0
23 11 0 0.0000 2 1637404 0
24 14 6 0.4286 3 1637404 0
25 6 6 1.0000 0 1637404 0
26 6 0 0.0000 0 1637404 0
27 4 0 0.0000 1 1637404 0
28 5 5 1.0000 0 1637404 0
29 12 10 0.8333 1 1637404 0
30 12 11 0.9167 1 1637404 0
31 4 2 0.5000 2 1637404 0
32 8 8 1.0000 0 1637404 0
33 4 4 1.0000 0 1637404 0
34 6 0 0.0000 0 1637404 0
35 11 10 0.9091 1 1637404 0
36 7 0 0.0000 1 1637404 0
37 5 0 0.0000 2 1637404 0
38 5 0 0.0000 0 1637404 0
39 8 0 0.0000 2 1637404 0
40 6 6 1.0000 0 1637404 0
41 9 7 0.7778 2 1637404 0
42 4 1 0.2500 1 1637404 0
43 8 5 0.6250 1 1637404 0
44 1 1 1.0000 0 1637404 0
45 2 0 0.0000 0 1637404 0
46 5 4 0.8000 0 1637404 0
47 6 5 0.8333 0 1637404 0
48 3 0 0.0000 0 1637404 0
49 3 0 0.0000 0 1637404 0
50 2 0 0.0000 0 1637404 0
51 3 0 0.0000 1 1637404 0
52 2 0 0.0000 0 1637404 0
53 3 0 0.0000 0 1637404 0
54 2 0 0.0000 0 1637404 0
-------- -------- -------- -------- -------- -------- --------
Total: 1000 710 0.7100 152 1637404 0
Later in the file there is this table:
Comparable Node Alpha Reliability:
Node Raw Dem Sim Rely Wtd Cov
-------- -------- -------- --------
0 71.0000 0.8351 59.2887
1 62.0000 0.8000 49.6000
2 56.0000 0.8738 48.9320
3 39.0000 0.7794 30.3971
4 35.0000 0.9048 31.6667
5 21.0000 0.8286 17.4000
6 20.0000 0.8710 17.4194
7 19.0000 0.9000 17.1000
8 17.0000 0.7727 13.1364
9 17.0000 0.9231 15.6923
10 16.0000 0.8750 14.0000
11 15.0000 0.0000 0.0000
12 14.0000 0.8696 12.1739
13 12.0000 0.0000 0.0000
14 12.0000 0.9500 11.4000
15 11.0000 0.0000 0.0000
16 10.0000 0.7826 7.8261
17 10.0000 0.7500 7.5000
18 9.0000 1.0000 9.0000
19 9.0000 0.0000 0.0000
20 9.0000 0.0000 0.0000
21 8.0000 0.7778 6.2222
22 8.0000 0.8182 6.5455
23 8.0000 0.0000 0.0000
24 8.0000 0.4286 3.4286
25 7.0000 1.0000 7.0000
26 6.0000 0.0000 0.0000
27 6.0000 0.0000 0.0000
28 6.0000 1.0000 6.0000
29 6.0000 0.8333 5.0000
30 6.0000 0.9167 5.5000
31 5.0000 0.5000 2.5000
32 5.0000 1.0000 5.0000
33 5.0000 1.0000 5.0000
34 5.0000 0.0000 0.0000
35 5.0000 0.9091 4.5455
36 5.0000 0.0000 0.0000
37 4.0000 0.0000 0.0000
38 4.0000 0.0000 0.0000
39 4.0000 0.0000 0.0000
40 4.0000 1.0000 4.0000
41 4.0000 0.7778 3.1111
42 4.0000 0.2500 1.0000
43 4.0000 0.6250 2.5000
44 3.0000 1.0000 3.0000
45 3.0000 0.0000 0.0000
46 3.0000 0.8000 2.4000
47 3.0000 0.8333 2.5000
48 3.0000 0.0000 0.0000
49 3.0000 0.0000 0.0000
50 3.0000 0.0000 0.0000
51 2.0000 0.0000 0.0000
52 2.0000 0.0000 0.0000
53 2.0000 0.0000 0.0000
54 2.0000 0.0000 0.0000
-------- -------- -------- --------
Total: 437.7852
I need to be able to store the two middle columns as an array in order to do some calculations.
How do I go about doing this in powershell? I already have the following code that works (with generic name changes):
foreach ($file in $files) {
$fullName = [IO.Path]::GetFileNameWithoutExtension($file)
$CR = $fullName.Split("CRAPTFV")[-2]
$CT = $fullName.Split("CRAPTFV")[-3]
$P = $fullName.Split("CRAPTFV")[-4]
$A = $fullName.Split("CRAPTFV")[-5]
$S = $fullName.Split("CRAPTFV")[-6]
$CV = $fullName.Split("CRAPTFV")[-7]
$DEM = Select-String -Path $file -Pattern("Total Covered Demand = (\d*.?\d*)")
$REL = Select-String -Path $file -Pattern("\d+\t+\s+(\d+\.{1}\d+)\t+\s+(\d\.{1}\d+)\t+\s+(\d+.{1}\d+)") -AllMatches
Write-Output "$CT,$CR,$CV,$S,$A,$P,$DEM.Matches.groups[1]" | Out-File "fileadress" -Append
}
The goal is to use the table from each file to calculate some measurement and then append it to an output file. I seem to have yanked them out with $REL and I can see all the values with this code
$REL = Select-String -Path $file -Pattern("\d+\t+\s+(\d+\.{1}\d+)\t+\s+(\d\.{1}\d+)\t+\s+(\d+.{1}\d+)") -AllMatches
Write-Host $REL.Matches
But when I type the following I can only see the first value for each file. This
Write-Host $REL.Matches.Groups[1]
produces this:
71.0000 71.0000 71.0000 71.0000 71.0000 71.0000
for all files.
Upvotes: 2
Views: 293
Answers (1)
JPBlanc
Reputation: 72680
If I imagine that 4 spaces give a tab here is a way to use $REL :
$REL.matches[0].Groups[2].Value gives 0.8351
$REL.matches[1].Groups[3].Value gives 49.6000
$REL.matches[X].Groups[Y].Value for a file gives the cell of th Y column of the X line. X and Y start from 0.
Upvotes: 1
Related Questions
- Extract a table from plain text file using Powershell
- Is there a way to convert tables of text into a PowerShell Object
- Extract specific multiple string from lines via regex
- Extract table data from file using Select-String
- How to extract specific values from a HTML table and copy them horizontally to a file using PowerShell?
- Regex to retrieve value from table
- PowerShell: Pull table from a string
- Get specific value from table
- Get powershell regex captures into a table
- Extract a Table from an HTML File with PowerShell or VBS