Delete files from file path against corresponding machine which are listed in a CSV

We have CSV files which list the hostname and filepath.

Our requirement is to delete the file mentioned in the path column from its corresponding host mentioned in the host column. We prefer to have a check before deletion to see if the file exists and log the output to a file whether the delete was successful or not. (If file was already missing, then the output should show file missing)

We prefer to use either a VB script or a powershell script to achieve this.

A sample CSV file is attached.

Please advise the best possible way to go about this as there is a huge number of files which are required to be removed.
qgmasterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob McCoyCommented:
Do the remote machines (HostA,  HostB, et al) have PowerShell remoting enabled?
0
qgmasterAuthor Commented:
No, they don't have Powershell remoting enabled.

We do have a central server from where we do management tasks, we can run the script that can connect to these machines remotely through SMB and remove these files.
0
Michael PfisterCommented:
See if this does the trick. C$ share of the raget computer mut be reachable
CSV with 2 colums: Computername,File
with path starting from C:, ie. MYCOMPUTER,\windows\temp\dummifile.log
$ToDoCsv = ".\your.csv" 
$ToDo = Import-Csv -Path $ToDoCsv

foreach($d in $$ToDo)
{
	$file2delete = "\\" + $d.Computername  + "\c$\" + $d.File
	
	try {
		if(Test-Path $file2delete) {
			Write-Host "Computer reachable & file exists ... deleting"
			Remove-Item $file2delete
			$deleted++
		}
		else 
		{
			$notfound++
		}
		catch {
			$unreachable++
		}
	}
	$count++
}

Write-Host "Files in csv: $count"
Write-Host "Deleted: $deleted"
Write-Host "File not found: $notfound"
Write-Host "Host unreachable: $unreachable"

Open in new window

0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

cantorisCommented:
Here's my effort:
Add column headers to your CSV of Computername,Path,Status
Leave the Status field column empty.
The Path column is expecting a full local path such as   c:\Folder\File.txt
# CSV Needs Header row - Computername,Path,Status
$csvPath = "C:\TheCsvFile.csv"
$csv = Import-Csv $csvPath
$csv | ForEach-Object {
    $computer = $_.Computername
    $path = $($_.Path) -replace [char]34,""
    $status = $_.Status
    $props = [ordered]@{
        "Computername" = $computer;
        "Path" = $path;
        "Status" = $status
    }
    $obj = New-Object -TypeName PSObject -Property $props
    Write-Host "Processing computer $($computer.ToUpper())..."
    if ($status -eq "") {
        if (Test-Connection -ComputerName $computer -Count 1 -Quiet) {
            $dollarShare = $path -replace "c:","c$"
            $uncPath = "\\$computer\$dollarShare"
            if (Test-Path -Path $uncPath) {
                $deleted = $true
                try {
                    Remove-Item -Path $uncPath -Force
                } catch {
                    $deleted = $false
                    $obj.Status = "Could not delete"
                    Write-Host " - failed to delete file."
                }
                if ($deleted) {
                    $obj.Status = "Deleted"
                    Write-Host " - removed file."
                }
            } else {
                $obj.Status = "Missing"
                Write-Host " - file not there."
            }
        } else {
            Write-Host " - offline."
        }
    } else {
        Write-Host " - already processed."
    }
    Write-Output $obj
} | Export-Csv -Path $csvPath -Force -NoTypeInformation

Open in new window


The script will go through the CSV and afterwards, the same CSV file will now have the status column filled in.  You can run it repeatedly so it can re-try the previously offline PCs.  Since the output is still the CSV, you could load it into Excel and filter by the Status column to see the info you want.
0
cantorisCommented:
I forgot to add that the Write-Host lines are just there to give you visual feedback.  Strip them all out if you want and then you could run it as a scheduled task from your central server until all PCs have been contacted.  You'll need PowerShell 3 or above as I used an [ordered] hashtable to keep the CSV file's columns in order.
0
qgmasterAuthor Commented:
Gerwin,
I did attach a sample file when I initiated the post, but seems it is not visible anymore.

Attached is the sample again.
Sample.csv
0
Michael PfisterCommented:
$ToDoCsv = ".\your.csv" 
$ToDo = Import-Csv -Path $ToDoCsv

foreach($d in $$ToDo)
{
	$file2delete = "\\" + $d.Host + "\" + $d.Path
	$file2delete = $file2delete -replace "c:","c$"
	try {
		if(Test-Path $file2delete) {
			Write-Host "Computer reachable & file exists ... deleting"
			Remove-Item $file2delete
			$deleted++
		}
		else 
		{
			$notfound++
		}
		catch {
			$unreachable++
		}
	}
	$count++
}

Write-Host "Files in csv: $count"
Write-Host "Deleted: $deleted"
Write-Host "File not found: $notfound"
Write-Host "Host unreachable: $unreachable"

Open in new window

0
cantorisCommented:
Updated to support other than C: and the column being called "Host" rather than "Computername":

# CSV Needs Header row - Host,Path,Status
$csvPath = "C:\TheCsvFile.csv"
$csv = Import-Csv $csvPath
$csv | ForEach-Object {
    $computer = $_.Host
    $path = $($_.Path) -replace [char]34,""
    $status = $_.Status
    $props = [ordered]@{
        "Host" = $computer;
        "Path" = $path;
        "Status" = $status
    }
    $obj = New-Object -TypeName PSObject -Property $props
    Write-Host "Processing computer $($computer.ToUpper())..."
    if ($status -eq "") {
        if (Test-Connection -ComputerName $computer -Count 1 -Quiet) {
            $dollarShare = $path -replace ":","$"
            $uncPath = "\\$computer\$dollarShare"
            if (Test-Path -Path $uncPath) {
                $deleted = $true
                try {
                    Remove-Item -Path $uncPath -Force
                } catch {
                    $deleted = $false
                    $obj.Status = "Could not delete"
                    Write-Host " - failed to delete file."
                }
                if ($deleted) {
                    $obj.Status = "Deleted"
                    Write-Host " - removed file."
                }
            } else {
                $obj.Status = "Missing"
                Write-Host " - file not there."
            }
        } else {
            Write-Host " - offline."
        }
    } else {
        Write-Host " - already processed."
    }
    Write-Output $obj
} | Export-Csv -Path $csvPath -Force -NoTypeInformation

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qgmasterAuthor Commented:
Superb solution. Very impressed, thanks a lot.
0
cantorisCommented:
Thanks!  Glad you liked it so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows 7

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.