Solved

Need help copying foldered files to thier respective folder based on a txt or xls list

Posted on 2015-02-23
7
144 Views
Last Modified: 2015-02-24
I have a situation where I have a document management server (3.8 million documents stored in 1900 folders) that has lost a bunch of files (thousands). What I do have is a huge list pulled from SQL in xls format that can be converted to text of all the files that were unceremoniously deleted by the system. What I also have is a backup of all 1900 folder and 2.8 million docs including the thousands of files that were deleted. What I need is a script that will copy all the files listed in the xls or txt file back to their correct folders. Here is an example of the txt file. This is an example of the text file with the file list:

\\cp1\CP_HISTORY\CPWin\History\041208_0024\This document is gone.doc
\\cp1\CP_HISTORY\CPWin\History\041208_0045\abc456.doc
\\cp1\CP_HISTORY\CPWin\History\041245_0001\Our DM server sucks.doc...etc

This is actually the destination path on the production server. Notice the server and first 3 directory levels do not change but the last directory and filename changes with each file.

Here's the pathing I am looking at with the backup data:

\\backuppc\e$\Backup 2015-02-08\CP1\History\041208_0024\This document is gone.doc
\\backuppc\e$\Backup 2015-02-08\CP1\History\041208_0045\abc456.doc
\\backuppc\e$\Backup 2015-02-08\CP1\History\041245_0001\Our DM server sucks.doc...etc

What I can't figure out is how to get a script to read the txt file with each file and path (thinking batch file with FOR loop but after that I have no idea), use this path and file name which is the destination path and name to copy the file from the backup path to the production path .

Any help would be extremely appreciated. Thanks
0
Comment
Question by:gwbmcse
[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
7 Comments
 
LVL 85

Expert Comment

by:oBdA
ID: 40626187
Well, basically, a batch script could look like this (in test mode, it will only display the copy commands it would normally run; remove the uppercase ECHO in line 10 to run it for real). Whether this works depends mainly on the amount of files in the list, because "for /f" isn't really fast when it comes to large input files.
Make sure that the input file is saved as ANSI, NOT Unicode!
 
@echo off
setlocal enabledelayedexpansion
set FileList=C:\Temp\test.txt
set TargetRoot=\\cp1\CP_HISTORY\CPWin
set BackupRoot=\\backuppc\e$\Backup 2015-02-08\CP1
for /f "usebackq delims=" %%a in ("%FileList%") do (
	set Target=%%~a
	set Source=!Target:%TargetRoot%=%BackupRoot%!
	echo Processing '%%~a' ...
	ECHO copy "!Source!" "!Target!"
)

Open in new window

So just in case, here's a Powershell version; it's in test mode as well, remove the uppercase "-WHATIF" in line 9 to run it for real:
$FileList = "C:\Temp\test.txt"
$TargetRoot = "\\cp1\CP_HISTORY\CPWin"
$BackupRoot = "\\backuppc\e$\Backup 2015-02-08\CP1"
$TargetRoot = $TargetRoot.ToLower()
$BackupRoot = $BackupRoot.ToLower()
Get-Content -Path $FileList | % {
	"Processing '$($_)' ..." | Write-Host
	Try {
		Copy-Item -Path $_.ToLower().Replace($TargetRoot, $BackupRoot) -Destination $_ -Force -ErrorAction Stop -WHATIF
	} Catch {
		$_.Exception.Message | Write-Host -Fore Red
	}
}

Open in new window

0
 

Author Comment

by:gwbmcse
ID: 40626450
I tested it and it works but it's having trouble copying files with spaces in the filename. At least, that seems to be the common thread of the problem. Thanks for what you have done so far though, it works great for filename without a space.
0
 

Author Comment

by:gwbmcse
ID: 40626452
oh, I jumped directly to the powershell script. I didn't test the batch file.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 85

Expert Comment

by:oBdA
ID: 40626730
Neither the PS script nor the batch script should have issues with spaces in the file names, I tested with these kind of file names as well.
Could it be that the file names in the list have leading or trailing spaces?
Could it be that the "spaces" in the list aren't actually Char(32) / 0x20?
Are the file names in the list surrounded with quotes, and if so, with "pretty" ones, or the proper double quotes (as in Chr(34) / 0x22)?
If the lines in the file are enclosed in double quotes, use this for PS:
$FileList = "C:\Temp\test.txt"
$TargetRoot = "\\cp1\CP_HISTORY\CPWin"
$BackupRoot = "\\backuppc\e$\Backup 2015-02-08\CP1"
$TargetRoot = $TargetRoot.ToLower()
$BackupRoot = $BackupRoot.ToLower()
Get-Content -Path $FileList | % {
	"Processing '$($_)' ..." | Write-Host
	Try {
		Copy-Item -Path $_.Trim('"').ToLower().Replace($TargetRoot, $BackupRoot) -Destination $_.Trim('"') -Force -ErrorAction Stop -WHATIF
	} Catch {
		$_.Exception.Message | Write-Host -Fore Red
	}
}

Open in new window

0
 

Author Comment

by:gwbmcse
ID: 40626792
I did more testing and you're right, it's not the spaces. The problem is that all my doc names have a doc number inside brackets []...example doc name being: [1634] WCC Form 20.doc

If I remove the brackets, it works fine. Any workaround for the [ ]? Thanks again.
0
 
LVL 85

Accepted Solution

by:
oBdA earned 500 total points
ID: 40627505
Sorry, that should have been "-LiteralPath" in Copy-Item, not "-Path":
$FileList = "C:\Temp\test.txt"
$TargetRoot = "D:\Temp" # "\\cp1\CP_HISTORY\CPWin"
$BackupRoot = "C:\Temp" # "\\backuppc\e$\Backup 2015-02-08\CP1"
$TargetRoot = $TargetRoot.ToLower()
$BackupRoot = $BackupRoot.ToLower()
Get-Content -Path $FileList | % {
	"Processing '$($_)' ..." | Write-Host
	Try {
		Copy-Item -LiteralPath $_.Trim('"').ToLower().Replace($TargetRoot, $BackupRoot) -Destination $_.Trim('"') -Force -ErrorAction Stop -WHATIF
	} Catch {
		$_.Exception.Message | Write-Host -Fore Red
	}
}

Open in new window

0
 

Author Closing Comment

by:gwbmcse
ID: 40628913
Work's perfectly. My company should write you a check as you have saved hundreds of man hours. Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

717 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