Merge two Excel files into one using Powershell

Christopher Jack 1,616 Reputation points
2021-02-18T11:16:24.283+00:00

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.

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,448 questions
0 comments No comments
{count} votes

Accepted answer
  1. SChalakov 10,371 Reputation points MVP
    2021-02-18T13:48:15.967+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Christopher Jack 1,616 Reputation points
    2021-02-18T14:24:08.383+00:00

    Thank you, some very useful tips in there.