Extracting columns from txt file with two delimiters using powershell

Question

i have a sample.txt file

Processing...
Cl.Cog="/u/l/Cg.txt"
V.DM=Nv
AL.Ft="He Se Fe",Bt,@L(Ey,0),&Ct:&Cu3

i need to seperate this file with 4 different columns with new column added before output.csv

PHP> This is hard coded line;

column1              column2             column3             column4              
+-------------------+-------------------+-------------------+-------------------
abcdefg                Cl                 Cog               "/u/l/Cg.txt"               
abcdefg                V                  DM                   Nv
abcdefg                AL                 Ft                 "He Se Fe",Bt,@L(Ey,0),&Ct:&Cu3

so for i have tried this,which doesn't seem to be working

Get-Content test.txt | 
$headerString = "PHP> This is hard coded line;"

$headerElements = $headerString -split "\s+" | Where-Object{$_}
$headerIndexes = $headerElements | ForEach-Object{$headerString.IndexOf($_)}
$results = $data 
Foreach {"$(($_ -split '\s+',4)[0..2])"} |
Out-file -filepath "D:\output.txt"    

Answer 1

I encourage you to not try and "build" the csv yourself. Focus on creating the objects you want and then simply export to csv. Your header line as more columns then your data, plus you don't state specifically you want to exclude the processing... line, nor do you account for it in your example code, but your desired output doesn't show it. Here is how I would handle what I believe you are after.

# Create the headers for the csv from your hard coded line
$headerString = "PHP> This is hard coded line;"
$headers = -split $headerString

# Get the text content, you can skip 1 here if you need to exclude processing
$data = Get-Content test.txt

# Iterate over each line replacing literal period and equals with a pipe. 
# Combine this with your static first column value and a pipe 
# I chose pipe as a delimiter as your text file had commas but not a pipe
$results = foreach($line in $data){
    # again, this will create a pipe delimited string
    $csv = 'abcdefg|' + ($line -replace '\.|=','|')
    # convert this string to csv with your custom headers
    $csv | ConvertFrom-Csv -Delimiter '|' -Header $headers
}

All the output was collected in $results. You can review and/or export now

$results | Format-Table

PHP>    This is  hard                          coded line;
----    ---- --  ----                          ----- -----
abcdefg Cl   Cog /u/l/Cg|txt                              
abcdefg V    DM  Nv                                       
abcdefg AL   Ft  He Se Fe,Bt,@L(Ey,0),&Ct:&Cu3  

# output to csv
$results | Export-Csv Some.csv -NoType

If you want to output the table view to a text file (I don't recommend it, csv is much easier to consume again later) you can do this

$results | Format-Table | Out-String | Set-Content c:\temp\textfile.txt