?
Solved

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

Posted on 2014-09-09
12
Medium Priority
?
332 Views
Last Modified: 2014-09-16
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.
0
Comment
Question by:qgmaster
[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
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:Bob McCoy
ID: 40312850
Do the remote machines (HostA,  HostB, et al) have PowerShell remoting enabled?
0
 

Author Comment

by:qgmaster
ID: 40313709
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
 
LVL 29

Expert Comment

by:Michael Pfister
ID: 40322616
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 16

Expert Comment

by:cantoris
ID: 40322637
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
 
LVL 16

Expert Comment

by:cantoris
ID: 40322653
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
 

Author Comment

by:qgmaster
ID: 40323025
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
 
LVL 29

Expert Comment

by:Michael Pfister
ID: 40323046
$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
 
LVL 16

Accepted Solution

by:
cantoris earned 2000 total points
ID: 40323052
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
 

Author Closing Comment

by:qgmaster
ID: 40325041
Superb solution. Very impressed, thanks a lot.
0
 
LVL 16

Expert Comment

by:cantoris
ID: 40325093
Thanks!  Glad you liked it so much!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

800 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