如何从表格格式的txt文件中导入数据并在PowerShell中导出为csv?

huangapple go评论75阅读模式
英文:

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

问题

这是您的代码部分,已翻译好:

$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"

如果您需要任何进一步的帮助或解释,请告诉我。

英文:

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"

答案1

得分: 1

以下是您要翻译的内容:

<!-- language-all: sh -->

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](https://stackoverflow.com/a/55174715/45375) for more information.

* [`ConvertFrom-StringData`](https://learn.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/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`
英文:

<!-- language-all: sh -->

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 &#39;\s+&#39;, &quot;`t&quot; } | # Replace whitespace runs w/ tab
  ConvertFrom-Csv -Delimiter &quot;`t&quot; | # 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):

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

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

huangapple
  • 本文由 发表于 2023年6月15日 02:00:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/76476369.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定