Solved

PowerShell Query to Count Files by Portion of Filename

Posted on 2015-02-23
5
636 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
This article will help you understand what HashTables are and how to use them in PowerShell.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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