Count occurences of string (from txt file) in text file and export to CSV or txt file

Question

I have used different sources here on Stack Overflow, for example this, to clean my alarm file (data.txt) from spam and to extract the unique occurences to this file (unik.txt). The next step, where i am stuck, is to use unik.txt and count the number of occurences in data.txt, and to export the string and count to a text or csv-file. As you may have guessed it is a way of creating an alarm statistic. I have considered using other methods/languages but will first want to try this way.

I have read this post also without coming to a close.

Extract from data.txt:

D_TA204_GT1_DV_AL
D_TA204_GT41_DV_AL
D_TA204_GT31_DV_AL
D_TA204_GT21_DV_AL
U_TA364_GT11_LARM
U_TA364_GT11_LARM
U_TA364_GT11_LARM
U_FF415_GT46_L_AL
U_TA364_GT11_LARM
D_TA204_GT31_DV_AL
U_KB6_GT11_DV_AL
U_FF415_GT46_L_AL
D_TA204_GT21_DV_AL
U_KB6_GT11_DV_AL
TRE-11-11-I033
TRE-11-11-D5394
U_KB6_GT11_DV_AL
U_KB6_GT11_DV_AL
U_KB6_GT11_DV_AL

Extract from unik.txt:

D_TA204_GT1_DV_AL
D_TA204_GT41_DV_AL
D_TA204_GT31_DV_AL
D_TA204_GT21_DV_AL
U_TA364_GT11_LARM
U_FF415_GT46_L_AL
U_KB6_GT11_DV_AL

For help with your code: show your code (a [mcve] to be precise) and explain what about that code didn't work the way you expected. — Ansgar Wiechers, Jun 21 '18 at 08:21

Julien Nury · Accepted Answer · 2018-06-21T12:03:56.203

If your file contains only one alarm by line, you can use Get-Content to create a list of alarms:

$alarmList = Get-Content -Path .\data.txt

If your file contains alarms separated by a space (as in your example before edition) you may use the Select-String CmdLet to extract all alarms of your data file:

$alarmList = (Select-String -Path .\data.txt -Pattern '[^ ]+' -AllMatches).Matches.Value

The expression '[^ ]+' will capture strings of one or more characters without space.

Then, use Group-Object to count each occurence of alarm in the list:

$alarmList | Group-Object -NoElement

You'll obtain statistics like:

Count Name                                                                                                                                                                              
----- ----                                                                                                                                                                            
    1 D_TA204_GT1_DV_AL                                                                                                                                                               
    1 D_TA204_GT41_DV_AL                                                                                                                                                                                                                                                                                                     
    4 U_TA364_GT11_LARM                                                                                                     
    2 U_FF415_GT46_L_AL

Finally, sending statistics in a CSV file:

(Select-String -Path '.\data.txt' -Pattern '[^ ]+' -AllMatches).Matches.Value |
Group-Object -NoElement |
ConvertTo-Csv -Delimiter "`t" -NoTypeInformation |
Out-File -FilePath '.\Statistics.csv'

This works perfectly. I need to read up on this command and powershell. What does '[^ ]+ mean? It seems to me that this script itself checks what unique values are in the file and counts them. Is this so? — Egan, Jun 21 '18 at 10:46
Group-Object does the work, and as every line in data.txt is a key, Select-String with a RegEx that matches at least one non space character `[^ ]+` isn't neccessary at all. `Get-Content .\data.txt |Group-Object -Noelement` is all you need. — , Jun 21 '18 at 11:15
Dan: I added some explanations. Don't forget to 'accept' the answer if it solution your problem. LotPings: the original sample (edited since) was separated by spaces. Thank-you for the '-NoElement' proposal ! — Julien Nury, Jun 21 '18 at 12:06

aschipfl · Answer 2 · 2018-06-25T18:24:45.537

1

Although you did not show any own efforts, I decided to provide a tiny script:

@echo off
rem // Iterate over lines of `unik.txt`:
for /F usebackq^ delims^=^ eol^= %%I in ("unik.txt") do (
    rem // Count number of occurrences in `data.txt`:
    for /F %%J in ('^< "data.txt" find /C "%%I"') do (
        rem // Return TAB-separated item and count:
        echo/%%I    %%J
    )
)

Of course you can change the output order or format according to your needs.

edited Jun 25 '18 at 18:24

answered Jun 21 '18 at 11:37

aschipfl

33,626
12
54
99

1

Great. I had started using a three-row script and was able to output the number for a single one. Then Julien provided a fine powershell example covering it all. Will learn more about powershell it seems... – Egan Jun 21 '18 at 12:29

Egan · Answer 3 · 2018-06-23T10:37:32.947

0

A friend just showed me an alternative solution using AWK. I prefer powershell though. Thanks all.

awk "{s[$0]=s[$0] + 1}END{for (i in s) {print i, s[i]}}"  data.txt > t1

edited Jun 23 '18 at 10:37

answered Jun 23 '18 at 10:24

Egan

43
5

Count occurences of string (from txt file) in text file and export to CSV or txt file

3 Answers3