Powershell to convert files from one location to another

[penfold]

I'm using a script to convert old xls files into xlsx which does so and then moves the old files into a subfolder Foldername\old. This works but what I want to be able to do is convert the files in 1 location and have it save it in another.

 

Basically we have moved the files from 1 drive to another (mirrored structure) so I just want to be able to convert from drive X:\Path to Y:\Path.

 

The code I'm using is

 

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $false
$folderpath = "D:\Excel Docs"
$filetype ="*xls"
Get-ChildItem -Path ($folderpath) -Include $filetype -recurse | 
ForEach-Object `
{
   $path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
   write-host $path


   "Converting $path"
   $workbook = $excel.workbooks.open($_.fullname)

   $path += ".xlsx"
   $workbook.saveas($path, $xlFixedFormat)
   $workbook.close()
   
   $oldFolder = $path.substring(0, $path.lastIndexOf("\")) + "\old"
   


   write-host $oldFolder
  # write-host $newFolder


   if(-not (test-path $oldFolder))
   {
       new-item $oldFolder -type directory
   }
   
   move-item $_.fullname $oldFolder
   
}
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

 

I'm having trouble seeing where I can split the source and destination easily so I can still run through the sub-folders to catch all the xls files. The easy way I can think of is to run the converter script and then use a separate script to move all .xlsx files but I would like to keep it in 1 script if possible.

 

Any suggestions?

[halbaradkenafin]

You should just be able to add on something like this to the end of your current script:

 

Get-ChildItem -Path $Folderpath -Include "xlsx" -Recurse | Foreach { Move-Item -Path $_.FullName -Destination "\\path\to\destination" -Force -Confirm:$False}

[penfold]

But how do I set the path to estination? I basically want it to copy

X:\folder 1

X:\folder 2\subfolder1

 

to

Y:\folder 1

Y:\folder 2\subfolder1

 

If I enter it into the code you posted it is then hard coded and it copies all the files into the same folder.

[halbaradkenafin]

In which case you just want something like:

 

Move-Item -Path "Top level folder" -Destination "Top level destination" -Confirm:$False

 

If you want them to be in both places then Copy-Item does that job and would need the -Recurse option adding. Both accept an -Include option but I'm not sure how that will work with a folder structure as I've never tested it.

[penfold]

What I've ended up doing is adding a line to copy files with ".xlsx" extention to the destination folder recursively and then added a second line to remove any files with the "xlsx" extention as it doesn't look like it's possible to use the move method recursively.

[ascott2]

I haven't tested this but wouldn't this work?

 

Get-ChildItem -Path $Folderpath -Include "xlsx" -Recurse | Foreach { Move-Item -Path $_.FullName -Destination ($_.FullName -Replace ("X:","Y:")) -Force -Confirm:$False}

[penfold]

For now I've stopped trying to create an all in one script as I've hit another snag which is when I run the conversion it fails against long file names. What I need to do is have an output for the script so it records when the conversion has failed. I've tried the command Export-csv -path "xyz" but I can't seem to get the information I need. How can I put an error handling condition in there so that if the script errors it will output the file name and full path so that I can do it manually?

[halbaradkenafin]

Look up Try/Catch blocks, they should deal with this for you. Though you might need to specify -ErrorAction (or some similar parameter) for the block to work correctly with some commands.

[Garacesh]

@penfold, I've just thrown this together* and tested it as best I can, it ought to work. Note it will not keep folder structure, but should convert any and all .xls files it finds.

 

$SourceFolder = "[b][color="#FF0000"]P:\ROVIDE\ROOT\FOLDER[/color][/b]" # Does not require final \
$DestinationFolder = "[color="#FF0000"][b]P:\ROVIDE\DESTINATION\FOLDER\[/b][/color]" # Must include final \
if (!(Test-Path $DestinationFolder)) {
New-Item $DestinationFolder -type directory
}
ForEach ($Workbook in (Get-ChildItem -Path $SourceFolder -Include "*.xls" -Recurse)) {
$MSXL = New-Object -ComObject excel.application
$MSXL.Visible = $false
	$WorkbookTypes = Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel' -Passthru
	$WorkbookSaveFormat = $WorkbookTypes | Where {$_.Name -eq "xlSaveFormat"}	
	$OpenWorkbook = $MSXL.Workbooks.Open("$workbook", 2, $true)
	$Filename = ($DestinationFolder + ($Workbook.name -replace '\.xls', ''))
	$OpenWorkbook.SaveAs($Filename, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault)
Stop-Process -name "EXCEL" -force
$MSEXCEL = $Null	
if (!(Test-Path -path "$Filename.xlsx")) {
	"FAILURE: $Workbook" | Out-File -Append "$DestinationFolder\Failed.txt"
}
}

 

* I say 'thrown this together', what I really mean is 'spent an hour and a half adapting it from an automatic Powerpoint to JPG script I've coded in the past'

 

This makes Excel (invisibly) open all the .xls files from your root and all subfolders, saving them as .xlsx (actually Save As, rather than just renaming the file extension) - if you want to watch it happening, just change $MSXL.Visible = $false to $true

 

Edit: Whoops. Changed the error handling. Made a boo-boo. All is fixed now.

Edited March 25, 2015 by Garacesh

[penfold]

Thanks for all the feedback. I think I have managed to get more or less what I was after. What the script should now do is convert each file from "xls" to "xlsx" and then move the old file (xls) to an archive folder while also keeping the structure. This works recursively. What I've also tried to do is put an error catch on it so it outputs any file it fails on to a csv file (*this bit needs testing as so far it has worked on pretty much all files it has found* - if anyone spots a problem with it feel free to let me know so I can update it properly)

 

Code below Note: The script has been hard coded to work with drive letters V & U

Add-Type -AssemblyName Microsoft.Office.Interop.Excel


$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $false
$folderpath = "[b]FullSourcePath[/b]"




$filetype ="*xls"
try
{
Get-ChildItem -Path ($folderpath) -Include $filetype -recurse |
ForEach-Object `
{
$path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))

