Text manipulation to CSV

Welcome Forums General PowerShell Q&A Text manipulation to CSV

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
1 week, 1 day ago.

  • Author
    Posts
  • #175801

    Participant
    Topics: 1
    Replies: 2
    Points: 17
    Rank: Member

    Hi All,

    Thanks for the forums, they look active from the great community that's here.

    Problem:
    I have a text file with some data separated by new lines and spaces that I want some of it converted to csv, so that I can then import into a database.

    Sample data:

    Tue 09/10/2019 08:20:46:27 Text Here
    FOO 8000 - BAR 10.1.0.70 XXX
    7,846
    $22,037.00
    $292.50

    Expected output:

    09/10/2019,08:20:46:27,8000,FOO,10.1.0.70,7846,$22037.00,$292.50

    Notice commas are missing from fields 6 and 7.

    I suppose an alternative to this is to quote them:

    09/10/2019,08:20:46:27,8000,FOO,10.1.0.70,"7,846","$22,037.00",$292.50

    So how can I approach this with powershell and manipulate the data to my desired outcome?

     

  • #175828

    Participant
    Topics: 0
    Replies: 100
    Points: 363
    Helping Hand
    Rank: Contributor

    Sean,

    We will need a bit more details, you have various entries here and will need to look at a way to filter them into a new PSCustomObject. We also request you to provide the code you currently have worked on. This forum is not here to build the code for you, but instead on your journey to learning of PowerShell and its awesomeness! I'm happy to help with the coding, but your efforts are required first.

  • #175849

    Participant
    Topics: 1
    Replies: 2
    Points: 17
    Rank: Member

    Hi Jason,

    Thanks for the reply. I should have stated that I don't intend for someone to make a script for me to do exactly what I wanted. Sorry about that.

    I'll take a look at PSCustomObject and see how it can filter data for my needs.

    Thanks!

     

  • #175864

    Participant
    Topics: 0
    Replies: 100
    Points: 363
    Helping Hand
    Rank: Contributor

    Sean,

    PSCustomObject is used to create a custom object that you can add the note properties (members) and then pipeline to other cmdlets. You would still need to look at adding filtering to your data. If you can provide a subset of data that we can use that will help to provide you with more guidance.

    Example below note this uses Powershell 4.0 or higher.

    $Object = [PSCustomObject][Ordered] @{
       Property1 = 'Property1Value'
       Property2 = 'Property2Value'
    }
    $object | Export-CSV C:\temp\File.Csv -NoTypeInformation
  • #175954

    Participant
    Topics: 1
    Replies: 2
    Points: 17
    Rank: Member

    Hello,

    Here's an idea of the properties and their respective values:

    $Object = [PSCustomObject][Ordered] @{
       date = '09/10/2019'
       time = '08:20:46:27'
       FOO = '8000'
       BAZ ='AAA'
       BAR = '10.1.0.70'
       Value = '$22,037.00'
       Value2 = '$292.50'
    } $object | Export-CSV C:\temp\File.Csv -NoTypeInformation

    And the output:

    "date","time","FOO","BAZ","BAR","Bet","Balance"
    "09/10/2019","08:20:46:27","8000","AAA","10.1.0.70","$22,037.00","$292.50"
    As you see in the original post, the file is five lines, with each value separated by a space or a new line. The second line contains FOO and BAR, separated by a – and spaces.

    Does this help?

    Edit: Sorry, I don't know how to hide the white spaces or red marks.

  • #175981

    Participant
    Topics: 1
    Replies: 1528
    Points: 2,581
    Helping Hand
    Rank: Community Hero

    Assumed your input text file always has the same format and with the requirement to create the output you stated in your first post you will probably have to parse it yourself. This comes close ... if I got it right:

    $Content = Get-Content -Path D:\sample\TextInputFile.txt
    
    $Object = [ordered]@{
        Date      = $($Content[0] -match '\d{2}\/\d{2}\/\d{4}' | Out-Null ; $Matches[0])
        Time      = $($Content[0] -match '\d{2}:\d{2}:\d{2}:\d{2}' | Out-Null ; $Matches[0])
        Foo       = $($Content[1] -match '(?< =Foo\s+)\d+(?=\s+)' | Out-Null ; $Matches[0])
        FooName   = $($Content[1] -match '(?<=^)\w+(?=\s+)' | Out-Null ; $Matches[0])
        IP        = $($Content[1] -match '(?<=Bar\s+)\d+\.\d+\.\d+\.\d+(?=\s+)' | Out-Null ; $Matches[0])
        IntNumber = [INT]$( $($Content[2] -match '[\d,]+' | Out-Null ; $Matches[0]))
        PriceOne  = [DOUBLE]$( $($Content[3] -match '[\d,]+\.?(\d{2})' | Out-Null ; $Matches[0]) )
        PriceTwo  = [DOUBLE]$( $($Content[4] -match '[\d,]+\.?(\d{2})' | Out-Null ; $Matches[0]))
    }
    $Object.Values -join ','

You must be logged in to reply to this topic.