Convert txt file to csv

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 7 months ago.

  • Author
    Posts
  • #5480

    by bpa350 at 2013-01-29 06:24:53

    I have a directory of log files with alot of information that I need to extract specific data out of and present in a csv file. I found a script at the old site recently and tried to apply the code but something is alittle off as it presents the information in a table format but the data is not under the correct table. Thanks in advance.

    The log files are composed of:
    XXX Data
    BEGINUSER=smith.john@email.com
    XXX Data
    TotalMsgCnt=676
    XXX Data
    StartTime=01/23/2013 22:47:52
    XXX Data
    EndTime=01/23/2013 23:12:33
    BEGINUSER=jones.jim@email.com
    TotalMsgCnt=1948
    XXXData
    StartTime=01/23/2013 22:47:50
    EndTime=01/23/2013 23:23:26

    I would like to have them broken out as:

    User TotalMsgCnt StartTime EndTime
    smith.john@email.com 676 01/23/2013 22:47:52 01/23/2013 23:12:33

    The code I have been working with is:

    $people = @()

    $data = Select-String -path c:\users\admin\desktop\logs\testlog.wlog -Pattern "beginuser=", "totalmsgcnt=", "starttime=", "endtime="

    for ($i = 0; $i -lt $data.Length; $i += 4)
    {
    $beginuser = ($data[$i] -split '=')[1]
    $totalmsgcnt = ($data[$i + 1] -split '=')[1]
    $starttime = ($data[$i + 2] -split '=')[1]
    $endtime = ($data[$i + 3] -split '=')[1]

    $people += New-Object PSObject -Property @{
    User = $name
    TotalMsgCnt = $totalmsgcnt
    StartTime = $startime
    EndTime = $endtime
    }
    }

    by DonJ at 2013-01-29 07:41:23

    As a note, you should use the CODE button in the toolbar to format code when posting it.

    So, in terms of folks here being able to help, you'll have to break it down a little. We're all volunteering, so it's often tough for us to copy your code, and your sample file, off the Web site and run it ourselves and fix it for you. It's a bit easier if you can point us to one problem at a time, and let us help with that – and show us the output you're working with as well. I'm gonna give it a shot here, because I think I see the problem, but I'm working a bit blind, so bear with me if this doesn't do it.

    The code you're working with should emit an object. PowerShell natively formats that as a table because it only has four properties, but it isn't a "table." The code isn't well-written though. For example, it isn't actually outputting $people. $People also isn't necessary.


    $data = Select-String -path c:\users\admin\desktop\logs\testlog.wlog -Pattern "beginuser=", "totalmsgcnt=", "starttime=", "endtime="

    for ($i = 0; $i -lt $data.Length; $i += 4)
    {
    $beginuser = ($data[$i] -split '=')[1]
    $totalmsgcnt = ($data[$i + 1] -split '=')[1]
    $starttime = ($data[$i + 2] -split '=')[1]
    $endtime = ($data[$i + 3] -split '=')[1]

    New-Object PSObject -Property @{
    User = $name
    TotalMsgCnt = $totalmsgcnt
    StartTime = $startime
    EndTime = $endtime
    }
    }

    This modification doesn't accumulate the output into $people; it writes the output to the pipeline, whose whole job is to accumulate output and pass it on to the next command.

    If you put that into a script named C:\Test.ps1, you can then run C:\Test | Export-CSV filename.csv to get a CSV file out of it.

    by bpa350 at 2013-01-29 08:08:29

    Thanks Don... I will make sure I use the code button next time and will use the method you described above and pipe the information out. So, to break this down alittle further the log files I am pulling from have many lines of code. When a user migration process starts the "Beginuser=" entry is placed into the log file followed by a few irrelevent lines and then "TotalMsgCnt=", a few more irrelevant lines, "Starttime=" , a few more, and so on. What I am learning is that some of the users migration process is aborted which in turns only creates the "Beginuser=" entry and not the other three entries. Once this occurs it throws off the formatting of the data and for example causes "Starttime=" to be placed under one of the other headers such as EndTime. I am realizing this is not the best approach at data mining the log files but I was hoping for a quick fix.

    Any help in not having to scrap and start over would be appreciated.

    by DonJ at 2013-01-29 08:14:19

    There's no quick fix. Garbage in means more work for you.

    You're going to have to parse the log file line by line, and if you see another "begin" before you saw an "end," take whatever action you deem appropriate. It's going to require adding a good bit more logic to the script.

    Perfect example of why text files make a horrible form of data storage. They're easy to create and then impossible to re-use.

    by nohandle at 2013-01-30 02:35:00

    This may work for you, the data are split into groups by the Begin part of the BEGINUSER. Then I just iterate over each line in the group and look for name=value patterns. Make these into properties and output them as objects. Two loops and some logic. When you use it the file needs to by loaded as single string using get-content -readcount 0.

    (the code needed to be split into two parts, the markup plugin did some wierd things with the email addresses so I pasted the first part as 'code')
    #preparing the data to be able to feed one line at time
    #the get-content reads the file the same way by default
    $file = @'
    The log files are composed of:
    XXX Data
    BEGINUSER=smith.john@email.com
    XXX Data
    TotalMsgCnt=676
    XXX Data
    StartTime=01/23/2013 22:47:52
    XXX Data
    EndTime=01/23/2013 23:12:33
    BEGINUSER=jones.jim@email.com
    TotalMsgCnt=1948
    XXXData
    StartTime=01/23/2013 22:47:50
    EndTime=01/23/2013 23:23:26
    '@

    $userScopes = $file -split 'Begin'
    $userScopes | foreach -process {
    $userLines = $_ -split "`n"
    $userLines |
    foreach -Begin {
    $PropertySet=@{}
    } -Process {
    if ($_ -match "^\S*?(?.*?)=(?.*)\s*$")
    {
    $PropertySet += @{$matches.name = $matches.value}
    }
    } -end {
    if ($PropertySet.Count -gt 0)
    {
    New-Object -TypeName psObject -Property $PropertySet
    }
    }
    } | Format-List


    EndTime : 01/23/2013 23:12:33
    USER : smith.john@email.com
    TotalMsgCnt : 676
    StartTime : 01/23/2013 22:47:52

    EndTime : 01/23/2013 23:23:26
    USER : jones.jim@email.com
    TotalMsgCnt : 1948
    StartTime : 01/23/2013 22:47:50

You must be logged in to reply to this topic.