Convert text File to CSV Format using powershell

Question

I want to convert the Text File to CSV File and below is the Format using Powershell and need some assistance

• Text File Structure: This is how the data looks in output or text file

Content for reference:

Quantity: 30
Manufacturer: Microsoft
License metric: Microsoft Core Infrastructure Server Suite Datacenter Core
Expiry: 31/12/2024

Quantity: 70
Manufacturer: Microsoft
License metric: Microsoft Windows Server Datacenter Core (SA)
Expiry: 31/12/2025

Quantity: 58
Manufacturer: Microsoft
License metric: Microsoft Core Infrastructure Server Suite Datacenter Core
Expiry: 31/12/2024

Convert Text File to CSV (into rows and columns) CSV Header: Quantity, Manufacturer, License metric, Expiry

Answer 1

Note:

• If preserving the input order of properties isn't important, a simpler,
  ConvertFrom-StringData-based solution is possible, as shown in Doug Maurer's answer.

• GitHub issue #19070 is a feature request to make ConvertFrom-Json emit ordered hashtables instead, which then would preserve the input order.

---

I suggest processing your file via the regex-based -split operator:

# Create an ordered helper hashtable to help with [pscustomobject] construction
$oht = [ordered] @{} 

# Split the input file into blocks of lines, and process each block.
(Get-Content -Raw file.txt) -split '\r?\n\r?\n' -ne '' | ForEach-Object { 
  $oht.Clear() # Clear the helper hashtable to start constructing a new object.
  # Split the block at hand into individual lines and process each.
  $_ -split '\r?\n' -ne '' | ForEach-Object {
    # Split the line at hand into property name and value...
    $name, $value = $_ -split ':', 2
    # ... and add this property-name pair to the helper hashtable.
    $oht[$name] = $value.Trim()
  }
  # Construct a [pscustomobject] from the helper hashtable and output it.
  [pscustomobject] $oht
}  # | Export-Csv ...  # Add your Export-Csv call here.

Display output, showing the constructed objects and their properties, using implicit table formatting:

Quantity Manufacturer License metric                                             Expiry
-------- ------------ --------------                                             ------
30       Microsoft    Microsoft Core Infrastructure Server Suite Datacenter Core 31/12/2024
70       Microsoft    Microsoft Windows Server Datacenter Core (SA)              31/12/2025
58       Microsoft    Microsoft Core Infrastructure Server Suite Datacenter Core 31/12/2024

Note:

• This solution works with any similarly formatted text file (where each contiguous block of lines represents an object, and property names and values are separated by :, and are unquoted), irrespective of the specific property names.

• Due to using regex \r?\n to match newlines (line breaks), both Windows-format files with CRLF newlines and Unix-format files with LF-only newlines are supported.

• For each block of lines, an ordered hashtable ([ordered] @{}) is used to collect that block's property-value pairs.

• Casting this hashtable to [pscustomobject] creates an instance of the latter, suitable for piping to Export-Csv.

Answer 2

If the order of the properties is not a concern, you could take advantage of ConvertFrom-StringData to simplify this task. Github issue #19070 proposes this issue be fixed.

For powershell 6+ which offers the -Delimiter parameter we can process as is

# Get all the text data in one string
$data = Get-Content textfile.txt -Raw

# split on repeating new lines to get the blocks of text
$blocks = $data -split '\r?\n\r?\n'

# process each block 
$blocks | Foreach-Object {
    # cast the resulting hash table to pscustomobject
    [PSCustomObject]($_ | ConvertFrom-StringData -Delimiter ':')
}

For powershell 5.1, we have to replace the colon with an equals sign

# Get all the text data in one string
$data = Get-Content textfile.txt -Raw

# split on repeating new lines to get the blocks of text and replace the first found colon on each line with an equals character
$blocks = $data -split '\r?\n\r?\n' -replace '(?m)(?<=^[^:]+):','='

# process each block 
$blocks | Foreach-Object {
    # cast the resulting hash table to pscustomobject
    [PSCustomObject]($_ | ConvertFrom-StringData)
}

Both should produce this output

License metric                                             Expiry     Quantity Manufacturer
--------------                                             ------     -------- ------------
Microsoft Core Infrastructure Server Suite Datacenter Core 31/12/2024 30       Microsoft
Microsoft Windows Server Datacenter Core (SA)              31/12/2025 70       Microsoft
Microsoft Core Infrastructure Server Suite Datacenter Core 31/12/2024 58       Microsoft

Answer 3

To convert the text file to CSV file, you can use the following PowerShell script:

$inputFile = "file1.txt"
$outputFile = "fileout1.csv"
$inputLines = Get-Content $inputFile
$outputLines = @("Quantity,Manufacturer,License metric,Expiry")
for ($i = 0; $i -lt $inputLines.Length; $i += 4) {
    $quantity = $inputLines[$i] -replace "Quantity: "
    $manufacturer = $inputLines[$i+1] -replace "Manufacturer: "
    $licenseMetric = $inputLines[$i+2] -replace "License metric: "
    $expiry = $inputLines[$i+3] -replace "Expiry: "
    $outputLines += "$quantity,$manufacturer,$licenseMetric,$expiry"
}
$outputLines | Out-File $outputFile

Make sure to replace the $inputFile and $outputFile variables with the actual paths to your input and output files. This script reads the input file, splits the lines into columns, and writes the output to a CSV file with the specified header.

Output:

Quantity,Manufacturer,License metric,Expiry
30,Microsoft,Microsoft Core Infrastructure Server Suite Datacenter Core,31/12/2024
70,Microsoft,Microsoft Windows Server Datacenter Core (SA),31/12/2025
58,Microsoft,Microsoft Core Infrastructure Server Suite Datacenter Core,31/12/2024