2

Can somebody please help me to resolve this. This is my requirement:

I have few excel files (It will vary) in a folder D:\Script\Test

The name of the files are ExcelA, ExcelB, ExcelC etc

It has worksheets like this: A1 (for ExcelA), B1(for ExcelB), C1(for ExcelC) etc

Here I want to merge all those different excel sheets to one excel sheets

(Lets say the merge file name is 'Final.xlsx')

Condition: Instead of getting the worksheets as its actual names (A1, B1, C1), I want to get it updated with excel file names.

Meaning : Final.xlsx should have merged 3 worksheets with the names: ExcelA, ExcelB, ExcelC

AllenBooTung
  • 1,349
Vimal VV
  • 21

1 Answers1

1
  1. Run powershell / PowerShell ISE as an administrator
  2. Run the following commands,
    • Install-Module ImportExcel (and follow the prompts)
    • Import-Module ImportExcel
  3. Create a script (e.g. MergeExcelWorkbooks.ps1)
    • The below will help you merge 1 sheet each from multiple workbooks and, per requirement, the merged workbook (Final.xlsx) sheet names are workbook names of the source excel files.

Script:

$sourceFolderPath = "D:\Script\Test"

$OutputFilePath = "D:\Script\Test\Final.xlsx"


$XLfiles = Get-ChildItem $sourceFolderPath -Filter *.xlsx


foreach ($XLfile in $XLfiles) {


<# Hints,
- If there is only 1 sheet or if you want to import data from the 1st sheet (ordinal, i.e index 0) in the Excel file the below would work
- Else please use the 'WorkSheetName' parameter to specify the sheet name to import from
- Use the NoHeader switch of Import-Excel if the source Excel sheets do not contain a header; HeaderName parameter can be used in combination with NoHeader to specify a custom header name
#>
Import-Excel $XLfile.FullName | Export-Excel $OutputFilePath -WorksheetName $XLfile.BaseName    




}

In the above script,

  1. $XLfile.FullName returns the full filepath of the source $XLfile
  2. $XLfile.BaseName returns the name of the current excel file (without the extension)

Please try it out and let me know in the comments if it worked for you or if you have a different scenario.

EDIT In order to import from a specific sheet use the -WorkSheetName parameter of the Import-Excel command like below,

Import-Excel $XLfile.FullName -WorksheetName 'Snapshots'| Export-Excel $OutputFilePath -WorksheetName $XLfile.BaseName
Karthick Ganesan
  • 247