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

Viewing 1 reply thread
  • 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: 2
      Replies: 1736
      Points: 3,551
      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. 😉

Viewing 1 reply thread
  • The topic ‘import the text files into Excel in different sheets’ is closed to new replies.