none
Powershell (Replacing White Spaces with a comma) RRS feed

  • Question

  • The object property values output tends to be tabular with varying white spaces between each column. I’m looking for a script that will allow me to:

    1. Output the object property values to a file
    2. Remove all white spaces encountered (regardless of the number of spaces)
    3. Replace the spaces with one (1) comma

    Replace isn’t the answer unless I literally define each instance of spaces and I can’t find any discernable documentation on using Regex…

    -- formattting the output doesn't help either...

    Any guidance / help would be appreciated

    Roderick



    • Edited by NPhinite Friday, May 2, 2014 2:46 PM
    Friday, May 2, 2014 2:29 PM

Answers

  • No need to do any string manipulation here.  That's PowerShell's default table output, but you can just as easily generate CSV style output.  For example:

    Get-ClusterResource  -Cluster ClusterName |
    Where-Object {$_.Name -like "SQL Server (*"} |
    ConvertTo-Csv -NoTypeInformation

    Friday, May 2, 2014 4:31 PM

All replies

  • What object? What property values? What does "tends to be tabular" mean? What columns?

    We can't read your mind, and we can't see your screen. You will need to give more information.

    Read these guidelines first:


    -- Bill Stewart [Bill_Stewart]

    Friday, May 2, 2014 2:39 PM
    Moderator
  • Let's say I ran the PowerShell command below:

    Get-ClusterResource  -Cluster ClusterName | Where-Object {$_.Name -like "SQL Server (*"}

    The output would be something along lines of what you see below.

    I typically output the result set to a csv file, strip the header, replace the spaces with a single comma, and bulk import the data into a SQL Server table for reporting.

    Basically, I'd like a "dynamic way" (not replace with the literal space count between double quotes) of getting rid of the spaces between the columns and replace the spaces with a single comma.

    Name                                        State           Group             ResourceType                              
    ----                                            -----               -----                 ------------                              
    SQL Server (SRV101O)            Online           SRV101o            SQL Server                                
    SQL Server (SRV301O)            Online           SRV301o            SQL Server                                
    SQL Server (SRV201O)            Online           SRV201o            SQL Server                                
    SQL Server (REMSRV101O)      Offline           REMSRV101o     SQL Server                                
    SQL Server (REMSRV301O)      Offline           REMSRV301o     SQL Server                                
    SQL Server (REMSRV201O)      Offline           REMSRV201o     SQL Server                                
    SQL Server (REMSRV401O)      Offline           REMSRV401O     SQL Server                                
    SQL Server (REMSRV501O)      Offline           REMSRV501O     SQL Server                                
    SQL Server (SRV401O)             Online          SRV401O            SQL Server                                
    SQL Server (SRV501O)             Online          SRV501O            SQL Server                                

    Friday, May 2, 2014 4:18 PM
  • No need to do any string manipulation here.  That's PowerShell's default table output, but you can just as easily generate CSV style output.  For example:

    Get-ClusterResource  -Cluster ClusterName |
    Where-Object {$_.Name -like "SQL Server (*"} |
    ConvertTo-Csv -NoTypeInformation

    Friday, May 2, 2014 4:31 PM
  • I might be missing what you're trying to accomplish, but couldn't you use trim and then concatenate the comma?

    $text = "SomeSortOfString " $text.trim() + ","

    SomeSortOfString,


    Friday, May 2, 2014 4:31 PM
  • I was going to say what David Wyatt posted: If you want object properties, just get them. If you want CSV output, then generate it. You don't need to do any string manipulation.


    -- Bill Stewart [Bill_Stewart]

    Friday, May 2, 2014 4:36 PM
    Moderator
  • Hi,

    this sounds like you are (shortened) doing something like this:
    Get-ClusterResource >> File.csv
    instead of the more useful
    Get-ClusterResoource | Export-Csv File.csv

    I recommend reading the help comments - especially the available parameters - for Export-Csv.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Friday, May 2, 2014 4:36 PM
  • Get-ClusterResource  -Cluster INTELCLU02O  |
    Where-Object {$_.Name -like "SQL Server (*"} |
    ConvertTo-Csv -NoTypeInformation

    Adds all property values and ignores the $_.Name filter

    Friday, May 2, 2014 4:48 PM
  • not a text string... property object output (default output is in table format )
    Friday, May 2, 2014 4:52 PM
  • Get-ClusterResource  -Cluster INTELCLU02O  |
    Where-Object {$_.Name -like "SQL Server (*"} |
    ConvertTo-Csv -NoTypeInformation

    Adds all property values and ignores the $_.Name filter

    Where is wrong:

    Where-Object {$_.Name -match 'SQL Server'}

    Should work better.

    This will customize:

    Get-ClusterResource  -Cluster INTELCLU02O  |
         Where-Object {$_.Name -match 'SQL Server'} |
         Select name, date, time, other |
         ConvertTo-Csv -NoTypeInformation

    It would be to your advantage to actually sit down and try to learn PowerShell.


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, May 2, 2014 5:06 PM
    • Proposed as answer by jrv Friday, May 2, 2014 5:19 PM
    Friday, May 2, 2014 5:05 PM
  • not a text string... property object output (default output is in table format )

    Default output is a collection of table formatter objects.  You need to use a formatter or converter.  Export-Csv converts objects to CSV.   CSV is a single comma and is consumable by all other comma delimited utilities.


    ¯\_(ツ)_/¯

    Friday, May 2, 2014 5:08 PM
  • exactly... thanks for your help!
    Friday, May 2, 2014 5:15 PM
  • exactly... thanks for your help!

    "exactly"?  Exactly what? 

    ¯\_(ツ)_/¯

    Friday, May 2, 2014 5:18 PM
  • .
    • Edited by NPhinite Friday, May 2, 2014 5:25 PM
    Friday, May 2, 2014 5:23 PM