"Converting $path"

$workbook = $excel.workbooks.open($_.fullname)

$path += ".xlsx"
$workbook.saveas($path, $xlFixedFormat) 
$workbook.close()


   $oldFolder = ($path.substring(0, $path.lastIndexOf("\")) -Replace ("V:","U:"))


#write-host "Archive folder $oldFolder"
if(-not (test-path $oldFolder))
   {
       new-item $oldFolder -type directory
   }


move-item $_.fullname $OldFolder


}
}
Catch [system.Exception]
{
"Failed: $path"  | Out-File -Append "D:\FailedXLSConversion.csv"
} 
$excel.Quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()

 

Now I just need to start applying it to live folders:)

 

Edit: Just tried this on my first "live" folder and the error catching doesn't quite work. It will log if you cancel the file conversion (file already exists) but if it fails on the FQDN being too long and can't access the file, nothing is recorded. I did want it to be recorded so I can manually go and file those files, but I might just leave them be and run it on everything that can be converted and anything else that is missed can be manually done by the user.

Edited March 26, 2015 by penfold

[Garacesh]

Nothing I would suggest is wrong, just double-check with the task manager your Excel process actually quits - When I was building the PowerPoint script I could get PowerPoint to close the currently open presentation, but not actually close the program (which is why I resorted to using Stop-Process -Force).

 

For the record (it may be oversight, it may not, just felt it worth pointing out) your script will move the .xls file to the archive even if it fails to copy, but your $path variable that is written out to the CSV is its old/starting directory (which no longer has the file in it).

Edited March 26, 2015 by Garacesh

[penfold]

@Garacesh - it was an oversight. However, have I used the correct error catch to be able to log this? Meaning I could then still find the file to manually convert (or restore) as the will the file will be in the same location in the Archive Directory?

