Convert Text to Excel using PowerShell

Posted on Updated on

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
$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"
$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.Rows.Item(1).Font.Bold = $True
$xl.Rows.Item(1).Interior.ColorIndex = 44
#Save Sheet
#Clean up
#Remove-Variable -Name xl

3. Output should be something like this: