Pulling out specific text from csv

[penfold]

Hi,

I've been searching round and I now can't see the wood from the tress. I have a csv file which is providing some scanning information for us. Currently there is too much data in it and it takes a manual effort to go through to get anything useful. What I want to be able to do is import it to PowerShell and then take the column with data I'm interested in and pull out specific text only.

Example of the column:

Found entries missing:

(heading)

- KB1234567

- KB1234568

more text

I want to pull out the text KBxxxxxxx so I can then I can work with it.

I was playing around with splitting the column content, so a for each loop to do something like

$temp = $row.heading

$temp.split('-',2)

$temp[0]

From what I can tell, this will work if the text I want is at the start, but how do I do it if the text is in the middle, and there may be more than 1 occurrence? Can I do something to capture the next 8 Chars after the '-' or 'KB'? The problem I am having is how do I do this so I will capture all entries and not just the first one in the column? Am I able to do this via split or should I be doing something different?

 

Ultimately I want to be able to output every entry of KBxxxxxxx from the column and export it into another csv so I can work with only the data I am interested in.

[HPlum78]

So the simple way would be select-string with a pattern match.

Here is what MS has:

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-string?view=powershell-7.1

 

Good resource here:

https://adamtheautomator.com/powershell-grep/

 

And Ed Wilson (Dr Scrpto) post from 2011 is a good read as well

https://devblogs.microsoft.com/scripting/use-an-easy-powershell-command-to-search-files-for-information/

Edited May 25, 2021 by HPlum78

[Foresthippy]

*something, something* regular expressions. For example (KB)\d+ will match any string beginning with KB followed by numbers. I don't know enough about powershell or your particular needs to know how you would use this, but it's the ultimate tool for pattern matching in text data.

[penfold]

OK, that has allowed me to search for the line containing KB, but am I being a bit stupid (likely) how do I select the word from there so I can do something with it? If I am understanding select-string correctly, it allows me to search files to get files containing specific text, but it outputs the whole line? I still need to pull out the specific parts of the line I am looking for KBxxxxxx

[penfold]

OK, I think I have an idea how to proceed now. Thanks

[penfold]

Just a quick update in case anyone finds it useful...I ended up using this

$KB = (Select-String 'KB\d\d\d\d\d\d\d' $File -Allmatches).Matches.Value $KB | Sort-Object -Unique

 

This seems to go through the file I want to check and grab all the matches for KB* I then use the Out-file command to export the unique entries. I can now work with this data which is much smaller than the original file.

 

Thanks guys