9

I have this CSV file.

The actual CSV data is delimited with a ";" , like this:

[2017-04-27 15:45:04] ;x;/x;Succes;

You can see there is a column "Terminaison" which contains success or failure information. My goal is to display all columns of the file in a Gridview.

I'm doing : 

import-csv "C:\x\Journal\Capsule\CapsulePoste.csv" -Delimiter ";"

and get the desired output with all columns containing the proper data:

Now here is my question: when I want to display all that in a GridView, the "terminaison" column is all empty? Why? All other columns are properly displayed... :

import-csv "C:\x\Journal\Capsule\CapsulePoste.csv" -Delimiter ";" | out-gridview

I've found that a blank space at the end of the header (first line of csv file) is causing this...

Date;Trousse;Version;Demandeur;Action;Paramètres;Terminaison

(there is a blank space right there after "Terminaison")

If i edit the csv in Notepad and remove that blank space, bingo, it works. However that doesn't solve my problem as I don't want to edit the file before-hand. What is this strange limitation and is there a workaround?

EDIT:

Answers provided below are great. I ended up using another option which i find is worth adding to the possibilities :

$content = Get-Content C:\x\Journal\Capsule\CapsulePoste.csv 
$content | Foreach {$_.TrimEnd()} | Set-Content C:\x\Journal\Capsule\CapsulePoste.csv
import-csv "C:\x\Journal\Capsule\CapsulePoste.csv" -Delimiter ";"| sort-object Date -descending
Rakha
  • 1,874
  • 3
  • 26
  • 60
  • 3
    Is there a [whitespace character](https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/11718579-out-gridview-fails-to-display-data-for-properties) at the end of `Terminaison`? – BenH Jan 09 '18 at 15:07
  • BenH, yes there is, this is causing the problem. If i remove it, it works. However i don't want to have to edit the file everytime before opening it...Is this a bug in powershell ? The only other thing i've found is this : https://windowsserver.uservoice.com/forums/301869-powershell/suggestions/11718579-out-gridview-fails-to-display-data-for-properties – Rakha Jan 09 '18 at 15:12
  • In my case,i choose different columns from what is there in object :) – TheGameiswar Sep 23 '18 at 05:59
  • Issue raised for PWSH: https://github.com/PowerShell/PowerShell/issues/20195 – JohnLBevan Sep 01 '23 at 23:28

2 Answers2

6

As I mentioned in the comments, there is a bug in Out-GridView that displays blank values when there is a space or special character at the beginning or ending of a property. I don't know of a fix for whitespace bug, but you could fix the file before converting from csv like this:

(Get-Content "C:\X\Capsule\CapsulePoste.csv") -replace 'Terminaison ','Terminaison' |
    ConvertFrom-CSV -Delimiter ";" |
    Out-GridView

Note: As Matt and Mark stated, Mark's answer is more generalized. This quick fix makes the assumption that Terminaison is header to fix and is found no where else in the document.

Rakha
  • 1,874
  • 3
  • 26
  • 60
BenH
  • 9,766
  • 1
  • 22
  • 35
  • 2
    You need to be careful with suggestions like this in case `Terminaison ` appears somewhere else. Not likely with this case but it is certainly possible. I would opt to just change the first line to be sure. – Matt Jan 09 '18 at 16:01
4

Here's a generic way to fix it after you've imported the CSV:

$CSV = import-csv "C:\x\Capsule\CapsulePoste.csv" -Delimiter ";"

$FixedObject = $CSV | ForEach-Object {
    $NewObject = New-Object -TypeName PSCustomObject
    $_.PSObject.Properties | ForEach-Object { $NewObject | Add-Member -Name $_.Name.Trim() -Value $_.Value -MemberType $_.MemberType }

    $NewObject
}

$FixedObject | Out-GridView

This iterates through the properties of each of the resultant object and then creates a new object with those same properties but with the .Trim() method run on the name of each to remove any surrounding whitespace.

This is obviously less efficient than just fixing the CSV file before you import, but doing that (generically) could also be tricky if you can't be certain where in the CSV the headerline appears (as it's not always the first).

Of course if you just want to fix this one specific scenario, the other answer is simplest.

Rakha
  • 1,874
  • 3
  • 26
  • 60
Mark Wragg
  • 22,105
  • 7
  • 39
  • 68
  • Good technique there but the problem is when you out-gridview you end up with only one result in the grid. I would need to build some kind of hash table to add them all as it loops during the for-each maybe? – Rakha Jan 09 '18 at 16:23
  • Ah yeah, poor testing, we just need to combine the object outputs. I will fix. – Mark Wragg Jan 09 '18 at 16:23
  • That should export all the rows now. You could also not do `$FixedObject = ` and instead put the `| Out-GridView` on the end of the `ForEach-Object`. – Mark Wragg Jan 09 '18 at 16:25
  • Ah, i should've seen the difference myself! Still new to powershell so I miss these obvious things sometimes. Thank you for your help, choosing this as best answer. It's efficient enough considering the file isn't too large. Much appreciated – Rakha Jan 09 '18 at 16:38
  • What if i want to sort-object on the property "date", does it work with a custom object like this? Can't seem to make it happen – Rakha Jan 09 '18 at 16:42
  • 1
    It should in theory as the object should still be a date object if it was to start with. Did it sort by date without the custom object fix? My guess is because of the square brackets around them PowerShell treats them as strings. – Mark Wragg Jan 09 '18 at 18:20
  • Mark, thanks. I was sorting at the end when i output the result to gridview while i had to sort right on the same line as Import-CSV. That's why it wasn't working. It sorts fine even with the brackets. Thanks for everything! – Rakha Jan 09 '18 at 19:05
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162839/discussion-between-rakha-and-mark-wragg). – Rakha Jan 09 '18 at 19:09