import the text files into Excel in different sheets

PowerShell Help
1

Hi, I have three different text files. I need to import the text files into Excel in different sheets and need to calculate the number of rows and Sum of values in column L.
Can anyone please help me to on this?

text file1.txt -> sheet1
text file2.txt -> sheet2
text file.txt -> sheet3

File content:

644NORM4,09/25/2018,Desiree,Cuevas,73183 Sunnyvale Dr,,Twentynine Palms,CA,92277,US ,57180194,19.99,0,5393149,,09/25/2018,485432978X,ROCK HALL IN CONCERT 12DVD HOST SET,,4,1,en,

644NORM4,09/28/2018,Billie,Hudson,207 Highview Tr.,,Boyd,TX,76023,US ,57190814,19.99,0,5394137,,09/28/2018,448532416X,THE BEST OF THE CAROL BURNETT SHOW 11DVD,,4,1,en,

644NORM4,10/01/2018,Jennet,Joyce,520 Meadowlawn Dr,,Franklin,KY,42134,US ,57197570,29.99,0,5347100,,10/01/2018,445631968D,POWER OF LOVE 9CD SLIPCASE SET,,4,1,en,

Expected Result for each file:
Number of Records in file#-
Sum of value in file#-

I have tried with below code but it got failed for Single line text files.

$excel = New-Object -ComObject Excel.Application
$excel.visible = $False
$Excel.Workbooks.Add()
$Workbook = $Excel.Workbooks.Item(1)
$Worksheet = $Workbook.Worksheets.Item("sheet1")
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "lpg"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "lif"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "dha"
$Today = Get-Date -Format yyyy_MM_dd_hhmmss
$excel.DisplayAlerts = 'False'

$SourcePath1 = "C:\Users\pvaradharaj\Desktop\SEL_TEMP\SEL_TEMP_LPG\*.txt"
$SourcePath2 = "C:\Users\pvaradharaj\Desktop\SEL_TEMP\SEL_TEMP_LIF\*.txt"
$SourcePath3 = "C:\Users\pvaradharaj\Desktop\SEL_TEMP\SEL_TEMP_DHA\*.txt"
#sheet1
$ws = $Workbook.WorkSheets.item("lpg")
$ws.activate()
$cells=$ws.Cells
$Content3 = Get-Content $SourcePath1
$numOfRows3 = $Content3.Length
$numOfColumns3 = $Content3[0].split($deleimter).Count
$numOfColumns9 = $Content3.split($deleimter)
for ($i=0; $i -lt $numOfRows3 ;$i++)
{
$rowData3 = $Content3[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns3; $j++)
{
$cellData3 = $rowData3[$j]
$cells.item($i+1,$j+1) = $cellData3
}
}

#sheet2
$wt = $Workbook.WorkSheets.item("lif")
$wt.activate()

$cells1=$wt.Cells
$Content1 = Get-Content $SourcePath2
$numOfRows1 = $Content1.Length
$numOfColumns1 = $Content1[0].split($deleimter).Count
for ($i=0; $i -lt $numOfRows1 ;$i++)
{
$rowData1 = $Content1[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns1; $j++)
{
$cellData1 = $rowData1[$j]
$cells1.item($i+1,$j+1) = $cellData1
}
}

#sheet3
$wr = $Workbook.WorkSheets.item("dha")
$wr.activate()

$cells2=$wr.Cells
$Content2 = Get-Content $SourcePath3
$numOfRows2 = $Content2.Length
$numOfColumns2 = $Content2[0].split($deleimter).Count
for ($i=0; $i -lt $numOfRows2 ;$i++)
{
$rowData2 = $Content2[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns2; $j++)
{
$cellData2 = $rowData2[$j]
$cells2.item($i+1,$j+1) = $cellData2
}
}

$row1 = $wt.UsedRange.rows.Count
$Sumrow1 = $row1 + 1
$r2 = $wt.Range("L1:L$row1")
$functions = $excel.WorkSheetfunction
$wt.cells.item($Sumrow1,1) = "Total"
$wt.cells.item($Sumrow1,2) = $functions.sum($r2)
$Temp = $wt.cells.item($Sumrow1,2)
$Total1 = $Temp.text

$row2 = $ws.UsedRange.rows.Count

$Sumrow2 = $row2 + 2
$r3 = $ws.Range("L1:L$row2")
$functions = $excel.WorkSheetfunction
$ws.cells.item($Sumrow2,1) = "Total"
$ws.cells.item($Sumrow2,2) = $functions.sum($r3)
$Temp1 = $ws.cells.item($Sumrow2,2)
$Total2 = $Temp1.text

$row3 = $wr.UsedRange.rows.Count
$Sumrow3 = $row3 + 1
$r4 = $wr.Range("L1:L$row3")
$functions = $excel.WorkSheetfunction
$wr.cells.item($Sumrow3,1) = "Total"
$wr.cells.item($Sumrow3,2) = $functions.sum($r4)
$Temp3 = $wr.cells.item($Sumrow3,2)
$Total3 = $Temp3.text

#Apply some Format for Excel header
$excel.Cells.EntireColumn.AutoFit()
$excel.Cells.EntireColumn.AutoFilter()

$workbook.SaveAs("C:\Users\pvaradharaj\SEL_TEMP\DataSheet_$Today.xlsx")

Write-host "Total Count in lif" $row1
Write-host "Total value in lif" $Total1

Write-host "Total Count in lpg" $row2
Write-host "Total value in lpg" $Total2

Write-host "Total Count in dha" $row3
Write-host "Total value in dha" $Total3

$excel.DisplayAlerts = 'False'
$excel.Quit()
2

Did you consider using the great module from Doug Finke ImportExcel? You can create and work with Excel files even without the need of an existing Excel installation. I think it would make your life much easier. :wink: