Excel.Application
Convert Text to Excel using PowerShell
In this post, we will convert text file to Excel using PowerShell.
Pre-requisite : you should have Microsoft Office Excel installed on your machine
1. This is a sample of the data to be converted, If you want to try it your self please Copy paste the below box to a file named sourceFile.txt
2. Now, open the PowerShell_ISE and paste the below script in it
#This script is to convert text files to formalized excel sheet.
#Please make sure txt file is applied in this format
#Header1, Header2, Header3, Header4
#X1, X2, X3, X4
#Y1, Y2, Y3, Y4
#Z1, Z2, Z3, Z4
#Constants
$SourcePath = "C:\temp\sourceFile.txt"
$DestinationPath = "C:\temp\destinationPath"
$deleimter= ","; #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)
#Remove-Variable -Name xl
#[gc]::Collect()
#[gc]::WaitForPendingFinalizers()
3. Output should be something like this:
Tech Gang 