Read from text file, change formatting, write to excel

I have a text file that reads as:

SAAS_1

IP: xxx.xxx.xxx.xxx
Port id: xxxx.xxxx.xxxx
Serial number: xxxxxxxxxxxxx
Model: xxxxx
SAAS_2

I want to break the data up into seperate colums in excel as:

SAAS_1 SAAS_1 SAAS_1 SAAS_1
IP:  Port id: Serial number: Model:     
SAAS_2 SAAS_2 SAAS_2 SAAS_2

One issue we're having is not all serial numbers are coming back with an IP. 

Is there a powershell script that can do this automatically?

Parents
  • We are here to help you with code that is not working, or that you are getting error with. I most cases, Q&A sites like this one frown on folks not at least providing some effort, vs asking the folks here to write your code for you. That later part does happen sometimes, but it’s really not a practice, and folks will push back on you.

    There are a lot of things that PowerShell tries to coerce for you, but string formatting, is often an effort you have to code for. When some object / string does not come back from a text file, then PowerShell will not magically go grab or do anything to that for you. It's just empty, and you have to deal with that directly. But if you are saying, that what you get back may not always have at least a label, then that is a whole different issue and you show no example of that that would look like.

    If the # 4 is actually that the IP line is completely missing, then you have to have logic to add that back in or your columns can never be correct, and you'd just get errors every time that would show up.

    So, looking at your sample, it indicates your stuff is semi unstructured. I've made some assumptions (extending the sample a bit) below, guessing at what you are saying.


    SAAS_1

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_2

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_3

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_4

    IP:
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx

    Meaning, empty lines, random spaces, no line feed for subsequent items, etc., in otherwise structured layout.

    If you are on earlier versions of PowerShell, then it’s a bit harder. With PSv5 and above, you have more flexibility and other cmdlets. Specifically, regular Expression string parsing and converting string to objects.

    This string parsing, sort of thing is pretty common, and not without its challenges. Yet, there are lots of articles and samples all over the on how to do this. You just have to be willing to search for them. As per the below.

    Using the Split Method in PowerShell | Scripting
    https://devblogs.microsoft.com/scripting/using-the-split-method-in-powershell

    About join
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_join?view=powershell-6

    Delete all blank lines from a text file using PowerShell
    https://www.madwithpowershell.com/2013/08/delete-all-blank-lines-from-text-file.html

    PowerTip: Remove Leading and Trailing Spaces with PowerShell
    https://devblogs.microsoft.com/scripting/powertip-remove-leading-and-trailing-spaces-with-powershell

    ConvertFrom-String
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/convertfrom-string?view=powershell-5.1

    ConvertFrom-String: Example-based text parsing
    https://devblogs.microsoft.com/powershell/convertfrom-string-example-based-text-parsing

    Using the ConvertFrom-String cmdlet to parse structured text
    https://www.powershellmagazine.com/2014/09/09/using-the-convertfrom-string-cmdlet-to-parse-structured-text

    Out-String
    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/out-string?view=powershell-6

    PSCustomObject
    https://powershellexplained.com/2016-10-28-powershell-everything-you-wanted-to-know-about-pscustomobject/


    See also this video:

    6 Weltner Sophisitcated Techniques of Plain Text Parsing
    https://www.youtube.com/watch?v=Hkzd8spCfCU

    The output layout you are after ...

    SAAS_1 SAAS_1 SAAS_1 SAAS_1      
    IP:  Port id: Serial number: Model:          
    SAAS_2 SAAS_2 SAAS_2 SAAS_2

    ... is really a bit odd from a usability perspective relative to dealing with a structured object in PowerShell. I’d suggest this way

    Code   IP:              Port id:        Serial number:  Model:
    SAAS_1 xxx.xxx.xxx.xxx  xxxx.xxxx.xxxx  xxxxxxxxxxxxx   xxxxx  
    SAAS_2 xxx.xxx.xxx.xxx  xxxx.xxxx.xxxx  xxxxxxxxxxxxx   xxxxx

    In any case, you have to turn that text file data in to an object you can work with to get either your output, or my suggested one. Again, see that YouTube video.

    As I stated earlier. May will push back on you by not showing any work you've done, but at times, folks will step up out of the kindness of their hearts. Yet, remember, be more cognizant of what you are asking of folks. People are willing to help, but not really willing to do the work for you. Here is a sample I whipped up based on what you stated thus far and assumptions I've made in context. There are many ways to solve a given task. It's all a matter of what you are comfortable with or want to be.

    # Text file content

    SAAS_1

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_2

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_3

    IP: xxx.xxx.xxx.xxx
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx
    SAAS_4

    IP:
    Port id: xxxx.xxxx.xxxx
     Serial number: xxxxxxxxxxxxx
     Model: xxxxx

    ### Begin example script
    #
    Clear-Host
    $pattern = '*_*' 
    $content = Get-Content E:\temp\saas.txt | Out-String
    $content.Split($pattern,[System.StringSplitOptions]::RemoveEmptyEntries) |
    Where-Object {$_ -match '\S'} |
    ForEach-Object {

    $item = $_ -split "\s+`n" | Where-Object {$_}

        New-Object PSobject -Property @{
            Code         = 'SSAS_' + $item[0].Split(':')[-1].Trim()
            IP           = $item[1].Split(':')[-1].Trim()
            PortID       = $item[2].Split(':')[-1].Trim()
            SerialNumber = $item[3].Split(':')[-1].Trim()
            Model        = $item[4].Split(':')[-1].Trim()
        } | Select-Object -Property Code, IP, PortID, SerialNumber, Model
    } | Format-Table -AutoSize

    #
    ### End example script

    or this way ...

    (Get-Content 'E:\temp\saas.txt') -replace ' ' |
    Select-String -Pattern 'SAAS' -Context 0,5 |
    ConvertFrom-String -PropertyNames RecordMarker, Code, IP, PortID, SerialNumber, Model |
    Select-Object -Property Code,
    @{Name = 'IP';Expression = {$PSItem.IP -replace '.*:'}},
    @{Name = 'PortID';Expression = {$PSItem.PortID -replace '.*:'}},
    @{Name = 'SerialNumber';Expression = {$PSItem.SerialNumber -replace '.*:'}},
    @{Name = 'Model';Expression = {$PSItem.Model -replace '.*:'}} |
    Format-Table -AutoSize

    Or this way... using the ConvertFrom-String template approach.

    $SAASDataTemplate = @'
    {Code*:SAAS_3}

    {IP:xxx.xxx.xxx.xxx}
    {Portid:xxxx.xxxx.xxxx}
    {Serialnumber:xxxxxxxxxxxxx}
    {Model:xxxxx}
    {Code*:SAAS_4}

    {IP:' '}
    {Portid:xxxx.xxxx.xxxx}
    {Serialnumber:xxxxxxxxxxxxx}
    {Model:xxxxx}
    '@

    (Get-Content 'E:\temp\saas.txt') -replace '.*: ' |
    ConvertFrom-String -TemplateContent $SAASDataTemplate |
    Format-Table -AutoSize

    # Results using one of the above is this, "some of the above is OS and PowerShell version specific."

    Code       IP                      PortID               SerialNumber   Model
    ----           --                       ------                   ------------          -----
    SSAS_1  xxx.xxx.xxx.xxx xxxx.xxxx.xxxx xxxxxxxxxxxxx xxxxx
    SSAS_2  xxx.xxx.xxx.xxx xxxx.xxxx.xxxx xxxxxxxxxxxxx xxxxx
    SSAS_3  xxx.xxx.xxx.xxx xxxx.xxxx.xxxx xxxxxxxxxxxxx xxxxx
    SSAS_4                           xxxx.xxxx.xxxx xxxxxxxxxxxxx xxxxx

    Export this above output to a csv, then Excel can read it. I'll leave that part for you to figure out.

Reply Children
No Data