Capture Specific String in CSV

Question

I'm trying to capture a specific sequence of letters and numbers in a CSV field. The field can contain the following:

"Some random text WMM540" < cell text

EDIT: Here's the raw csv:

Employee ID,First Name,Surname,Base Store Code,Job Title  
555555,Mark,Testing-Process,Mo Newcastle Under Lyme WMM222,Merchandiser  
555556,Carly,O'Test,AS Congleton ASD1111,Supervisor

I need to capture the WMMXXX (where X is the number) in a variable, but there can be more codes. The possible codes I need to get are:

WMMXXX  
MORXXX  
ASDXXXX (4 digits instead of 3)  
WILXXX  
PLDXXX  
SUPXXX

.. and a few more.

I'm aware you can split and isolate text using regex, but I don't have enough experience with it.
Ultimately I need a variable that looks like: $v = 'WMMXXX'

Please could you help?

Many thanks

Something like $v = if ($cellValue -match '((?:WMM|MOR|WIL|PLD|SUP)\d{3}|ASD\d{4})') { $matches[1] } should work, but we really need more details... — Theo
– Theo, Commented Feb 6, 2021 at 11:08
If it is always the last space-separated token, regardless of its content: $v = $cellValue -replace '^.* '. This simply strips everything up to and including the last space from the input string. — zett42
– zett42, Commented Feb 6, 2021 at 11:26

Theo · Accepted Answer · 2021-02-06 15:55:36Z

2

Turning my comment into an answer

Since you apparently only want to find very specific codes, you can do something likt this:

$regex = '((?:WMM|MOR|WIL|PLD|SUP)\d{3}|ASD\d{4})'
Import-Csv -Path 'PathToTheFile.csv' | ForEach-Object {
     if ($_.'Base Store Code' -match $regex) { $matches[1] }
}

edited Feb 6, 2021 at 15:55

answered Feb 6, 2021 at 15:25

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

1 Comment

Phoenix2207 Over a year ago

Upvoted, accepted and very much appreciated, thank you!

mklement0 · Accepted Answer · 2021-02-06 16:27:23Z

1

Note:

It sounds like you ultimately need a regex to extract the substring of interest, as shown in Theo's helpful answer.
This answer shows a simplified approach based on the assumption that the substring of interest is always the last whitespace-separated token in the column of interest.

Use Import-Csv, extract the column values of interest (column Base Store Code), and extract the last whitespace-separated token from each, which the unary form of -split, the string-splitting operator allows you to do (index [-1] returns the last element of the resulting array):

Import-Csv in.csv | ForEach-Object { (-split $_.'Base Store Code')[-1] }

With your sample data as input, this yields:

WMM222
ASD1111

edited Feb 6, 2021 at 16:27

answered Feb 6, 2021 at 15:03

mklement0

452k68 gold badges728 silver badges987 bronze badges

Collectives™ on Stack Overflow

Capture Specific String in CSV

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related