Windows – Powershell – Import-CSV then use specific column for exclusion array

csvimportpowershellwindows

I'm having a bit of a day with this one. I am attempting to use powershell to import a CSV file which is a log file for XML's that are being processed later on down the script.

The goal is to have an if statement towards the top of the script to exclude $_.FullName of each file that has already been processed that way if the script is run multiple times, it will read this column and not process the file a second time.

Note: I am very new to powershell sorry for the sloppy syntaxing

Below is the current code and attempt that I have

$sourceDir = "C:\test" #Production Folder "D:\Vodassets\_PreImport_Success"
$tempDir = "C:\test-temp"
$targetDir = "C:\test-copyback"
$Date = Get-Date -format MM-dd-yyyy
$Time =     "{0:h:mm:ss tt zzz}" -f (get-date)
$LogFile = "Disney-Log.csv"


### Copy Disney Metadata.xml's to a temporary folder for the editting process
if (Test-Path $LogFile){
    $ImportLog = Import-CSV $LogFile
    $GetXML = @(Get-ChildItem $sourceDir -recurse -filter "Metadata.xml" -exclude {$LogFile.Fullname} | Where-Object {$_.FullName -like "*disney*"})
} else {
    $GetXML = @(Get-ChildItem $sourceDir -recurse -filter "Metadata.xml" | Where-Object {$_.FullName -like "*disney*"})
}
$OutXML = ForEach-Object { $GetXML } | Select LastWriteTime,Fullname,DirectoryName
if ($GetXML.count -eq 0){
    write-host "No files to copy. Ending Process....." -foregroundcolor yellow -backgroundcolor black 
} else {
    ForEach ($File in $GetXML)
    {   $Path = $File.DirectoryName.Replace($sourceDir,$tempDir)
        if (-not (Test-Path $Path)) {
            Write-Host "Destination $Path doesn't exist, creating it." -foregroundcolor yellow -backgroundcolor black
            New-Item -Path $Path -ItemType Directory
            Copy-Item -Path $File.FullName -Destination $Path -ErrorAction silentlyContinue }
            elseif (-not $?) { write-warning "Failed to copy $($File.Fullname)" }
            else { write-host "Succesfully copied $($File.Fullname) to $($tempDir)" -foregroundcolor green -backgroundcolor black }
        }



### Edit XML Process
    ForEach ($File in $GetXML)
    {   $Path = $File.DirectoryName.Replace($sourceDir,$tempDir)
        if (Test-Path $Path) {
            $xmlData = [xml](Get-Content $File.FullName)
            foreach ($group in $xmlData){
                $xmlData.assetpackages.assetpackage.'Type' = 'SVOD'
                $xmlData.assetpackages.assetpackage.'Product' = 'SVOD'
                $xmlData.assetpackages.assetpackage.'Name' = 'Disney Family Movies'
            }
        }
        $xmlData.Save($File.Fullname)   
    }


    $OutXML | Export-Csv $LogFile -NoTypeInformation -Force -Append


### Copy Files to VOD Import Server
    $import = (Get-ChildItem $tempDir -recurse -filter "Metadata.xml" | Where-Object {$_.FullName -like "*disney*"})
    if ($import.count -eq 0) {
        write-host "No files to import. Ending Process....." -foregroundcolor yellow -backgroundcolor black
    } else {
        ForEach ($File in $import)
        {   $Path = $File.DirectoryName.Replace($tempDir,$targetDir)
            if (-not (Test-Path $Path)) {
                Write-Host "Destination $Path doesn't exist, creating it." -foregroundcolor yellow -backgroundcolor black
                New-Item -Path $Path -ItemType Directory
                Copy-Item -Path $File.FullName -Destination $Path -ErrorAction silentlyContinue }
                elseif (-not $?) { write-warning "Failed to copy $($File.Fullname)" }
                else { write-host "Succesfully copied $($File.Fullname) to $($targetDir)" -foregroundcolor green -backgroundcolor black }
            }
    }


### Cleanup temporary directory
    if (-not (Test-Path $tempDir | Where-Object {$_.FullName -like "*disney*"})){
        Get-ChildItem $tempDir -recurse | % { Remove-Item $_.FullName -recurse } #Remove the -whatif to actual clean out the directory
    }
}

Best Answer

I see multiple things that needs attention in your file:

  • $OutXML = ForEach-Object { $GetXML } | Select LastWriteTime,Fullname,DirectoryName #Foreach loop is unnecessary. $OutXML = $GetXML | Select ... works

  • New-Item -Path $Path -ItemType Directory #ItemType should be File

  • Your XML-editing looks wrong. You ask for every group, but each time you try to edit the whole file xmlData

There may be more, those were just the ones that caught my eye early.

To answer you question, look at the code below. It should give an idea of how to solve it. If filepath is the only thing you store in the log, you should use a pure text-file instead because it's faster and easier. I've included solutions for text-file and csv below. Since you didn't provide csv-headers/sample, the solution is made for single-column csv(I don't know how you want to save it back to csv).

#Load from csv
$log = Import-Csv .\test.csv
$loggedpaths = $log | Select-Object -ExpandProperty FullName

#Get only untouched files
$GetXML = @(Get-ChildItem $sourceDir -recurse -filter "Metadata.xml" | Where-Object {$_.FullName -like "*disney*" -and $loggedpaths -notcontains $_.FullName})

#Add processed file after editing(after $xmldata.save($file.fullname)
$log += $file | Select-Object LastWriteTime, Fullname, DirectoryName

#Save log to csv
$log | Export-Csv .\test.csv -NoTypeInformation
Related Topic