Merge two Excel files into one using Powershell

Question

Hi,

I have the following code.

## - Get SQL Server Table data:
$SQLServer = 'server';
$Database = 'db';
$today = (get-date).ToString("dd-MM-yyyy")
$ExportFileHeader = "\\FC Folder\Despatch\Brexit Files\Landmark\ReboundHeader.xlsx"
$ExportFileItem = "\\FC Folder\Despatch\Brexit Files\Landmark\ReboundItem.xlsx"
$ExportFileItem = "\\FC Folder\Despatch\Brexit Files\Landmark\ReboundCombined.xlsx"
##$SqlQuery = @'EXEC [zoomfs].[LandMarkGlobalExport]'@ ;

## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnectionHeader = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnectionItem = New-Object System.Data.SqlClient.SqlConnection;

##Header
$SqlConnectionHeader.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";

##Item
$SqlConnectionItem.ConnectionString = `
"Server = $SQLServer; Database = $Database; Integrated Security = True";

#Header
$SqlCmdHeader = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmdHeader.CommandText = $("EXEC [zoomfs].[ReboundHeader]");
$SqlCmdHeader.Connection = $SqlConnectionHeader;
$SqlCmdHeader.CommandTimeout = 0;

## - Extract and build the SQL data object '$DataSetTableHeader':
$SqlAdapterHeader = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapterHeader.SelectCommand = $SqlCmdHeader;
$DataSetHeader = New-Object System.Data.DataSet;
$SqlAdapterHeader.Fill($DataSetHeader);
$DataSetTableHeader = $DataSetHeader.Tables["Table"];

#Item
$SqlCmdItem = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmdItem.CommandText = $("EXEC [zoomfs].[ReboundItem]");
$SqlCmdItem.Connection = $SqlConnectionItem;
$SqlCmdItem.CommandTimeout = 0;

## - Extract and build the SQL data object '$DataSetTableHeader':
$SqlAdapterItem = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapterItem.SelectCommand = $SqlCmdItem;
$DataSetItem = New-Object System.Data.DataSet;
$SqlAdapterItem.Fill($DataSetItem);
$DataSetTableItem = $DataSetItem.Tables["Table"];

## ---------- Working with Excel ---------- ##

$DataSetTableHeader |   Export-Excel $ExportFileHeader -AutoSize ;
$DataSetTableItem |   Export-Excel $ExportFileItem -AutoSize ;

I am needing to merge the files into one sheet within a document and then sort on a column smallest to largest.
Is this possible in powershell? What would be the best way to go about it.

Something like

#Merging Excel Files together
$excelHeader = Open-ExcelPackage -Path $ExportFileHeader;
$excelItem = Open-ExcelPackage -Path $ExportFileItem;
$ExcelCombined = $excelHeader + $excelItem;

Write-Host $ExcelCombined;

Though that does not work.

Answer 1

Hi @Christopher Jack ,

There are number of solutions and different approaches to achieve that. Here are a couple of references:

Use powershell to merge many excel files as one
https://steemit.com/utopian-io/@cha0s0000/use-powershell-to-merge-many-excel-files-as-one

The following suggestion is based on an external PowerShell moduke, which you can also use. Here the suggestion:

Merge content of multiple Excel files into one using PowerShell
https://stackoverflow.com/questions/55608584/merge-content-of-multiple-excel-files-into-one-using-powershell

and here is the link to the module:

Import EXcel 5.4.2
https://www.powershellgallery.com/packages/ImportExcel/5.4.2/Content/Merge-worksheet.ps1

and last, but not least:

MERGING EXCEL FILES USING POWERSHELL, YES IT CAN BE DONE.
http://www.blogabout.cloud/2019/02/490/

Hope I was able to help!

----------

If the Answer is helpful, please click "Accept Answer" and upvote it.
Regards,
Stoyan

Answer 2

Thank you, some very useful tips in there.