Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PowerShell Query to Count Files by Portion of Filename

Posted on 2015-02-23
5
Medium Priority
?
761 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

824 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