Format a TXT file into an Excel spreadsheet

Question

Format a TXT file into an Excel spreadsheet

Eric Borchert 20

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.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Accepted answer

1 additional answer

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Rich Matheisen 47,896

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

MotoX80 36,186

I'm not having any luck using a template. I'll take a look at what you've posted.

I did get this to work.

 
$table = @()
$data = (Get-Content c:\temp\x.txt -raw) -split "=================="
foreach ($d in $data){
    if ($d.trim().length -gt 0) {
         $Props = ConvertFrom-StringData -StringData ( $d -replace ':', '=') 
         $table += (New-Object PSOBject -Property $Props | Select-Object Date,Code,Description,Qty,District)
    }
}
$table | Export-Csv c:\temp\x.csv -NoTypeInformation 
Get-Content c:\temp\x.csv

From https://powershellmagazine.com/2014/09/09/using-the-convertfrom-string-cmdlet-to-parse-structured-text/

Eric Borchert 20 Reputation points

2023-12-09T14:13:07.81+00:00

I have chosen this one as the accepted answer because it also provides me a method to ignore certain lines that contain frivolous data.

Answer 2

MotoX80 36,186

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

Rich Matheisen 47,896 Reputation points

2023-11-25T00:29:36.9633333+00:00

@MotoX80 Have you ever tried to use ConvertFrom-String? Did you ever get it to work using a template?
MotoX80 36,186 Reputation points

2023-11-25T02:23:32.0466667+00:00

Rich, no, in my travels I have not come across convertfrom-string. I'll play around with it tomorrow as it looks interesting.

-Dave
Rich Matheisen 47,896 Reputation points

2023-11-25T16:50:41.0633333+00:00
HAH! I think I get it!

Get-Content C:\junk\File.txt | ConvertFrom-String -TemplateFile C:\junk\TemplateFile.txt | Export-CSV c:\junk\file.csv -NoTypeInformation

In the template file you identify the record delimiter by following the name with an asterisk.

To construct the template file, start by selecting (manually) the first "record" in the data file and pasting it into an empty template file. Identify the parts of the record (in the template file) that will become the property names in the PSCustomObject for each record.

I've tried marking the 2nd field (e.g., "Code") as the delimiter but it fails with an obscure "ConvertFrom-String : Multiple field examples in a region! There must be only one." error. So far it only seems to work if you mark the 1st field as the delimiter (i.e., the "Date").

You really need to examine the input (data) file to ensure you provide enough examples of records that may have different "patterns" of data in the fields you're using.

For example, if the "Code" in your template data only contains A-Z in a field and there are records in the data file that have only 0-9 you may have to add another record to the template file that looks like that record.

It's an iterative process, but it looks like it might make sense to use this on complex data sets.

Oh . . . the diagnostics from any errors can be head-scratchingly inscrutable!

The data file:

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 ================== Date: 11/01/2010 Code: 123 Description: ab-3x Qty: 1234 District: 142 ================== Date: 4/1/1990 Code: 1 Description: 123 Qty: 1234 District: 142 ==================

The template file:

Date: {Date*:11/01/2023} Code: {Code:ABC} Description: {Description:blah-blah-blah} Qty: {Qty:14} District: {District:3} ================== Date: {Date*:11/02/2023} Code: {Code:XYZ} Description: {Description:yada-yada-yada} Qty: {Qty:12} District: {District:12} ==================
Rich Matheisen 47,896 Reputation points

2023-11-25T19:37:30.1+00:00

I see why trying to make the "Code" field the record delimiter doesn't work. The software would see the "Date" field at the top of the file AND the "Date" field in the 2nd "record" before sees the "Code" field in the 2nd "record" that signifies the start of the 2nd record. Therefore, there are TWO "Date" fields in what the software considers to be one record.

If the data file contained only one line per record (like, say, a log file) that problem wouldn't arise.
MotoX80 36,186 Reputation points

2023-11-25T21:08:03.2033333+00:00

My problem was that I only had one "record" in the template, and I had also removed the blank lines.

Share via

Format a TXT file into an Excel spreadsheet

1 additional answer

Your answer