我在 Excel 中有以下数据：
我如何将数据分成几列？并在每列末尾添加“；”。
预期输出
我已经尝试了上千种方法，但还是迷路了。我认为应该将正确的数据类型与字母分开，但我不知道该怎么做，有人能帮我吗？
更新：尝试代码响应时，它不起作用，因为短名称在开头放置了时间戳，这是问题所在，我有一个 csv，其中只有一列包含该数据，但有些名字没有 2 个姓氏，它们被 1、2 或 3 个空格分隔，具体取决于名字，关于模块，我无法在公司中执行任何外部模块。
答案1
因此，这看起来像是一个生成此内容的时间钟应用程序。
如果是真正的单列文本文件。您只需在读取字符串时对其进行操作即可。例如：
# Single column
Clear-Host
'ES123456789 Some Name Some 03/01/2002 00:00:00 00:00:00 00:00:00' |
ConvertFrom-Csv -Delimiter ' ' -Header h0, h1, h2, h3, h4, h5, h6, h7 |
# Results
<#
h0 : ES123456789
h1 : Some
h2 : Name
h3 : Some
h4 : 03/01/2002
h5 : 00:00:00
h6 : 00:00:00
h7 : 00:00:00
#>
Select-Object -Property @{
Name = 'Key'
Expression = {$PSitem.h0 + ';'}
},
@{
Name = 'FullName'
Expression = {$PSitem.h1 + ' ' + $PSItem.h2 + ' ' + $PSItem.h3 + ';'}
},
@{
Name = 'Timestamp'
Expression = {$PSitem.h4 + ' ' + $PSItem.h5 + ' ' + $PSItem.h6 + $PSItem.h7 +';'}
}
# Results
<#
Key FullName Timestamp
--- -------- ---------
ES123456789; Some Name Some; 03/01/2002 00:00:00 00:00:0000:00:00;
#>
以上只是在空格上分割字符串并使用计算属性按照您想要的方式重新连接。
如果有两列或更多列，您可以先将它们连接起来，然后采用相同的方法，将两者合二为一并进行解析。例如：
# Two columns
Clear-Host
"
'ES123456789 Some Name', 'Some 03/01/2002 00:00:00 00:00:00 00:00:00'
'ES123456780 Another Name', 'Another 03/01/2002 00:00:00 00:00:00 00:00:00'
" |
ConvertFrom-Csv -Delimiter ',' -Header h0, h1 |
# Results
<#
h0 h1
-- --
'ES123456789 Some Name' 'Some 03/01/2002 00:00:00 00:00:00 00:00:00'
'ES123456780 Another Name' 'ANother 03/01/2002 00:00:00 00:00:00 00:00:00'
#>
ForEach-Object {
$RecodObjects = [PSCustomObject]@{
Key = ($PSItem.h0 -split ' ',2)[0] + ';' -replace "'"
Fullname = ($PSItem.h0 -split ' ',2)[1] + ' ' + ($PSItem.h1 -split ' ',2)[0] + ';' -replace "'"
TimeStamp = ($PSItem.h1 -split ' ',2)[1] + ';' -replace "'"
}
$RecodObjects
}
# Results
<#
Key Fullname TimeStamp
--- -------- ---------
ES123456789; Some Name Some; 03/01/2002 00:00:00 00:00:00 00:00:00;
ES123456780; Another Name Another; 03/01/2002 00:00:00 00:00:00 00:00:00;
#>
您可以在 Excel COM 中执行同样的事情......
https://lazywinadmin.com/2014/03/powershell-read-excel-file-using-com.html
...或者直接在 Excel 中使用 Excel 功能，文本分列数据选项卡菜单中的选项。
例如，只需搜索 Excel 文本到列以及 Excel 文本拆分和/或连接即可。
您最终要做的就是在电子表格中创建一个新列，并创建一个公式来读取其他两列并合并结果，然后将该新列用于您想要的任何内容。
最终得到如下的 Excel 单元格公式。
ColumnA: ES123456789 Some Name
ColumnB: Some 03/01/2002 00:00:00 00:00:00 00:00:00
ColumnC: =CONCATENATE(LEFT(A1,SEARCH(" ",A1)),";")
ColumnD: =CONCATENATE(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1))," ",LEFT(B1,SEARCH(" ",B1)),";")
ColumnE: =CONCATENATE(RIGHT(B1,LEN(B1)-SEARCH(" ",B1,3)),";")
您也可以使用前面提到的工具通过 PS 执行上述操作。例如：
$FilePath = 'D:\Temp\how to structure columns in excel.xlsx'
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$WorkBook = $objExcel.Workbooks.Open($FilePath)
<#
$WorkBook.ActiveSheet.UsedRange.Rows.Value2
$WorkBook.ActiveSheet.UsedRange.EntireRow.Value2
#>
# Results
<#
ES123456789 Some Name
Some 03/01/2002 00:00:00 00:00:00 00:00:00
ES123456780 Another Name
Another 03/01/2002 00:00:00 00:00:00 00:00:00
#>
$SheetData = $WorkBook.ActiveSheet.UsedRange.Rows |
ForEach-Object {
$PSItem.Value2 -join ',' |
ConvertFrom-Csv -Delimiter ',' -Header Key, TimeStamp
}
# Results
<#
Key TimeStamp
--- ---------
ES123456789 Some Name Some 03/01/2002 00:00:00 00:00:00 00:00:00
ES123456780 Another Name Another 03/01/2002 00:00:00 00:00:00 00:00:00
#>
$ReportWorksheet = $WorkBook.Worksheets.Add()
$ReportWorksheet.Name = "Timesheet Report"
$RowCounter = 0
$SheetData |
ForEach-Object {
$RowCounter += 1
$ReportData = [PSCustomObject]@{
Key = ($PSItem.Key -split ' ',2)[0] + ';' -replace "'"
FullName = ($PSItem.Key -split ' ',2)[1] + ' ' + ($PSItem.TimeStamp -split ' ',2)[0] + ';' -replace "'"
TimeStamp = ($PSItem.TimeStamp -split ' ',2)[1] + ';' -replace "'"
}
$ReportWorksheet.Cells.Item($RowCounter,1) = $ReportData.Key
$ReportWorksheet.Cells.Item($RowCounter,2) = $ReportData.FullName
$ReportWorksheet.Cells.Item($RowCounter,3) = $ReportData.TimeStamp
}
# Results
<#
Key FullName TimeStamp
--- -------- ---------
ES123456789; Some Name Some; 03/01/2002 00:00:00 00:00:00 00:00:00;
ES123456789; Some Name Some; 03/01/2002 00:00:00 00:00:00 00:00:00;
#>
$ReportWorksheet.UsedRange.EntireColumn.AutoFit()
$WorkBook.SaveAs( "D:\Temp\TimeSheetReport.xlsx")
$WorkBook.Save = $true
但是，如果您只是一次性执行此操作，则不需要编写脚本。
根据我上次给您的评论进行更新 再次强调，如果这不是常规做法，您可以直接在 MS Excel 中使用 Excel 公式执行此操作。编写一次性脚本确实有点想太多了。
# Single column RegEx capture for data refactor
Clear-Host
$FilePath = 'D:\Temp\how to structure columns in excel.xlsx'
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$WorkBook = $objExcel.Workbooks.Open($FilePath)
$ReportData = $WorkBook.ActiveSheet.UsedRange.Rows.Value2 |
ForEach-Object {
([RegEx]::Matches($PSItem, 'ES\d{9}').Value).Trim() +
';,' + (([RegEx]::Matches($PSItem, "(?<=ES\d{9})(.*\s)(?=\d{1,2}\/\d{1,2}\/\d{1,4}.*)").Value) -replace "\s+", ' ').Trim() +
';,' + ([RegEx]::Matches($PSItem, "\d{1,2}\/\d{1,2}\/\d{1,4}.*").Value).Trim() + ';'
}
$ReportWorksheet = $WorkBook.Worksheets.Add()
$ReportWorksheet.Name = "Timesheet Report"
$RowCounter = 0
$ReportData |
ForEach-Object {
$RowCounter += 1
$ReportWorksheet.Cells.Item(($RowCounter), 1) = ($PSItem -split ',')[0]
$ReportWorksheet.Cells.Item(($RowCounter), 2) = ($PSItem -split ',')[1]
$ReportWorksheet.Cells.Item(($RowCounter), 3) = ($PSItem -split ',')[2]
}
[void]$ReportWorksheet.UsedRange.EntireColumn.AutoFit()
Remove-Item -Path 'D:\Temp\TimeSheetReport.xlsx' -ErrorAction SilentlyContinue -Force
$WorkBook.SaveAs( "D:\Temp\TimeSheetReport.xlsx", 51)
[void]$WorkBook.Save
