looping through csv files located on remote share

PowerShell Help
1

So I’m working my way up the ranks as being the only powershell “guru” at my firm and while I can usually knock out scripts fairly quickly, this one request has stumped me. I suspect it is because I’m not understanding how to store csv files into an array and being able to call them when needed. So here’s my scenario:

We are running Exchange 2013, migrating away from IBM Domino (save the laughter), and I have been asked to replicate a process in where we have a scheduled task that updates the out of office for users when they leave the firm; for various reasons. The process entails a manager inputting information into a web page with all the necessary information and clicks submit. That process is completed and I have a nice csv file saved onto a file share for my use.

I had originally asked for the list of mailboxes in one csv file because that’s what I’m used to, but there is a need to keep what was submitted for each person for historical reasons. So I took on the challenge to see what I can come up with.

The short of it is, I can get the list of csv files, but it will not process the information within the files. I’ve ensured that the csv contains the relevant header information. I aslo found that if I copy the files locally the script works. While I can add a move-item to get those files onto the local machine, I’m curious to know what I am doing wrong, as this will come in handy for future requests. Here’s what I have:

$CSVSourceDir = "uncpathtoshare"

Get-ChildItem -path $CSVSourceDir -recurse -force | Where { $_.Name -like "*.csv" } | foreach-object {$filename=$_.userid;import-csv $filename | set-mailboxautoreplyconfiguration -identity $filename -autoreplystate Enabled -internalmessage $filename.internalmessage -externalmessage $filename.externalmessage -starttime $filename.startdate -endtime $filename.enddate -externalaudience All}

I had a conversation with a vbscript guy and he said it doesn’t look like I’m actually importing the values of the csv files in any way. Like I said before I had some snippets of code that tried using an array:

#for($i = 0;$i -le $csvarray.count -1;$i++){$csvarray[$i]}

Though I still couldn’t figure out how to grab the values I was looking for. Is there something I am missing that will allow me to grab those csv files from a remote share and be able to loop through each without having to bring them down to the local machine running the script?

Many thanks for any input.

Chuck

2

Hi!

I do suggest using the Filter parameter to the Get-ChildItem command, since it has a potential of performing a lot better than doing Where-Object to check every file manually.

Also, I’m not sure how your script is working at all, no matter if the file is local or not. For example, in your ForEach-Object statement, you start with doing '$filename=$.userid’ but the $ should be a FileInfo object, so it shouldn’t have a property called userid. Since your script works locally, I’m assuming this is just something that came from entering the script in the forum, but I make other assumptions incorrectly due to this, so look twice on any script I write here before actually using it. Of course, that’s always a good idea. :slight_smile: You might, however, want something like the following:

Assuming that the csv-file for a user with the identity ‘robwes’ would be similar to the following:

UserId,InternalMessage,ExternalMessage,StartDate,EndDate
robwes,Home for the holidays,Robert is away until 2013-12-26,2013-12-24T08:00:00,2013-12-25T17:00:00
robwes,Just not at work,Robert is away for the day,2014-01-10T08:00:00,2014-01-10T17:00:00

A script like the following might work:

Get-ChildItem -Path $CSVSourceDir -Recurse -Filter *.csv -Force |
Foreach {
$fileInfo = $_ #Since we’re foreaching the file list, the $_ will be a FileInfo object
Import-Csv $fileInfo.FullName |
Foreach {
$singleRowFromCsv = $_ #Here we’re foreaching the rows in the csv file
Set-MailBoxAutoReplyConfiguration -Identity $singleRowFromCsv.UserId -AutoReplyState Enabled
-InternalMessage $singleRowFromCsv.InternalMessage -ExternalMessage $singleRowFromCsv.ExternalMessage
-StartTime $singleRowFromCsv.StartDate -EndTime $singleRowFromCsv.EndDate
-ExternalAudience All
}
}

If you want to make it a reusable script, you might want to use the other Foreach syntax, to make it easier to read, e.g.:

$csvFiles = Get-ChildItem -Path $CSVSourceDir -Recurse -Filter *.csv -Force
foreach ($file in $csvFiles)
{
$outOfOfficeMessages = Import-Csv $file.FullName
foreach ($message in $outOfOfficeMessages)
{
Set-MailBoxAutoReplyConfiguration -Identity $message.UserId -AutoReplyState Enabled
-InternalMessage $message.InternalMessage -ExternalMessage $message.ExternalMessage
-StartTime $message.StartDate -EndTime $message.EndDate
-ExternalAudience All
}
}

If you want to try the commands without occuring changes, add the -WhatIf switch to the Set-MailBoxAutoReplyConfiguration command, to verify that your script works as you’re hoping it does.

Hope it helps!

/Robert

[edited for formatting issues, since the editor doesn’t seem to like uneven number of occurrences of the back-tick inside code]
[edited to clarify the file format assumptions]

3

Robert, as a note, the editor tends to hate back ticks altogether. It’s a Wordpress thing - they’re meant to be used to indicate lines of code, and that’s hardcoded fairly deeply where I can’t modify it without a hack. As you can see, if you even them up, it’ll figure it out, but it’s looking at them with a different meaning than PowerShell does. An alternative (and nicer to read) is to build your params in a hash table and then splat them into the cmdlet.

Thanks for that reply!