converting text file data to excel using powershell

Question

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 resutls

---

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()
$xl.Quit()

# Clean up

Answer 1

Leaving off the "convert to Excel" you can deal with your pseudo-csv file and create an array of PSCustomObjects like this:

$x2 = Get-Content C:\junk\x2.txt |
    ForEach-Object{
        if ($_.length){
            $_
        }
    } | convertfrom-csv -Delimiter " " 

It eliminates the empty rows from your set of example data (none of which had more than one space between columns, BTW). You can easily export the $x2 array to a ExportTo-CSV cmdlet, or you can eliminate the "$x2 =" on the 1sr line and add " | Export-CSV filename.csv -Notypeinformation " at the end of the last line and then just import the resulting CSV into Excel.

Answer 2

Hi,

Why not convert it to a csv file? It's much easier.

$source = "C:\Users\rnoy\Parse-html\inputfile.txt"  
$destination = "C:\Users\rnoy\Parse-html\outputfile.csv"  
Get-Content -Path $source | ForEach-Object {  
    ($_ -split " " | Where-Object { $_ }) -join ","  
} | ConvertFrom-Csv | Export-Csv -NoTypeInformation -Path $destination  

If you do need an xlsx file you can convert the csv file like this

$xlsxFile = "C:\Users\rnoy\Parse-html\outputfile.xlsx"  
$xlFileFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault  
$excel = New-Object -ComObject "Excel.Application"  
$workbook = $excel.Workbooks.Open($destination)  
$workbook.SaveAs($xlsxFile,$xlFileFormat)  
$workbook.Close()  
$excel.Quit()               
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.