[Garacesh]

@penfold, yes, the file that has failed to copy will be moved to your archive directory and you could find it and convert it manually. Just seemed inefficient that your error report would give you a location that the file was no longer in.

 

Imagine you get a ticket, "The phone in my classroom isn't working.", so you head to their classroom and when you get there they say "Oh, that phone? I have it to $Teacher in $Room. It's in there now." - well that's what you're doing with your error report

 

Edit: If you change your IF statement to be "If converted file exists, archive old file" that would fix it. You wouldn't even need the CSV then, as any files that were successfully converted would be moved, leaving only the failures in the original directory. I'm not a fan of using try/catch where I can help it because often it involves faffing around with EAP and only works if Powershell itself kicks out an error message AFAIK. If there's no error, it doesn't catch, even if what you want to do fails.

 

More edits: Including something like this inside your ForEach-Object loop should do it

if (!(Test-Path -Path $path)) {
"Failed: $_.Fullname"  | Out-File -Append "D:\FailedXLSConversion.csv"
} else {
move-item $_.fullname $OldFolder
}

Edited March 26, 2015 by Garacesh

[penfold]

@Garacesh - That's great. Just tried this on folder as I've had a request for a restore because the file is corrupt. When I've run the script it has converted all the files except for the corrupt one, but it has then recorded that in a csv so there is a log.

 

Great stuff. Thanks

[penfold]

OK, as good as the script is, it could be better so I'm back asking questions again. I've run this against numerous folders now and it works (which is nice) but if it can't open the file it suppliers a prompt to the user that it "can't open file - click OK to continue". At this point the powershell script waits for a user to click OK before carrying on.

 

Any chance there is a command which will auto carry on? The only concern would be that there were a couple of prompts for macros which needed to be updated (which I then cancelled) so it would need to be able to distinguish between the prompts. Am I asking a bit much? I can find some information about creating a prompt in powershell but this is an excel prompt when the file fails to open. Any one point me in the right direction?

[halbaradkenafin]

OK, as good as the script is, it could be better so I'm back asking questions again. I've run this against numerous folders now and it works (which is nice) but if it can't open the file it suppliers a prompt to the user that it "can't open file - click OK to continue". At this point the powershell script waits for a user to click OK before carrying on.

 

Any chance there is a command which will auto carry on? The only concern would be that there were a couple of prompts for macros which needed to be updated (which I then cancelled) so it would need to be able to distinguish between the prompts. Am I asking a bit much? I can find some information about creating a prompt in powershell but this is an excel prompt when the file fails to open. Any one point me in the right direction?

 

That sounds like something beyond the basic scope of Powershell. In theory you could use the -ErrorAction parameter but not all cmdlets support it correctly and I'm not sure how it will work if it's excel throwing the error rather than powershell (it would work if the file doesn't exist but probably not if the file is corrupt).

[penfold]

Yeah, I thought it would be a bit beyond what I could automate as from what I read it would be possible if Powershell generated the prompt, but as it looks like Excel I think I'm asking a bit much. It's not too bad as it does pretty much everything I want. I was just hoping to be able to run it overnight but not too worry. It does it far quicker than it would be done manually already

[Garacesh]

You could simulate keypresses, but you're getting into kludge territory, now.

 

$WShell = New-Object -ComObject wscript.shell;
$WShell.AppActivate('title of the application window')
Start-Sleep -Seconds 2
$WShell.SendKeys('~')

 

(~ is Keyboard-enter. Could also use {ENTER}, which is numpad enter.)

Full list of WScript keypresses.

 

Obviously keypress simulation will only work whilst you're idle because it can only interact with the active window, so it's not going to close the error off if you're in the middle of doing something.

Edited March 30, 2015 by Garacesh

[penfold]

I think I will leave it as is for now as I know it works and as you say it is going down the kludge route of making something work. I also need to do some restores while I am converting these files so there will have to be some manual interaction anyway.

 

Thanks for all the help everyone