Cleanup huge text file containing domain

user_13562697 0
Viewed 61

I have a database that contains a log of domains listed in the following matter:

.youtube.com
.ziprecruiter.com
0.etsystatic.com
0.sparkpost.com
00.mail.ne1.yahoo.com
00072e01.pphosted.com
00111b01.pphosted.com
001d4f01.pphosted.com
011.mail.bf1.yahoo.com
1.amazonaws.com

How would I go about cleaning them up using powershell or grep, though I rather use powershell, so that they contain only the root domain with the .com extension and remove whatever word and . is before that.

I'm thinking best way to do is is a query that looks for dots from right to left and removes the second dot and whatever comes after it. For example 1.amazonaws.com here we remove the second dot from the right and whatever is after it? i.e.

youtube.com
ziprecruiter.com
etsystatic.com
yahoo.com
pphosted.com
amazonaws.com
powershell-3.0 powershell-2.0 grep powershell
3 Answers

You can use the String.Trim() method to clean leading and trailing dots, then use the regex -replace operator to remove everything but the top- and second-level domain name:

$strings = Get-Content database_export.txt

@($strings |ForEach-Object Trim '.') -replace '.*?(\w+\.\w+)$','$1' |Sort-Object -Unique
user_7126490

You can read each line into an array of strings with Get-Content, Split on "." using Split(), get the last two items with [-2,-1], then join the array back up using -join. We can then retrieve unique items using -Unique from Select-Object.

Get-Content -Path .\database_export.txt | ForEach-Object {
    $_.Split('.')[-2,-1] -join '.'
} | Select-Object -Unique

Or using Select-Object -Last 2 to fetch the last two items, then piping to Join-String.

Get-Content -Path .\database_export.txt | ForEach-Object {
    $_.Split('.') | Select-Object -Last 2 | Join-String -Separator '.'
} | Select-Object -Unique

Output:

youtube.com
ziprecruiter.com
etsystatic.com
sparkpost.com
yahoo.com
pphosted.com
amazonaws.com
user_52389150

here is yet another method. [grin]

what it does ...

  • creates an array of strings to work with
    when ready to do this for real, remove the entire #region/#endregion section and use Get-Content to load the file.
  • iterates thru the $InStuff collection of strings
  • splits the current item on the dots
  • grabs the last two items in the resulting array
  • joins them with a dot
  • outputs the new string to the $Results collection
  • shows that on screen

the code ... 

#region >>> fake reading in a text file
#    in real life, use Get-Content
$InStuff = @'
.youtube.com
.ziprecruiter.com
0.etsystatic.com
0.sparkpost.com
00.mail.ne1.yahoo.com
00072e01.pphosted.com
00111b01.pphosted.com
001d4f01.pphosted.com
011.mail.bf1.yahoo.com
1.amazonaws.com
'@ -split [System.Environment]::NewLine
#endregion >>> fake reading in a text file

$Results = foreach ($IS_Item in $InStuff)
    {
    $IS_Item.Split('.')[-2, -1] -join '.'
    }

$Results

output ... 

youtube.com
ziprecruiter.com
etsystatic.com
sparkpost.com
yahoo.com
pphosted.com
pphosted.com
pphosted.com
yahoo.com
amazonaws.com

please note that this code expects the strings to be more-or-less-valid URLs. i can think of invalid ones that end with a dot ... and those would fail. if you need to deal with such, add the needed validation code.

another idea ... if the file is large [tens of thousands of strings], you may want to use the ForEach-Object pipeline cmdlet [as shown by RoadRunner] to save RAM at the expense of speed.

user_30809080
Related