I'm new at writing in powershell but this is what I'm trying to accomplish.

  1. I want to compare the dates of the two excel files to determine if one is newer than the other.
  2. I want to convert a file from csv to xls on a computer that doesn't have excel. Only if the statement above is true, the initial xls file was copied already.
  3. I want to copy the newly converted xls file to another location
  4. If the file is already open it will fail to copy so I want to send out an email alert on success or failure of this operation.

Here is the script that I'm having issues with. The error is "Expressions are only allowed as the first element of a pipeline." I know it's to do with the email operation but I'm at a loss as to how to write this out manually with all those variables included. There are probably more errors but I'm not seeing them now. Thanks for any help, I appreciate it!

$CSV = "C:filename.csv"
$LocalXLS = "C:\filename.xls"
$RemoteXLS = "D:\filename.xls"
$LocalDate = (Get-Item $LocalXLS).LASTWRITETIME
$RemoteDate = (Get-Item $RemoteXLS).LASTWRITETIME
$convert = "D:\CSV Converter\csvcnv.exe"
if ($LocalDate -eq $RemoteDate) {break}
else { 
& $convert $CSV $LocalXLS
$FromAddress = "email@address.com"
$ToAddress = "email@address.com"
$MessageSubject = "vague subject"
$SendingServer = "mail.mail.com"
$SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress, $MessageSubject, $MessageBody
$SMTPClient = New-Object System.Net.Mail.SMTPClient $SendingServer
$SendEmailSuccess = $MessageBody = "The copy completed successfully!" | New-Object System.Net.Mail.SMTPClient mail.mail.com $SMTPMessage
$RenamedXLS = {$_.BaseName+(Get-Date -f yyyy-MM-dd)+$_.Extension}

Rename-Item -path $RemoteXLS -newname $RenamedXLS -force -erroraction silentlycontinue
If (!$error)
	{ $SendEmailSuccess | copy-item $LocalXLS -destination $RemoteXLS -force }
Else
    {$MessageBody = "The copy failed, please make sure the file is closed." | $SMTPClient.Send($SMTPMessage)}
}	

You get this error when you are trying to execute an independent block of code from within a pipeline chain.

Just as a different example, imagine this code using jQuery:

$("div").not(".main").console.log(this)

Each dot (.) will chain the array into the next function. In the above function this breaks with console because it's not meant to have any values piped in. If we want to break from our chaining to execute some code (perhaps on objects in the chain - we can do so with each like this:

$("div").not(".main").each(function() {console.log(this)})

The solution is powershell is identical. If you want to run a script against each item in your chain individually, you can use ForEach-Object or it's alias (%).

Imagine you have the following function in Powershell:

$settings | ?{$_.Key -eq 'Environment' } | $_.Value = "Prod" 

The last line cannot be executed because it is a script, but we can fix that with ForEach like this:

$settings | ?{$_.Key -eq 'Environment' } | %{ $_.Value = "Prod" }