Get-content and splitting string into two columns

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of Jeff Jeff 1 year, 5 months ago.

  • Author
    Posts
  • #32783
    Profile photo of Jeff
    Jeff
    Participant

    I need some help splitting a string into 2 separate columns. I am using get-content to retrieve the data. However, there are no headers in the files I am using. This is what the files look like:
    USER1 11/21/2013 13:59
    USER2 11/21/2013 14:07
    USER3 11/21/2013 14:10
    USER3 11/21/2013 14:13

    It is gathering the last logon times for users on a particular machine. I want to use get-content to retrieve the data and then turn it into an object with 2 columns, "User" and "Logon Date."

    I can retrieve the content without a problem, but it isn't formatted nicely. Ultimately, I will be piping the object to out-gridview:

    Get-Content \\SERVER\gpotest$\USERS\$Computer.txt | select -Last 10 | Out-GridView -OutputMode Multiple -Title "Logon for Last 5 Users"

    Any ideas how I can split each line into 2 columns based on either the space between the user and the date or splitting where an integer starts (the date)? Any help will be greatly appreciated.

  • #32784
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Personally, I'd use Import-Csv for this. Your file looks like a pretty well-behaved, space-delimited file (without a header row), and you can use Import-Csv to handle that like this:

    Import-Csv \\SERVER\gpotest$\USERS\$Computer.txt -Delimiter ' ' -Header 'User', 'Date', 'Time'
    
  • #32785
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    After that, you can use Select-Object to combine the Date / Time fields:

    Import-Csv \\SERVER\gpotest$\USERS\$Computer.txt -Delimiter ' ' -Header 'User', 'Date', 'Time' |
    Select-Object -Properties @(
        'User',
        @{ Name = 'LogonDate'; Expression = { "$($_.Date) $($_.Time)" } }
    )
    
  • #32786
    Profile photo of Jeff
    Jeff
    Participant

    Getting closer. However the code you put above only creates 2 columns. First "User" column includes the user and the date and the "LogonDate" column includes the time.

    User LogonDate
    —- ———
    USER1 09/07/2015 QC
    USER1 09/09/2015 20:23
    euctest1 09/10/2015 11:44
    euctest 09/10/2015 12:02
    euctest1 09/10/2015 12:07
    USER2 09/10/2015 16:12
    USER1 09/11/2015 QC
    USER1 09/15/2015 QC
    USER1 09/19/2015 QC
    USER3 11/09/2015 10:25

  • #32788
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Ah. I can't see it from your post here, but I suspect what you actually have is a tab-delimited file, which is even easier. Can just use Import-Csv without Select-Object, if that's the case:

    Import-Csv \\SERVER\gpotest$\USERS\$Computer.txt -Delimiter "`t" -Header 'User', 'LogonDate'
    
  • #32791
    Profile photo of Jeff
    Jeff
    Participant

    Yep! Figured that out before I saw your comment. Definitely pointed me in the right direction. Thanks for your help! The following works perfectly:

    Import-Csv \\SERVER\gpotest$\USERS\$Computer.txt -Delimiter "`t" -Header 'User', 'Date' |
    Select-Object -Property @(
        'User',
        @{ Name = 'Date'; Expression = { "$($_.Date)" } }
    ) | select -Last 10 | Out-GridView -OutputMode Multiple -Title "Logon for Last 10 Users"

You must be logged in to reply to this topic.