Converting text file data to excel

Hi,

I want to convert a text file data to excel, below is the content of the text file:

ModuleName FailedCount SucceededCount TotalCount

BESS 0 80 80

IG 0 14 14

the spaces between the words in the the text file are not fixed, so I how do I convert this data to excel? Below is the PowerShell script i tried, but it is not giving correct results

#Constants
$SourcePath = "C:\Users\rnoy\Parse-html\inputfile.txt"
$DestinationPath = "C:\Users\rnoy\Parse-html\outputfile"
$deleimter= "`t"; #You can replace it by any other delimeter ';' or '/'#You can leave this part as it is
$SourceTxt = Get-Content $SourcePath
$xlsxFile = $DestinationPath + ".xlsx"
if (Test-Path ($xlsxFile))
{
Remove-Item $xlsxFile
}
#$SourceTxt >> $txtFile
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl=New-Object -ComObject "Excel.Application"
$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet
$xl.Visible=$True
$cells=$ws.Cells
$Content = Get-Content $SourcePath
$numOfRows = $Content.Length
$numOfColumns = $Content[0].split($deleimter).Count
for ($i=0; $i -lt $numOfRows ;$i++)
{
$rowData = $Content[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns; $j++)
{
$cellData = $rowData[$j]
$cells.item($i+1,$j+1) = $cellData
}
}
#Apply some Format for Excel header
$xl.Cells.EntireColumn.AutoFit()
$xl.Cells.EntireColumn.AutoFilter()
$xl.Rows.Item(1).Font.Bold = $True
$xl.Rows.Item(1).Interior.ColorIndex = 44
#Save Sheet
$wb.SaveAs($xlsxFile)
$wb.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
$xl.Quit()
#Clean up
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

When you crosspost the same question at the same time to different forums you should at least post links to the other forums along with your question to avoid people willing to help you making their work twice or more.

Thanks in advance

And please format your code as code. here you can learn how that works:

1 Like

This can be easily parsed with ConvertFrom-Csv with a space delimiter and exported to CSV:

$mockFile = @"
ModuleName FailedCount SucceededCount TotalCount
BESS 0 80 80
IG 0 14 14
"@ | ConvertFrom-Csv -Delimiter ' '

$mockFile | Export-Csv -Path C:\Scripts\MyOutput.csv -NoTypeInformation

Output:

ModuleName FailedCount SucceededCount TotalCount
---------- ----------- -------------- ----------
BESS       0           80             80
IG         0           14             14