Consolidate excel workbooks data to csv file from folder using power shell

Question

In a folder i have around 20 excel workbooks,each workbook having MIS for upload excel sheet i want to consolidate all data from each workbook from MIS for upload excel sheet to new csv file using powershell

below is the code which i have tried.But i want Browse for a Folder method.

#Get a list of files to copy from
$Files = GCI 'C:\Users\r.shishodia\Desktop\May 2018' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName

#Launch Excel, and make it do as its told (supress confirmations)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

#Open up a new workbook
$Dest = $Excel.Workbooks.Add()

#Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
ForEach($File in $Files[0..20]){
    $Source = $Excel.Workbooks.Open($File,$true,$true)
    If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
        $Source.WorkSheets.item("MIS for Upload").Activate()
        [void]$source.ActiveSheet.Range("A1","R$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A1").Select()
        }Else{ #If there is data go to the next empty row and select Column A
        $Source.WorkSheets.item("MIS for Upload").Activate()
        [void]$source.ActiveSheet.Range("A2","R$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
    }
    [void]$Dest.ActiveSheet.Paste()
    $Source.Close()
}
$Dest.SaveAs("C:\Users\r.shishodia\Desktop\Book2.xlsx",51)
$Dest.close()
$Excel.Quit()

Could you please show what you already tried and share your code? — Robert Dyjas

Robert Dyjas Robert Dyjas · Accepted Answer · 2018-06-14T09:22:12

For this purpose you could use ImportExcel module - installation guide included in repo README.

Once you install this module you can easily use Import-Excel cmdlet like this:

$Files = GCI 'C:\Users\r.shishodia\Desktop\May 2018' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName
$Temp = @()
ForEach ($File in $Files[0..20]) { # or 19 if you want to have exactly 20 files imported
  $Temp += Import-Excel -Path $File -WorksheetName 'MIS for Upload' `
  | Select Property0, Property1, Property2, Property3, Property4, Property5
}

To export (you wrote CSV but your destination file format says xlsx):

$Temp | Export-Excel 'C:\Users\r.shishodia\Desktop\Book2.xlsx'

or

$Temp | Export-Csv 'C:\Users\r.shishodia\Desktop\Book2.csv'

Consolidate excel workbooks data to csv file from folder using power shell

2 Answers