Script to extract sequential numbers in a .txt file

gubber12 · 2021-02-25T16:17:55.000Z

Hello,

I am looking to see if someone can help me create a script to extract sequential numbers with 8 or more digits from a text file.

I work in data quality and multiple times per week I have to do this manually.

Here is an example of what I am working with:

There are 4 lines of text for each number that I need to extract.

“First set of words and numbers”

“Second set of words and numbers”

“Third set”

“Fourth set - #11111111”

I need to extract the number after the “hashtag” for each set and then put them sequentially like:

11111111
11111112

Once they are listed sequentially I can copy them and put them in our work program. Essentially, I would like to just automate the process of getting those numbers every time I have to. Ideally, I would want something that I can copy and paste the lines of text into, it would extract and list those numbers, and then I could copy the numbers and paste them back into our program.

Anything to save the time it takes to remove everything before the 8 digit number manually would be helpful. Thank you in advance. I didn’t see anything that looked like it could help this specific situation in the previously answered topics of similar nature.

Evan7191 · 2021-02-25T17:41:55.000Z

Do you need only the numbers? Does the text matter at all? Can you post a sanitized sample of the text file?

EDIT: typo

Neally · 2021-02-25T17:45:35.000Z

Welcome

Gubber12:

help me create a script

we’re happy to help but not a script writing service.
Post What you have tried or where you are stuck and we’ll try to help from there.
If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

gubber12 · 2021-02-25T18:26:45.000Z

@techadmin8

Yes, only the 8 digit numbers matter in the end. The text field is different with every batch of numbers which is why I used the format I did. I guess I could clarify that more in the future. I can’t really upload a sample because it might contain personally-identifying information but hopefully, this gets you close as it shows pretty much the exact format the text appears after I copy it from our program.

sample.jpg800×439 21.8 KB

I know some basic “get” commands but nothing that I know could target those last numbers specifically much less in quantity. I am looking to get the output something like this:

Sample_2.jpg800×439 12.5 KB

But the number of numbers can vary between each instance when I am working on them. I could have a set of 4 like I show here or 100 or more depending on the day or week.

@alexw

Thank you for that reminder.

Neally · 2021-02-25T18:32:50.000Z

Gubber12:

I can’t really upload a sample because it might contain personally-identifying information

the point of the sample is that you provide the data (same format as prod) but scrub that PII out, so it’s easier for us to help you.

the sample you gave is not super helpful, what do you do to get those numbers? are there different numbers possibly in the file? Do you use regex to get the numbers?

Getting the last 8 is the easy part (e.g substrings) , making sure you get the right number, that’s the issue.

Is the input file always in that format? i.e. the line you care about is every 4 lines? You could also match by the # symbol, or the hyphen, assuming your input file is always that format.

Neally · 2021-02-25T18:39:25.000Z

e.g.

    some data
 more data here, 24234234234
 234234234234234
 345345345345345345 - #12345678
     some other data
 more data here, 24234234234
 234234234234234
 345345345345345345 - #12345679
      some other data
 more data here, 24234234234
 234234234234234
 345345345345345345 - #12345680

$data = get-content ".\sampler.txt"

$nums =
for ($i = 3; $i -le ($data|measure).count; $i = $i + 4)   { 
    ($data[$i] -split "#")[1]
}
$nums | out-file ".\sampler_post.txt"

output:

12345678
12345679
12345680

Neally · 2021-02-25T18:44:07.000Z

if the format is always consistent you can do it even easier

$data = get-content ".\sampler.txt"
$data -match "#" | 
foreach-object{($_ -split "#")[1]}

basically get all the lines that have a pound (#) in it and then just take what’s after the pound symbol

many ways to ‘skin the meow meow’

Neally · 2021-02-25T18:45:38.000Z

Gubber12:

and then put them sequentially

once you have all the numbers, powershell has a ‘sort-object’ cmdlet that should help with that.

learn.microsoft.com

Sort-Object (Microsoft.PowerShell.Utility) - PowerShell

The Sort-Object cmdlet sorts objects in ascending or descending order based on object property values. If sort properties aren't included in a command, PowerShell uses default sort properties of the first input object. If the input object's type has...

gubber12 · 2021-02-25T19:06:45.000Z

Neally:

if the format is always consistent you can do it even easier
$data = get-content ".\sampler.txt"
$data -match "#" | 
foreach-object{($_ -split "#")[1]}
basically get all the lines that have a pound (#) in it and then just take what’s after the pound symbol

many ways to ‘skin the meow meow’

Thank you for the suggestion and clarification on the forum etiquette.

To answer your questions, yes. The format is always consistent.

I appreciate the help and apologize for the BM. I think this is what I was looking for and if not, I should be able to fine tune it from there.

Again, thank you.

Evan7191 · 2021-02-25T19:15:49.000Z

This can be simplified even more with RegEx.

$Text = Get-Content -Path "Drive:\Folder\File.txt"
$Text -match '#' -ireplace '.*#','' | Sort-Object