locked
Extracting data from xlsx with multiple sheets to one sheet RRS feed

  • Question

  • Hello,

    I have been trying in numberous ways to extract data out of an excel file 
    This data is stored as follows: 

    Starting at Row 2 till x-numberous lines (ending with empty field)

    Address 1 | Address 2 | Milage | Empty
    Address 1 | Address 2 | Empty | Milage
    Address 1 | Address 2 | Milage | Empty
    Address 1 | Address 2 | Empty | Milage
    Address 1 | Address 2 | Milage | Empty
    Address 1 | Address 2 | Empty | Milage
    Script should end here and go to next sheet
    
    
    Empty | Empty | TOTAL: | TOTAL: 
    This should not be included in the ending result


    What I need to achieve is to extract the 2nd, 3rd, 4th and 5th column of each sheet (some files contain over 99 sheets..)  to place them in a new excel file. 

    Till now I tried extracting the columns for one sheet, which worked so far, tho I'm kinda stuck in how to continue the rest of the code, as in looping through all the other sheets and not continueing after an empty field. 

    Oudedijk, 9130 Beveren (Belgium)
    B-9060 Zelzate, Burgemeester Jos Chalmetlaan 51
    1 Rue Fernand Raynaud, 91100 Corbeil-Essonnes (France)
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    Total km  *0,93


    As you can see, it contains a lot of empty lines.. 

    What the endfile should be looking like: 

    Address 1 | Address 2 | | milage
    Address 1 | Address 2 | milage | 


    But then populated with all the adresses from all the sheets in column a, column b 
    I don't know if it has any link to inform that they'll always be on the same line, as; 

    1st column = startaddress
    2nd column = endaddress
    3rd & 4th column = milage

    This is the code I got so far: 

    $FilePath = "D:\test\Test2\1.xlsx"
    $SheetName = "1"
    $startrow = 2
    
    $Excel = New-Excel -Path $FilePath
    $WorkSheet = $Excel | Get-Worksheet -Name $SheetName
    $Excel
    
    #$Imported = Import-XLSX -Path $FilePath -Header samaccountname, EID, Date
    
    $objExcel = New-Object -ComObject Excel.Application
    $objExcel.Visible = $false
    $WorkBook = $objExcel.Workbooks.Open($FilePath)
    $WorkSheet = $WorkBook.sheets.item($SheetName)
    
    #$WorkBook.sheets | Select-Object -Property Index
    $WorkSheet = $WorkBook.sheets.item("2")
    
    $froms = @()
    $tos = @()
    $geladen = @()
    $leeg = @()
    
    $count = $Worksheet.Cells.Item(65536,2).End(-4162)
    
    for($startRow=2; $startRow -le $count.row; $startRow++)
    {
     $froms   += $Worksheet.Cells.Item($startRow, 2).Value()
     $tos     += $WorkSheet.Cells.Item($startRow, 3).Value()
     $geladen += $WorkSheet.Cells.Item($startRow, 4).Value()
     $leeg    += $WorkSheet.Cells.Item($startRow, 5).Value()
    }
    foreach($item in $froms) {
     Write-Host $item
    }
    foreach($item in $tos) {
     Write-Host $item
    }
    foreach($item in $geladen) {
     Write-Host $geladen
    }
    foreach($item in $leeg) {
     Write-Host $leeg
    }
    
    $objExcel.quit()
    
    

    I'm kinda new to PowerShell and tried a lot with online searches to gather as much info as I could
    If someone would be able to help, it would be greatly appreaciated. 
    Eagering to learn more of this!

    Thanks in advance


    Friday, April 13, 2018 5:15 PM

All replies

  • Please ask a single question about a single thing you need help with.  We cannot redesign your code or understand what you are trying to do.

    You will also take some time to learn how to use the Excel object model.


    \_(ツ)_/

    Friday, April 13, 2018 5:25 PM
  • Hello jrv,

    What I'm trying to do is to extract 4 columns out of one excelfile in multiple sheets. 
    This with the purpose on writing them into a new file, so the ending file would not have 99+ sheets, only one. 

    That would be my greatest help in going forward with my coding

    Friday, April 13, 2018 5:34 PM
  • You need to create a loop that cycles through all of the sheets and extracts the data.

    foreach($sheet in $wb.Worksheets{
          # output rows to a file
    }


    \_(ツ)_/


    • Edited by jrv Friday, April 13, 2018 5:38 PM
    Friday, April 13, 2018 5:38 PM
  • You have also copied and pasted incompatible methods.  Choose to use either the Excel object API or the Excel Module.  Do not try to mix them until you better understand PowerShell and Excel.


    \_(ツ)_/

    Friday, April 13, 2018 5:40 PM