Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PowerShell Query to Count Files by Portion of Filename

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

Industry Leaders: 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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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