英文:
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.txtWrite-host "FINAL FILE conversion completed"
如果您需要任何进一步的帮助或解释，请告诉我。
英文:
i have sample.txt in table format
Testing line1 in fileDATE M MA S FD FU PE SS AS NO OO CD05/31/23 FM 0 000 Account name 1 403 30.75 0.000 0.00 0 0 204/31/23 FM 0 000 Account name 2 403 30.75 0.000 0.00 0 0 203/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.txtWrite-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/ tabConvertFrom-Csv -Delimiter "`t" | # Read as tab-delimitedSelect-Object DATE, PE, SS, AS, NO, OO, CD | # Select cols. of interestConvertTo-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-StringDatais unsuited to parsing your data, because it expects strings with each line containing a single key-value pair in the formatkey=value
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论