CODEWITHSUNDEEP
csvpowershelltext
Rudy86
Rudy86

Reputation: 43

Text file to CSV file with header

I have a question about a text file that I want to convert to a CSV file with headers.

The origional text file looks like this: 

StepStartTime: 2/1/2016 12:02:03 PM
StepStopTime: 2/1/2016 12:02:06 PM
StepNumber: 1
NextStepNumber: 2
---------------------------------------------------------------
StepStartTime: 2/1/2016 12:02:06 PM
StepStopTime: 2/1/2016 12:02:07 PM
StepNumber: 2
NextStepNumber: 3
---------------------------------------------------------------
StepStartTime: 2/1/2016 12:02:07 PM
StepStopTime: 2/1/2016 12:02:08 PM
StepNumber: 3
NextStepNumber: 4
---------------------------------------------------------------

What I want to get is the following:

StepStartTime,StepStopTime,StepNumber,NextStepNumber
2/1/2016 12:02:03 PM,2/1/2016 12:02:06 PM,1,2
2/1/2016 12:02:06 PM,2/1/2016 12:02:07 PM,2,3
2/1/2016 12:02:07 PM,2/1/2016 12:02:08 PM,3,4

I'm trying to get it to work in PowerShell but no luck so far. I can read code on a basic level but to combine different actions by myself is one step to far.

Upvotes: 1

Views: 987

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200523

Read the input into a string and split it at the separating lines

(Get-Content 'C:\path\to\input.txt' -Raw) -split '-{63}'

so you get snippets like this:

StepStartTime: 2/1/2016 12:02:07 PM
StepStopTime: 2/1/2016 12:02:08 PM
StepNumber: 3
NextStepNumber: 4

Change the colons into = characters, so you can convert the snippets to hashtables via ConvertFrom-StringData:

$snippet -replace ': ', '=' | ConvertFrom-StringData

Which will give you something like this for each snippet:

Name                           Value
----                           -----
StepStopTime                   2/1/2016 12:02:08 PM
StepNumber                     3
NextStepNumber                 4
StepStartTime                  2/1/2016 12:02:07 PM

Create custom objects from the hashtables:

New-Object -Type PSObject -Property $hashtable

to get a format that you can export to a CSV:

StepStopTime           StepNumber   NextStepNumber   StepStartTime
------------           ----------   --------------   -------------
2/1/2016 12:02:08 PM   3            4                2/1/2016 12:02:07 PM

Full code:

(Get-Content 'C:\path\to\input.txt' -Raw) -split '-{63}' | Where-Object {
  $_.Trim()
} | ForEach-Object {
  $props = $_.Trim() -replace ': ', '=' | ConvertFrom-StringData
  New-Object -Type PSObject -Property $props
} | Export-Csv 'C:\path\to\output.csv' -NoType

Note: If you're still using PowerShell v2 you need to replace

Get-Content 'C:\path\to\input.txt' -Raw

with something like

Get-Content 'C:\path\to\input.txt' | Out-String

to get the content of the file as a single string. The parameter -Raw isn't available prior to PowerShell v3.

Upvotes: 5

Related Questions