Format a TXT file into an Excel spreadsheet

Eric Borchert 20 Reputation points
2023-11-24T15:27:46.0066667+00:00

I received a sample data with the following data w/ blank lines between each line:

Date: 11/01/2023

Code: ABC

Description: blah-blah-blah

Qty: 14

District: 3

==================

Date: 11/01/2023

Code: XYZ

Description: yada-yada-yada

Qty: 12

District: 12

==================

What I am looking to do is ingest this file and arrange it in a CSV as follows:

Date, Code, Description, Qty, District
11/01/2023, ABC, blah-blah-blah, 14, 3
11/01/2023, XYZ, yada-yada-yada, 12, 12

I'm relatively new at PowerShell coding, and my basic mind says, "I can do this with a 1000 If statements". I'm sure there is a more streamlined method, but it's just not coming to me.

PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,319 questions
{count} votes

Accepted answer
  1. Rich Matheisen 45,831 Reputation points
    2023-11-25T00:28:00.42+00:00

    Alternatively, you can do it this way:

    $props = @{}
    Get-Content c:\temp\x.txt |
        ForEach-Object{
            if ($_.Trim().Length -gt 0){
                [array]$parts = @()
                Switch -Regex ($_){
                    "^Date:\s\d\d?/\d\d?/\d\d\d\d"  {$parts =$_ -split ": "; $props[$parts[0].trim()] = $parts[1].trim(); break}
                    "^Code:\s\w+"                   {$parts =$_ -split ": "; $props[$parts[0].trim()] = $parts[1].trim(); break}
                    "^Description:\s"               {$parts =$_ -split ": "; $props[$parts[0].trim()] = $parts[1].trim(); break}
                    "^Qty:\s\d+"                    {$parts =$_ -split ": "; $props[$parts[0].trim()] = $parts[1].trim(); break}
                    "^District:\s\d+"               {$parts =$_ -split ": "; $props[$parts[0].trim()] = $parts[1].trim(); break}
                    "^=================="           {[PSCustomObject]$props; $props.Clear(); break}
                }
            }
        } | Export-CSV c:\junk\stuff.csv -NoTypeInformation
    

1 additional answer

Sort by: Most helpful
  1. MotoX80 32,836 Reputation points
    2023-11-24T18:18:43.3766667+00:00

    As long as your data is clean and consistent, here is one way.

    $data = Get-Content c:\temp\x.txt | Where-Object Length -NE 0  # read data and omit blank lines 
    $table = @()
    $Obj = New-Object -TypeName PSCustomObject           # define our object   
    foreach ($line in $data) {
        if ($line.StartsWith("====")) {                  # end of data row indicator 
            $table += $obj                               # add to our table 
            $Obj = New-Object -TypeName PSCustomObject   # and reset the object 
        } else {
            $array = $line.split(":")                    # pick off column name and value 
            $obj | Add-Member -MemberType NoteProperty -Name $array[0].trim() -Value $array[1].trim()
        }
    }
    
    $table | Export-Csv c:\temp\x.csv -NoTypeInformation 
    Get-Content c:\temp\x.csv