import the text files into Excel in different sheets

Welcome Forums General PowerShell Q&A import the text files into Excel in different sheets

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #171568

    Participant
    Topics: 1
    Replies: 0
    Points: 11
    Rank: Member

    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()
  • #171649

    Participant
    Topics: 1
    Replies: 1530
    Points: 2,591
    Helping Hand
    Rank: Community Hero

    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. 😉

You must be logged in to reply to this topic.