locked
Blank CSV columns and Concatenation RRS feed

  • Question

  • Hi,

    I'm concatenating 4 columns in a CSV into 1 single column and it's working very well.  The destination column (AddressCombined) is made up of the 4 source (Add1-1, Add1-2 etc..) columns but between each column I'm adding a comma.  

     #Import the CSV
     $kbc = Import-Csv $card
    
     #Concatanate the first 4 address fields (Too many)
     $kbc = $kbc | Select-Object -Property *, @{label = 'AddressCombined'; expression = {$_.'Add1-1' + ", " + $_.'Add1-2' + ", " + $_.'Add1-3' + ", " + $_.'Add1-4'}}


    The problem I have is that fields that don't have any text in end up in the AddressCombined, the destination column as just a comma and I can't have this.

    I need to do an if exists lookup on each of these expressions but this IF statement isn't working.  Is there a way to not add a comma if that cell is empty ?

     $kbc = $kbc | Select-Object -Property *, @{label = 'AddressCombined'; expression = {if($_.'Add1-1' + ", " + $_.'Add1-2' + ", " + $_.'Add1-3' + ", " + $_.'Add1-4')}}



    Alter De Ruine

    Tuesday, September 19, 2017 6:27 PM

Answers

  • OK, I've resolved it.  It's just a script block inside the expression and I'm checking if $_.Add1-1 is not NULL

    $kbc = $kbc | Select-Object -Property *, @{label = 'AddressCombined'; expression = {if($_.'Add1-1'){ $_.'Add1-1' + ", " + $_.'Add1-2' + ", " + $_.'Add1-3' + ", " + $_.'Add1-4'}}}


    Alter De Ruine

    • Marked as answer by James Bennett1 Wednesday, September 20, 2017 6:24 PM
    Wednesday, September 20, 2017 1:53 PM

All replies

  • Your computed column expression will have to test for blank or a single comma and return an empty string.


    \_(ツ)_/

    Tuesday, September 19, 2017 6:31 PM
  • Hi JVR,

    Thanks for this, do you have a little bit more detail on syntax.  Would it be a traditional If statement?  

    Sudo would be something like

    If $_. is Null dont add trailing comma.

    thanks


    Alter De Ruine

    Wednesday, September 20, 2017 10:37 AM
  • This will get you started with PowerShell:

     https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276<o:p></o:p>



    \_(ツ)_/

    Wednesday, September 20, 2017 11:00 AM
  • OK, I've resolved it.  It's just a script block inside the expression and I'm checking if $_.Add1-1 is not NULL

    $kbc = $kbc | Select-Object -Property *, @{label = 'AddressCombined'; expression = {if($_.'Add1-1'){ $_.'Add1-1' + ", " + $_.'Add1-2' + ", " + $_.'Add1-3' + ", " + $_.'Add1-4'}}}


    Alter De Ruine

    • Marked as answer by James Bennett1 Wednesday, September 20, 2017 6:24 PM
    Wednesday, September 20, 2017 1:53 PM