# Create a new Excel Application object
$excel = New-Object -ComObject Excel.Application
# Add a new workbook
$workbook = $excel.Workbooks.Add()
# Get the first worksheet in the workbook
$worksheet = $workbook.Worksheets.Item(1)
# Set the column headers
$worksheet.Cells.Item(1, 1) = "COLUMN 1"
$worksheet.Cells.Item(1, 2) = "COLUMN 2"
# Read the input data from the file
$inputData = Get-Content -Path 'D:\input.txt'
# Regular expression pattern to separate the numbering and header
$pattern = '(\d+(\.\d+)*)\s*(.*)'
# Iterate through the input data and populate the table
for ($i = 0; $i -lt $inputData.Count; $i++) {
$row = $inputData[$i].Trim()
if ($row -match $pattern) {
$numbering = $matches[1].Trim()
$header = $matches[3].Trim()
# Remove page numbers and ellipsis from the header
$header = $header -replace '\s*\d+$', '' -replace '\.\.+', ''
$rowIndex = $i + 2
# Set the numbering and header in the respective columns
$worksheet.Cells.Item($rowIndex, 1) = $numbering
$worksheet.Cells.Item($rowIndex, 2) = $header
}
}
# Save the workbook as an Excel file
$workbook.SaveAs('D:\output.xlsx')
# Close the workbook and quit Excel
$workbook.Close()
$excel.Quit()
# Clean up the COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
