Create a csv of file attributes recursively from a directory parameter in PowerShell

Question

I'm a bit new to PowerShell (version 5) and was wondering if there are any improvements I could make to the following script. Any suggestions for style, code, etc. are all welcome.

The script creates a csv of file attributes and extended properties recursively from a directory parameter. I'm going to then load the csv files into SQL Server.

I'm not sure if there is a more efficient way of filtering the files by last modified date. I need to run it against 30 million files in batches of 500,000.

I've included an example of the UNC path that I use to produce the results file name in the $searchPath variable.

I'm currently running this script in a .bat file. Is there a better way of running these scripts concurrently?

PowerShell Script

$searchPath = '\\server\ca$\los angeles\documents\*.*';  #An example of the path format used as a parameter for $args[0]
$resultsFileName = '';
$startDate = '01-Jul-2018';
$endDate = '31-Jul-2019';

$shell = New-Object -COMObject Shell.Application;

#set date defaults
$dateTimeFormat = 'dd-MMM-yyyy HH:mm:ss.fff';
$executeStart = Get-Date;
$executeStart = $executeStart.ToString($dateTimeFormat);
Write-Host 'Execute Start:' $executeStart;

#get the parent folder from the search path
Write-Host 'Search Path:' $searchPath;
$folder = ($searchPath).Substring(1, ($searchPath).Length - 4);
$parent = Split-Path $folder;

#if the path is not in the expected format dont set the results file name
 if($parent.Length -gt 2)
{
    #get the state name
    $state = Split-Path $parent;
    $state = Split-Path $state -Leaf;
    $state = $state -replace '\$','';
    $state = $state.ToLower();

    #get the office name
    $office = Split-Path $parent -Leaf;
    $office = $office -replace '\W','-';
    $office = $office.ToLower(); 
    $resultsFileName = $state + '_' + $office;
};

#format the result file name and path
$resultFileTimestamp = Get-Date -format 'yyyyMMdd_HHmmss.fff';
if($resultsFileName -eq '') {$resultsFileName = 'results_' + $resultsFileName + '_' + $resultFileTimestamp};

