How to import data from table format txt file and export to csv in powershell?

Question

i have sample.txt in table format

Testing line1 in file                                         

DATE    M    MA    S    FD    FU    PE    SS    AS    NO    OO    CD
05/31/23    FM    0    000    Account name 1         403    30.75    0.000    0.00    0    0    2
04/31/23    FM    0    000    Account name 2         403    30.75    0.000    0.00    0    0    2
03/31/23    FM    0    000    Account name 3         403    30.75    0.000    0.00    0    0    2

i want to convert this file into csv file and skip 2 rows and make changes like this by adding new column

FINAL_OUTPUT.csv

"DATE","PE","SS","AS","NO","OO","CD","Name","data"
"May","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 1,403","data1" 
"April","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 2,403","data1"
"March","30.75","0.000","0.00","0","0","2","FM,0,000,Account name 3,403","data1"

so far i have tried this code

$data = format-table 'E:\sample.txt'  |
    Select-Object -Skip 2 |
    Out-String |
    ConvertFrom-StringData
 [PSCustomObject]$data | Export-Csv 'E:\out1.txt' -NoType

$tempcsv=import-csv -path E:\out1.txt -Header 
"DATE”,”M”,”MA”,”S”,”FD”,”FU”,”PE”,”SS”,”AS”,”NO “,“OO”,”CD”| select -skip 2
$tempcsv | select DATE,M,MA, S ,FD,FU,PE,SS ,AS,NO,OO,CD| Group-Object M,MA,S,FD,FU| 
select Name,
@{ Name = 'PE'; Expression = { ($_.Group | Measure-Object -Property PE -Sum).Sum } },
@{ Name = 'SS'; Expression = { ($_.Group | Measure-Object -Property SS -Sum).Sum } },
@{ Name = 'AS'; Expression = { ($_.Group | Measure-Object -Property AS -Sum).Sum } },
@{ Name = 'NO'; Expression = { ($_.Group | Measure-Object -Property NO -Sum).Sum } },
@{ Name = 'OO'; Expression = { ($_.Group | Measure-Object -Property OO -Sum).Sum } },
@{ Name = 'CD'; Expression = { ($_.Group | Measure-Object -Property CD -Sum).Sum } }| 
EXPORT-CSV -path E:\FINAL_OUTPUT.txt
Write-host "FINAL FILE converSion completed"

mklement0 · Answer 1 · 2023-06-14T18:36:14.297

Leaving the desired transformations and follow-up processing aside, here's how you can transform your input data to CSV:

Get-Content sample.txt | # Read file as plain text, line by line.
  Select-Object -Skip 2 | # Skip first 2 lines.
  ForEach-Object { $_ -replace '\s+', "`t" } | # Replace whitespace runs w/ tab
  ConvertFrom-Csv -Delimiter "`t" | # Read as tab-delimited
  Select-Object DATE, PE, SS, AS, NO, OO, CD | # Select cols. of interest
  ConvertTo-Csv # Convert to CSV (use Export-Csv to send to a file).

Output (note that columns Name and data were omitted, as they're not in the original data):

"DATE","PE","SS","AS","NO","OO","CD"
"05/31/23","1","403","30.75","0.000","0.00","0"
"04/31/23","2","403","30.75","0.000","0.00","0"
"03/31/23","3","403","30.75","0.000","0.00","0"

As for what you tried:

Format-* cmdlets emit output objects whose sole purpose is to provide formatting instructions to PowerShell's for-display output-formatting system. In short: only ever use Format-* cmdlets to format data for display, never for subsequent programmatic processing - see this answer for more information.
ConvertFrom-StringData is unsuited to parsing your data, because it expects strings with each line containing a single key-value pair in the format key=value

How to import data from table format txt file and export to csv in powershell?

1 Answers1