locked
Blank CSV columns and Concatenation RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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