$resultsFile = "C:\Temp\Results\$resultsFileName.csv";
Write-Host 'Results File:' $resultsFile;

    $linenumber = 1;

    #get the file attributes from the recursively from the search path
    Get-Childitem -recurse -Path $searchPath | ? {($_.LastWriteTime -gt $startDate -AND $_.LastWriteTime -lt $endDate) -OR ($_.CreationTime -gt $startDate -AND $_.CreationTime -lt $endDate)}  | ForEach-Object { 
        $fullName = $_.FullName;
        $folder = Split-Path $fullName;
        $file = Split-Path $fullName  -Leaf;
        $shell = New-Object -COMObject Shell.Application;
        $shellfolder = $shell.Namespace($folder);
        $shellfile = $shellfolder.ParseName($file);

        #loop through the extended properties looking for the columns we want
        for ($a ; $a  -le 325; $a++)
           { 
             if($shellfolder.getDetailsOf($File, $a))
               {
                $keyValue = $shellfolder.GetDetailsOf($null, $a)
                   switch ( $keyValue )
                    { 
                    'Attributes' { $Attributes = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Title' { $Title = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Authors' { $Authors = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Last printed' { $LastPrinted = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Date last saved' { $DateLastSaved = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Pages' { $Pages = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Word count' { $WordCount = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'Total editing time' { $TotalEditingTime = $shellfolder.GetDetailsOf($shellfile, $a) }
                    'File count' { $FileCount = $shellfolder.GetDetailsOf($shellfile, $a) }
                    }
               } 
           } 
         $a=0;

        #format extended properties
        $LastPrinted = $LastPrinted -replace '[^\p{L}\p{Nd}/(/}/_/:/ ]', ''; #replace non date characters
        if($LastPrinted -ne '') {$LastPrinted = [datetime]::parseexact($LastPrinted, 'd/MM/yyyy h:mm tt', $null).ToString($dateTimeFormat) } else {$LastPrinted = 'NULL'};
        $DateLastSaved = $DateLastSaved -replace '[^\p{L}\p{Nd}/(/}/_/:/ ]', ''; #replace non date characters
        if($DateLastSaved -ne '') {$DateLastSaved = [datetime]::parseexact($DateLastSaved, 'd/MM/yyyy h:mm tt', $null).ToString($dateTimeFormat) } else {$DateLastSaved = 'NULL'};
        $Title = $Title.replace("`n","").replace("`r",""); #remove carriage return line feed from string
        $Authors = $Authors.replace("`n","").replace("`r",""); #remove carriage return line feed from string

        #show the user what file number the script is on
        $currentFileDateTime = Get-Date -format $dateTimeFormat;
        Write-Host $linenumber,  $currentFileDateTime,  $fullName;

        #format the output of the csv file
      Get-Content $fullName | Measure-Object -Character -Word | `
        Select-Object -ExcludeProperty Property `
          @{ Name = 'LineNumber'; Expression={$linenumber}} `
        , @{ Name = 'ExtractTime'; Expression={ Get-Date -format $dateTimeFormat }} `
        , @{ Name = 'FullName'; Expression={ $fullName }} `
        , @{ Name = 'FilePath'; Expression={ $folder }} `
        , @{ Name = 'FileName'; Expression={ $file }} `
        , @{ Name = 'FileSizeKb'; Expression={ (Get-Item $fullName).length / 1024 }} `
        , @{ Name = 'CreationTime'; Expression={(Get-ChildItem $fullName).CreationTime.ToString($dateTimeFormat) }} `
        , @{ Name = 'LastWriteTime'; Expression={(Get-ItemProperty $fullName).LastWriteTime.ToString($dateTimeFormat) }} `
        , @{ Name = 'Attributes'; Expression={ $Attributes.ToString() }} `
        , @{ Name = 'Title'; Expression={ $Title.ToString() }} `
        , @{ Name = 'Authors'; Expression={ $Authors.ToString() }} `
        , @{ Name = 'LastPrinted'; Expression={ $LastPrinted.ToString() }} `
        , @{ Name = 'LastSaved'; Expression={ $DateLastSaved.ToString() }} `
        , @{ Name = 'PageCount'; Expression={ $Pages.ToString() }} `
        , @{ Name = 'WordCount'; Expression={ $WordCount.ToString() }} `
        , Characters `
        , @{ Name = 'TotalEditingTime'; Expression={ $TotalEditingTime.ToString() }} `
        , @{ Name = 'FileCount'; Expression={ $FileCount.ToString() }}; `
        $linenumber ++ `
    } | Export-Csv -NoTypeInformation -Path $resultsFile;

$executeEnd = Get-Date;
$executeEnd = $executeEnd.ToString($dateTimeFormat);
Write-Host 'Execute End:' $executeEnd;

Example of CSV file

Answer 1

As ForEach-Object and Where-Object are slow, you had better to use the foreach statement instead of them when working with large data.
Also, because it is useless to get all extended properties, narrow down the property numbers to be obtained in advance.
Although Select-Object is useful, it will be slower if you use "calculated property", so it is better to create a new object directly using [pscustomobject]@{}.

# settings
$startDate = Get-Date "01-Jul-2018"
$endDate = Get-Date "31-Mar-2029"
$resultDir = mkdir "E:\tets\Results" -Force
$dateTimeFormat = "dd-MMM-yyyy HH:mm:ss.fff"
$pathList = @(
    "\\server\ca$\los angeles\documents"
    "\\server\ca$\san diego\documents"
    "\\server\ca$\san francisco\documents"
)

$exAttr = @{
    Title = 21
    Authors = 20
    LastPrinted = 200
    LastSaved = 154
    Pages = 157
    WordCount = 160
    TotalEditingTime = 159
    FileCount = 163
}


# start
"Execute Start: {0}" -f (Get-Date -Format $dateTimeFormat) | Write-Host

$shell = New-Object -Com Shell.Application

foreach ($path in $pathList) {

    # create a result file.
    $targetDir = Get-Item -LiteralPath $path
    $resultFileName = "{0}_{1}_{2}.csv" -f @(
        $targetDir.Parent.Parent.Name.TrimEnd("$").ToLower()
        $targetDir.Parent.Name.ToLower() -replace "\W","-"
        Get-Date -Format yyyyMMdd_HHmmss.fff
    )
    $resultsFile = New-Item -Path $resultDir -Name $resultFileName -Force

    $lineNumber = 1

    # create customized file information objects.
    $list = foreach ($file in Get-Childitem $targetDir -File -Recurse) {

        if ($file.LastWriteTime -le $startDate -or $file.LastWriteTime -ge $endDate) { continue }

        $exData = @{}

        # get extended properties of office file.
        if ($file.Extension -in ".docx",".xlsx") {
            $dirObj = $shell.NameSpace($file.DirectoryName)
            $itemObj = $dirObj.ParseName($file.Name)
            $exAttr.GetEnumerator() | & { process { $exData[$_.Key] = $dirObj.GetDetailsOf($itemObj, $_.Value) -replace "\p{Cf}" } }

            [void][Runtime.InteropServices.Marshal]::ReleaseComObject($itemObj)
            [void][Runtime.InteropServices.Marshal]::ReleaseComObject($dirObj)
        }

        [pscustomobject]@{
            LineNumber = $lineNumber++
            ExtractTime = Get-Date -format $dateTimeFormat
            FullName = $file.FullName
            FilePath = $file.DirectoryName
            FileName = $file.Name
            FileSize = $file.Length / 1KB
            CreationTime = $file.CreationTime.ToString($dateTimeFormat)
            LastWriteTime = $file.LastWriteTime.ToString($dateTimeFormat)
            Attributes = -join ($file.Attributes.Split(",") | & { process { $_.TrimStart()[0] } })
            Title = $exData.Title
            Authors = $exData.Authors
            LastPrinted = if ($exData.LastPrinted) { Get-Date $exData.LastPrinted -Format $dateTimeFormat } else { $null }
            LastSaved = if ($exData.LastSaved) { Get-Date $exData.LastSaved -Format $dateTimeFormat } else { $null }
            PageCount = $exData.Pages
            WordCount = $exData.WordCount
            TotalEditingTime = $exData.TotalEditingTime
            FileCount = $exData.FileCount
        }
    }

    # output to csv file.
    $list | Export-Csv $resultsFile -NoTypeInformation
    $resultsFile
}

[void][Runtime.InteropServices.Marshal]::ReleaseComObject($shell)

"`nExecute End: {0}`n" -f (Get-Date -Format $dateTimeFormat) | Write-Host

If you want to run in parallel, there is a way to use Start-Job.

$scriptBlock = {
    param(
        $path,
        $startDate,
        $endDate,
        $resultDir,
        $dateTimeFormat
    )

    $exAttr = @{
        ...
    }
    $shell = New-Object -Com Shell.Application

    # create a result file.
    $targetDir = Get-Item -LiteralPath $path
    ...
    ...
}

$pathList | foreach { $i = 0 } { Start-Job -Name $_ -ScriptBlock $scriptBlock -ArgumentList $_,$startDate,$endDate,$resultDir,$dateTimeFormat }

If file retrieval and filtering is a bottleneck, you can write partially in C#.

Add-Type -TypeDefinition @"
using System;
using System.IO;
using System.Collections.Generic;

public static class MyFileIO
{
    public static List<FileInfo> GetFiles(DirectoryInfo dir, DateTime startDate, DateTime endDate)
    {
        var files = new List<FileInfo>();
        var queue = new Queue<DirectoryInfo>();
        queue.Enqueue(dir);
        while (queue.Count > 0)
        {
            dir = queue.Dequeue();
            foreach (var subDir in dir.GetDirectories()) queue.Enqueue(subDir);
            foreach(var file in dir.GetFiles())
                if(file.LastWriteTime > startDate && file.LastWriteTime < endDate) files.Add(file);
        }
        return files;
    }
}
"@

foreach ($file in [MyFileIO]::GetFiles($targetDir, $startDate, $endDate)) {
    ...
}