CODEWITHSUNDEEP
powershellcsv
anyplane
anyplane

Reputation: 77

How to add a column to an existing CSV row in PowerShell?

I'm trying to write a simple usage logger into my script that would store information about the time when user opened the script, finished using the script and the user name.

The first part of the logger where I gather the first two data works fine and adds two necessary columns with values to the CSV file. Yet when I run the second part of the logger it does not add a new column to my existing CSV file.

#Code I will add at the very beginning of my script
$FileNameDate = Get-Date -Format "MMM_yyyy"
$FilePath = "C:\Users\Username\Desktop\Script\Logs\${FileNameDate}_MonthlyLog.csv"
$TimeStamp = (Get-Date).toString("dd/MMM/yyyy HH:mm:ss")
$UserName = [string]($env:UserName)
$LogArray = @()
$LogArrayDetails = @{
    Username = $UserName
    StartDate = $TimeStamp
}
$LogArray += New-Object PSObject -Property $LogArrayDetails | Export-Csv $FilePath -Notypeinformation -Append
#Code I will add at the very end of my script
$logArrayFinishDetails = @{FinishDate = $TimeStamp}
$LogCsv = Import-Csv $FilePath | Select Username, StartDate, @{$LogArrayFinishDetails} | Export-Csv $FilePath -NoTypeInformation -Append

CSV file should look like this when the script is closed:

Username    StartDate               FinishDate
anyplane    08/Apr/2018 23:47:55    08/Apr/2018 23:48:55

Yet it looks like this:

StartDate               Username                
08/Apr/2018 23:47:55    anyplane

The other weird thing is that it puts the StartDate first while I clearly stated in $LogArrayDetails that Username goes first.

Upvotes: 1

Views: 6696

Answers (2)

mklement0
mklement0

Reputation: 440431

Assuming that you only ever want to record the most recent run [see bottom if you want to record multiple runs] (PSv3+):

# Log start of execution.
[pscustomobject] @{ Username = $env:USERNAME; StartDate = $TimeStamp } |
  Export-Csv -Notypeinformation $FilePath

# Perform script actions...

# Log end of execution.
(Import-Csv $FilePath) | 
  Select-Object *, @{ n='FinishDate'; e={ (Get-Date).toString("dd/MMM/yyyy HH:mm:ss") } } |
    Export-Csv -Notypeinformation $FilePath

  • As noted in boxdog's helpful answer, using -Append with Export-Csv won't add additional columns.

  • However, since you're seemingly attempting to rewrite the entire file, there is no need to use
    -Append at all.

    • So as to ensure that the old version of the file has been read in full before you attempt to replace it with Export-Csv, be sure to enclose your Import-Csv $FilePath call in (...), however.
      This is not strictly necessary with a 1-line file such as in this case, but a good habit to form for such rewrites; do note that this approach is somewhat brittle in general, as something could go wrong while rewriting the file, resulting in potential data loss.

    • @{ n='FinishDate'; e={ (Get-Date).toString("dd/MMM/yyyy HH:mm:ss") } is an example of a calculated property/column that is appended to the preexisting columns (*)

The other weird thing is that it puts the StartDate first while I clearly stated in $LogArrayDetails that Username goes first.

You've used a hashtable (@{ ... }) to declare the columns for the output CSV, but the order in which a hashtable's entries are enumerated is not guaranteed.

In PSv3+, you can use an ordered hashtable instead ([ordered] @{ ... }) to achieve predictable enumeration, which you also get if you convert the hashtable to a custom object by casting to [pscustomobject], as shown above.

If you do want to append to the existing file, you can use the following, but note that:

  • this approach does not scale well, because the entire log file is read into memory every time (and converted to objects), though limiting the entries to a month's worth should be fine.

  • as stated, the approach is brittle, as things can go wrong while rewriting the file; consider simply writing 2 rows per execution instead, which allows you to append to the file line by line.

  • there's no concurrency management, so the assumption is that only ever one instance of the script is run at a time.

$FilePath = './t.csv'
$TimeStamp = (Get-Date).toString("dd/MMM/yyyy HH:mm:ss")
$env:USERNAME = $env:USER

# Log start of execution. Note the empty 'FinishDate' property
# to ensure all rows ultimately have the same column structure.
[pscustomobject] @{ Username = $env:USERNAME; StartDate = $TimeStamp; FinishDate = '' } |
  Export-Csv -Notypeinformation -Append $FilePath

# Perform script actions...

# Log end of execution:
# Read the entire existing file...
$logRows = Import-Csv $FilePath
# ... update the last row's .FinishDate property
$logRows[-1].FinishDate = (Get-Date).toString("dd/MMM/yyyy HH:mm:ss")
# ... and rewrite the entire file, keeping only the last 30 entries
$logRows[-30..-1] | Export-Csv -Notypeinformation $FilePath

Upvotes: 3

boxdog
boxdog

Reputation: 8442

Because your CSV already has a structure (i.e. defined headers), PowerShell honours this when appending and doesn't add additional columns. It is (sort of) explained in this excerpt from the Export-Csv help:

When you submit multiple objects to Export-CSV, Export-CSV organizes the file based on the properties of the first object that you submit. If the remaining objects do not have one of the specified properties, the property value of that object is null, as represented by two consecutive commas. If the remaining objects have additional properties, those property values are not included in the file.

You could include the FinishDate property in the original file (even though it would be empty), but the best option might be to export your output to a different CSV at the end, perhaps deleting the original after import then recreating it with the additional data. In fact, just removing the -Append will likely give the result you want.

Upvotes: 3

Related Questions