Share via

converting text file data to excel using powershell

Prabhasb 1 Reputation point
2021-03-02T09:26:50.863+00:00

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,462 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 34,271 Reputation points Microsoft Vendor
    2021-03-03T06:07:48.027+00:00

    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.

    1 person found this answer helpful.
    0 comments
  2. Rich Matheisen 45,906 Reputation points
    2021-03-02T15:46:18.397+00:00

    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.

    0 comments