CODEWITHSUNDEEP
powershell
user2725402
user2725402

Reputation: 4629

List of items in a folder using sub string of filename

I have a directory which contains .xls files named as follows:

Recon_[Account No]_[YYYYMMDD].xls

e.g:

Recon_9020111006076954416_20131216.xls

The Account number can be any number of characters between 16 and 20.

I need to get a list of items in this folder - but only the account numbers.

What would be FANTASTIC is if I could get a list of account numbers and then next to them the date the file was created (which can be the datestamp in the file name or the last modified date of the file), in YYYY/MM/DD format, sorted by date.

Like this:

9020111006076954416 2013/12/16
10201129080000235952 2013/12/17

I then need this list of accounts in a text file, or even better, an excel file. Any ideas?

Upvotes: 0

Views: 116

Answers (3)

Joey
Joey

Reputation: 354536

Fairly, easy, actually:

  1. First obtain the raw data

    Get-ChildItem *.xls |

  2. Then extract the properties you need from it:

    ForEach-Object {
    if ($_.Basename -match '^Recon_(?<account>\d+)_(?<date>\d+)$') {
        $_ | Add-Member NoteProperty AccountNumber $Matches.account
    }
} |

  3. Select those properties you are interested in (we are still dealing with the original FileInfo object, we just added the account number to it):

    Select-Object AccountNumber,LastWriteTime

    You could make the header nicer as well:

    Select-Object @{l='Account Number'; e={$_.AccountNumber}}, @{l='Date'; e={$_.LastWriteTime}}

At this point you have something you can display on screen nicely. You can then continue formatting the data by piping it into another ForEach-Object:

ForEach-Object {
    '{0}`t{1:yyyy-MM-dd}' -f $_.AccountNumber, $_.LastWriteTime
}

or convert it to CSV (which Excel can then open) by piping into ConvertTo-Csv:

ConvertTo-Csv foo.csv

To recap:

$data = Get-ChildItem *.xls |
          ForEach-Object {
              if ($_.Basename -match '^Recon_(?<account>\d+)_(?<date>\d+)$') {
                  $_ | Add-Member NoteProperty AccountNumber $Matches.account
              }
          } |
          Select-Object AccountNumber,LastWriteTime

$data | ForEach-Object {
    "{0}`t{1:yyyy-MM-dd}" -f $_.AccountNumber, $_.LastWriteTime
} | Out-File foo.txt

$data | ConvertTo-Csv foo.csv

Upvotes: 1

Shay Levy
Shay Levy

Reputation: 126752

Give this a try:

Get-ChildItem -Filter *.xls | Where-Object {$_.BaseName -match '^Recon_\d{16,20}_\d+$'} | ForEach-Object{

    $id,$date = $_.BaseName.Split('_')[1..2]

    New-Object PSObject -Property @{
       AccountNumber = $id
       DateCreated = $date.Insert(4,'/').Insert(7,'/')
    }

} | Export-Csv itemList.csv -NoTypeInformation

Upvotes: 1

Azadeh Radkianpour
Azadeh Radkianpour

Reputation: 971

Get all files of this directory. in a loop get file name and split it by '_'. the second item of array is account number and the third one is date.

Upvotes: 0

Related Questions