Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

PowerShell Query to Count Files by Portion of Filename

Posted on 2015-02-23
5
552 Views
Last Modified: 2015-02-24
I have numerous directories under a root directory. Each directory has multiple files. The first 5 characters of the filename are specific to a company name, and the next two characters to a file type. I want to write two queriers, the first will give me a report (can output to a file directory path, filename, first five characters of filename, count of files matching the first five characters). The second query would do the same but give me an output of value of positions 6 and 7 of filename and count. It would need to recurse through all directories under the root and give me a count of files in each. Anything after the first 7 characters is irrelevant.

Samele data might look like:
directory01
abcde01.r1234.txt
abcde02.r1234.txt
abcde03.r1234.txt
abcde04.r1234.txt
efghi01.r1234.txt
efghi03.r1234.txt
efghi04.r1234.txt

would output
directory01,adbde,4
directory01,efghi,3

and subsequently:
directory01,01,2
directory01,02,1
directory01,03,2
directory01,04,2

There can be up to 400+ files in a subdirectory. There should be no more than 10 distinct values in positions 6 and 7, and there could be 50+ distinct values for the first five characters of the filename.
0
Comment
Question by:dbbishop
  • 3
  • 2
5 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 40627376
Hi, here's my take on the first script.  I will stress that I'm not I'm not very proficient as Powershell, so it took me a while to get my head around this.
Get-ChildItem -Path "C:\Temp\Scripts\Output" -Recurse -Directory |
ForEach {
    Get-ChildItem -Path $_.FullName -File |
    ForEach {
        $Prop = [Ordered]@{
            'Directory'=$_.Directory.Name
            'Company'=$_.Name.Substring(0, 5)
        }
        New-Object PsObject -Property $Prop
    } | Group-Object Company,Directory |
        Select @{'n'='Directory';e={$_.Name.substring(7)}},@{'n'='Company';e={$_.Name.substring(0,5)}},Count
}

Open in new window


and here's my take on part 2:
Get-ChildItem -Path "C:\Temp\Scripts\Output" -Recurse -Directory |
ForEach {
    Get-ChildItem -Path $_.FullName -File |
    ForEach {
        $Prop = [Ordered]@{
            'Directory'=$_.Directory.Name
            'Company'=$_.Name.Substring(5, 2)
        }
        New-Object PsObject -Property $Prop
    } | Group-Object Company,Directory |
        Select @{'n'='Directory';e={$_.Name.substring(4)}},@{'n'='Company';e={$_.Name.substring(0,2)}},Count
}

Open in new window


Regards,

Rob.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 40627382
Actually, I have just shortened that.  Try these:
Get-ChildItem -Path "C:\Temp\Scripts\Output" -Recurse -Directory |
Select-Object -ExpandProperty FullName | 
Get-ChildItem -File |
Select-Object @{n='Directory';e={$_.Directory.Name}},@{n='Company';e={$_.Name.Substring(0,5)}} |
Group-Object Company,Directory |
Select-Object @{'n'='Directory';e={$_.Name.substring(0,5)}},@{n='Company';e={$_.Name.substring(7)}},Count

Open in new window


Second one:
Get-ChildItem -Path "C:\Temp\Scripts\Output" -Recurse -Directory |
Select-Object -ExpandProperty FullName | 
Get-ChildItem -File |
Select-Object @{n='Directory';e={$_.Directory.Name}},@{n='Company';e={$_.Name.Substring(5,2)}} |
Group-Object Company,Directory |
Select-Object @{'n'='Directory';e={$_.Name.substring(4)}},@{n='Company';e={$_.Name.substring(0,2)}},Count

Open in new window


Regards,

Rob.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 40629202
Had to break that one-liner down. Much easier to see first what was happening from the first example. For a newbie, short isn't always better :-) (there is probably a motto in there somewhere).
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40629231
I agree entirely.  I didn't actually realise it was a one-liner until I posted it and looked at it.  It can actually be posted into a script with the line breaks as is, to make it readable, since Powershell automatically concatenates after the pipe character.

I tend to start out a bit bigger (as in the first code), and then look at it to see where I can make it more efficient by examining the properties that are being passed along the pipeline.  As always, I'm impressed with the efficiency with which a task can be done.  The same thing would have been at least 30 lines in VBScript.

Regards,

Rob.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40629273
btw, take a look at http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28623690.html. Hopefully you can help. Newbie question.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question