Modifying Existing CSV file in Powershell

Question

I have a script that I am setting up to do some migration of users' Exchange mailboxes into .pst file. The idea was that I would have a CSV file that I could put users' names on and then when the script kicked off nightly it would open the CSV file and find users that have been added, perform the requested actions on those users accounts (export, move set permissions etc) and then write back to the CSV file Marking those users as completed and writing the date on which they were completed. Here is what I have so far.

$InPstPath = '\\server1\PST_Store\'
$OutPstPath = '\\server2\PST_Store\'
$User = Get-Content $OutPstPath'login.txt'
$PWord = cat $OutPstPath'pass.txt' | convertto-securestring
$Credentials = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $User, $PWord
$PSSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://Server1/powershell -Credential $Credentials
Import-PSSession $PSSession
$In_List = Invoke-Command {Import-Csv "\\Server1\PST_Store\Admin\To_Be_Exported.csv"} -computername Server1 -Credential $Credentials
foreach ($objUser in $In_List) {
    if ($objUser.Completed -ne "Yes") {
        $TargetUser = $objUser.name
        $ShortDate = (Get-Date).toshortdatestring()
        New-MailboxExportRequest -Mailbox $TargetUser -Filepath "$InPstPath$TargetUser.pst"
        $objUser.Completed = "Yes"
        $objUser.Date = $ShortDate
        }
    }
Remove-PSSession -Session (Get-PSSession)

I can't figure out a decent way to write back the $objUser.Completed and $objUser.Date values to the CSV.

Is there an Export-Csv function that mirrors the Import-Csv you used? — Edward
– Edward, Commented Oct 7, 2013 at 20:00
There is. I am unsure how I would use it to only target the values that require changing. Most of what I have found of this command makes it seem like it functions in a similar way to 'Out-File C:\filename.csv | ConvertTo-Csv' — Trey Nuckolls
– Trey Nuckolls, Commented Oct 7, 2013 at 20:19
I'm guessing you'll need to overwrite the entire file with the new contents. You'll want to test and make sure that you get only the changes you made, but that seems like a reasonable approach to me. — Edward
– Edward, Commented Oct 7, 2013 at 22:02
The more I look into this the More I start to think I should just build a new custom object and define properties for it then build a table based on the information imported (where Completed = Yes) or the New information (when Completed = not yes). — Trey Nuckolls
– Trey Nuckolls, Commented Oct 7, 2013 at 22:03

Adil Hindistan · Accepted Answer · 2013-10-07 23:50:43Z

2

Firstly, it's obvious but let me state it anyway. The very first time you run this script, $objUser.name, $objUser.Completed and $objUser.Date will not exist; So, the line

$TargetUser=$objUser.name

will not work, unless you actually have the structure in place in that csv (i.e. have the headers name,completed,date).

Now assuming you got that part done, all you have to do is to create an object that captures the state in an object and then write that back.

$Processed = foreach ($objUser in $In_List) {
    if ($objUser.Completed -ne "Yes") {
        $TargetUser = $objUser.name
        $ShortDate = (Get-Date).toshortdatestring()
        New-MailboxExportRequest -Mailbox $TargetUser -Filepath "$InPstPath$TargetUser.pst"

        [PSCustomObject]@{Name=$objUser.name;Completed="Yes";Date=$ShortDate}
        }
    } else {
        [PSCustomObject]@{Name=$objUser.name;Completed="No";Date=$null}
    }
## export to a temp file
$Processed | export-csv -Path $env:TEMP\processed.csv
## You should probably check to see if original file was modified since you started working on it
# and copy over if not
Copy-Item $env:TEMP\processed.csv $OutPstPath'login.txt' -force

answered Oct 7, 2013 at 23:50

Adil Hindistan

6,7254 gold badges29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Trey Nuckolls Over a year ago

The initial CSV does have the required headers. If I write the data out to a new CSV I will need to take care to recreate the headers, though perhaps the method you are suggesting does that. This Is certainly a step in the right direction and I will give it a try tomorrow. I think perhaps the else statement should be... [PSCustomObject]@{Name=$objUser.name;Completed=objUser.Completed;Date=objUser.Date} As if it fails the -ne "Yes" then the user's account has already been processed. I am fairly new at this though so I could be misreading it.

Adil Hindistan Over a year ago

Yes, export-csv will create a csv object that you can later import again as it was. You could of course use 'yes', 'no' as you like for Completed but also could simply use boolean $true, $false, as it fits your purpose just fine. Unless explicitly assigned any boolean variable will be $false and you can use that to your advantage. if ($Completed) {"stuff if true" } else { "stuff if not" }

Trey Nuckolls Over a year ago

After some amount of messing with it I got this to go using your prescribed solution. Thanks! (I would up vote but apparently I don't have enough reputation to do so :P)

Collectives™ on Stack Overflow

Modifying Existing CSV file in Powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related