CODEWITHSUNDEEP
csvpowershell
Graham J
Graham J

Reputation: 497

Import CSV and updating specific lines

So I have a script that runs at logon to search for PST's on a users machine, then copies them to a holding area waiting for migration. When the search/copy is complete it outputs to a CSV that looks something like this:

Hostname,User,Path,Size_in_MB,Creation,Last_Access,Copied

COMP1,user1,\\comp1\c$\Test PST.pst,20.58752,08/12/2015,08/12/2015,Client copied
COMP1,user1,\\comp1\c$\outlook\outlook.pst,100,08/12/2015,15,12,2015,In Use

The same logon script has an IF to import the CSV if the copied status is in use and makes further attempts at copying the PST into the holding area. If it's successful it exports the results to the CSV file.

My question is, is there anyway of getting it to either amend the existing CSV changing the copy status? I can get it to add the new line to the end, but not update.

This is my 'try again' script:

# imports line of csv where PST file is found to be in use
$PST_IN_USE = Import-CSV "\\comp4\TEMPPST\PST\$HOSTNAME - $USER.csv" | where { $_.copied -eq "In Use" }

ForEach ( $PST_USE in $PST_IN_USE )
{ $NAME = Get-ItemProperty $PST_IN_USE.Path | select -ExpandProperty Name
$NEW_NAME = $USER + "_" + $PST_IN_USE.Size_in_MB + "_" + $NAME

# attempts to copy the file to the pst staging area then rename it.
TRY { Copy-Item $PST_IN_USE.Path "\\comp4\TEMPPST\PST\$USER" -ErrorAction SilentlyContinue
Rename-Item "\\comp4\TEMPPST\PST\$USER\$NAME" -NewName $NEW_NAME

# edits the existing csv file replacing "In Use" with "Client Copied"
$PST_IN_USE.Copied -replace "In Use","Client Copied"

} # CLOSES TRY

# silences any errors.
CATCH { }


$PST_IN_USE | Export-Csv "\\comp4\TEMPPST\PST\$HOSTNAME - $USER.csv" -NoClobber -NoTypeInformation -Append


} # CLOSES ForEach ( $PST_USE in $PST_IN_USE )

This is the resulting CSV

Hostname,User,Path,Size_in_MB,Creation,Last_Access,Copied
COMP1,user1,\\comp1\c$\Test PST.pst,20.58752,08/12/2015,08/12/2015,Client copied
COMP1,user1,\\comp1\c$\outlook\outlook.pst,100,08/12/2015,15,12,2015,In Use
COMP1,user1,\\comp1\c$\outlook\outlook.pst,100,08/12/2015,15,12,2015,Client copied

It's almost certainly something really simple, but if it is, it's something I've yet to come across in my scripting. I'm mostly working in IF / ELSE land at the moment!

Upvotes: 1

Views: 114

Answers (2)

Graham J
Graham J

Reputation: 497

I've cracked it. It's almost certainly a long winded way of doing it, but it works and is relatively clean too.

#imports line of csv where PST file is found to be in use
$PST_IN_USE = Import-CSV "\\comp4\TEMPPST\PST\$HOSTNAME - $USER.csv" | where { $_.copied -eq "In Use" }

$PST_IN_USE | select -ExpandProperty path | foreach { 

# name of pst
$NAME = Get-ItemProperty $_ | select -ExpandProperty Name

# size of pst in MB without decimals
$SIZE = Get-ItemProperty $_ | select -ExpandProperty length | foreach { $_ / 1000000 }

# path of pst
$PATH = $_

# new name of pst when copied to the destination
$NEW_NAME = $USER + "_" + $SIZE + "_" + $NAME

TRY { Copy-Item $_ "\\comp4\TEMPPST\PST\$USER" -ErrorAction SilentlyContinue

      TRY { Rename-Item "\\comp4\TEMPPST\PST\$USER\$NAME" -NewName $NEW_NAME -ErrorAction SilentlyContinue | Out-Null }

      CATCH { $NEW_NAME = "Duplicate exists" }

      $COPIED = "Client copied" }

CATCH { $COPIED = "In use" ; $NEW_NAME = " " }

$NEW_FILE = Test-Path "\\comp4\TEMPPST\PST\$HOSTNAME - $USER 4.csv"

IF ( $NEW_FILE -eq $FALSE )
   { "Hostname,User,Path,Size_in_MB,Creation,Last_Access,Copied,New_Name" | 
     Set-Content "\\lccfp1\TEMPPST\PST\$HOSTNAME - $USER 4.csv" }

"$HOSTNAME,$USER,$PATH,$SIZE,$CREATION,$LASTACCESS,$COPIED,$NEW_NAME" | 
Add-Content "\\comp4\TEMPPST\PST\$HOSTNAME - $USER 4.csv"

} # CLOSES FOREACH #

$a = Import-CSV "\\comp4\TEMPPST\PST\$HOSTNAME - $USER.csv" | where { $_.copied -ne "in use" }
$b = Import-Csv "\\comp4\TEMPPST\PST\$HOSTNAME - $USER 4.csv"

$a + $b | export-csv "\\comp4\TEMPPST\PST\$HOSTNAME - $USER 8.csv" -NoClobber -NoTypeInformation

Thanks for the help. Sometimes it takes a moments break and a large cup of coffee to see things a different way.

Upvotes: 0

Joey
Joey

Reputation: 354586

If you want to change the CSV file, you have to write it completely again, not just appending new lines. In your case this means:

# Get the data
$data = Import-Csv ...

# Get the 'In Use' entries
$inUse = $data | where Copied -eq 'In Use'

foreach ($x in $inUse) {
   ...
   $x.Copied = 'Client Copied'
}

# Write the file again
$data | Export-Csv ...

The point here is, you grab all the lines from the CSV, modify those that you process and then write the complete collection back to the file again.

Upvotes: 2

Related Questions