Text manipulation to CSV

Welcome Forums General PowerShell Q&A Text manipulation to CSV

Viewing 5 reply threads
  • 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: 115
      Points: 433
      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: 115
      Points: 433
      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: 1629
      Points: 3,061
      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 ','
Viewing 5 reply threads
  • You must be logged in to reply to this topic.