如何在excel中构造列

所以，这看起来像是一个产生这个的时钟应用程序。

如果是真正的单列文本文件。您只需在阅读字符串时对其进行操作即可。例如：

# 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 连接来自两列的数据'

因此最终得到这样的 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