Powershell convert unordered Text to CSV

I have a system that exports a text file [.txt] and wish to convert the text to a .csv file. The export contains 500+ lines so manual manipulation in Excel is very slow. I have no means of changing the way the export is formatted so a delimiter is not possible. 😒

Below is an example of the export...

 ACC004 Access Control 29/03/23 9000053641 801MS-0070 Sliding -1 -£391.00  -£255.31

 ACC061 Access Automation 22/03/2023 9000053431 ZL92Z Control -1 -£139.50 -83.80

 ACC061 Access Automation 27/03/2023 9000053538 STYLO-ME Swing -1 -£191.62 -£113.45

 ACT015 Active Concept 31/03/2023 9000053739 801MP-0070 Swing -2 -£324.76 -£238.69

 ADI005 ADI Gardner Ltd 29/03/2023 9000053642 806SS-0020 Safety -1 -£46.35 -£28.19

 ADI008 ADI Gardner Ltd 31/03/2023 9000053736 AX3024 Swing -1 -£213.00 -£126.55

 ADI011 ADI Gardner Ltd 29/03/2023 9000053591 3199ZM3E Control -1 -£112.25 -£64.80

 AGD001 AGD Systems Ltd 23/03/2023 9000053465 AX3024 Swing -1 -£200.65 -£123.79

My plan is to use PowerShell to iterate through each line of the .txt file with a foreach loop and convert the line into an array that can be written to a CSV file. My stumbling block is how to split the row so that the correct values are added to the correct array positions...

Whilst I can use:-

 .Substring(0, 6)

To pull the ID [0] from the start of the string the next value [1] should contain the account name which can be any character length and any number of spaces.

Example CSV

Any suggestions would be gladly received, So the fun begins...

Try following code which uses Regex

<!-- begin snippet: js hide: false console: true babel: false -->

$text = @"
ACC004 Access Control 29/03/23 9000053641 801MS-0070 Sliding -1 -£391.00  -£255.31
ACC061 Access Automation 22/03/2023 9000053431 ZL92Z Control -1 -£139.50 -83.80
ACC061 Access Automation 27/03/2023 9000053538 STYLO-ME Swing -1 -£191.62 -£113.45
ACT015 Active Concept 31/03/2023 9000053739 801MP-0070 Swing -2 -£324.76 -£238.69
ADI005 ADI Gardner Ltd 29/03/2023 9000053642 806SS-0020 Safety -1 -£46.35 -£28.19
ADI008 ADI Gardner Ltd 31/03/2023 9000053736 AX3024 Swing -1 -£213.00 -£126.55
ADI011 ADI Gardner Ltd 29/03/2023 9000053591 3199ZM3E Control -1 -£112.25 -£64.80
AGD001 AGD Systems Ltd 23/03/2023 9000053465 AX3024 Swing -1 -£200.65 -£123.79
"@
$pattern = "^(?<id>[^\s]+)\s+(?<name>.*)(?<date>\d{2}/\d{2}/\d+)\s+(?<order>\d+)\s+(?<part>[^\s]+)\s+(?<cat>.*)\s+(?<qty>.\d+)\s+(?<val>[^\s]+)\s+(?<adj>.*)"
$reader = [System.IO.StringReader]::new($text)
$table = [System.Collections.ArrayList]::new()

while(($line = $reader.ReadLine()) -ne $null)
{
   $line -match $pattern | Out-Null
   $id = $Matches.id.Trim()
   $name = $Matches.name.Trim()
   $date = $Matches.date.Trim()
   $order = $Matches.order.Trim()
   $part = $Matches.part.Trim()
   $cat = $Matches.cat.Trim()
   $qty = $Matches.qty.Trim()
   $val = $Matches.val.Trim()
   $adj = $Matches.adj.Trim()
   
   $newRow = [PSCustomObject]@{
      ID = $id
      Name = $name
      Date = $date
      Order = $order
      Part = $part
      Cat = $cat
      Qty = $qty
      Val = $val
      Adj = $adj
   }

   $table.Add($newRow) | Out-Null
   
}
$table | format-list
$table | export-csv 'c:\temp\test.csv'

<!-- end snippet -->

Looking at your data. You could use split to split them up, then use a regex expression to search for the date. Once you found the date you know the ones prior are the account and id. Since ID seems to be one "Word" you know the "Words" between ID and date are your account name.