英文:
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 '\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 useFormat-*
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 formatkey=value
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论