Solved how can i convert non-columnar text to colums (CSV or tab)

Microsoft Windows server 2008 r2 standar...
June 22, 2016 at 16:23:42
Specs: Windows Server 2008
I hope this is a simple question...

I have a text file like this:

name: John
age: 23
location: USA
status: active
name: Joe
age: 45
location: Canada
status: pending
name: Jane
age: 40
location: England
status: active
and so on...

So it's a database where each record is a number of variable length lines separated by a "----" line and the fields are 'key:value' or 'label:value' pairs on each line.

My first question is what would you call this format (to distinguish it from CSV or tabular, etc)? Always helps to know the terminology.

Second, I would like to convert this to a CSV file (or some columnar format) like:

and so on...

Would be nice to handle the keys not being in the same order or present in all records but I'm not picky.

Many thanks in advance.

See More: how can i convert non-columnar text to colums (CSV or tab)

Reply ↓  Report •

June 22, 2016 at 21:34:32
✔ Best Answer
Microsoft refers to it as list format, which is the best name I've heard for it.

Anyways, you can convert it with PowerShell, assuming you've got a Win7+ / 2008R2+ box kicking around, or you patched that 2008 box to have at least PowerShell v2.

gc 'in.txt' | 
foreach -Begin { $record = @{}; $allRecords = @() } {
   if ($_ -notlike "----*") { $line = $_ -split ':', 2; $record.Add($line[0], $line[1].Trim()) }
   else { $allRecords += New-Object PSObject -Property $record; $record = @{} }
} -End { $allRecords | Export-Csv 'out.csv' -NoTypeInformation }

How To Ask Questions The Smart Way

Reply ↓  Report •

July 4, 2016 at 08:33:09
Yes, this does exactly what I want! I haven't a clue how it works but it does and thank you!

Reply ↓  Report •

Related Solutions

Ask Question