Text manipulation to CSV

PowerShell Help
1

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?

 

2

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.

3

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!

 

4

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
5

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.
6

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 ‘,’