Solved

PowerShell Query to Count Files by Portion of Filename

Posted on 2015-02-23
5
584 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
[X]
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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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 …
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
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…

730 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