Compare 2 excel files and add columns from one excel to other using powershell Import-Excel

Question

I am having below 2 excel files with me

File_1

VM          System / Category   Sub-System V2.0           FULL VM DRP
001_MPEU15  001-Europe          001-011-Mobility-DRP-NP   FULL VM DRP
001_MPEU29  001-Europe          001-011-Mobility-DRP-NP   FULL VM DRP

File_2

VM                  powerState
001_MPEU15          poweredOn
001_MPEU29          poweredOn

Based on the VM, I need to add column from File_1 to File_2

The output I am looking for in File_2 or can be a new file

VM              powerState   System / Category  Sub-System V2.0           FULL VM DRP
001_MPEU15      poweredOn    001-Europe         001-011-Mobility-DRP-NP   FULL VM DRP
001_MPEU29      poweredOn    001-Europe         001-011-Mobility-DRP-NP   FULL VM DRP

I don't have excel installed in the server so using Import-Excel module

Please let me know how can I compare and merge the data like this

The example given in cases are with csv, need the same thing with excel. not sure it works in same way or not

Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\File1.Csv |Join (Import-Csv .\File2.Csv) -on vm |Export-Csv .\Output.Csv` — iRon, Apr 06 '23 at 10:02
The Join-Object is not a standard cmdlet. Use : Foreach($row1 in $File_1) { ForEach($row2 in $File2) { if($row1.VM -eq $row1.VM) { $row2 | Add-Member -NotePropertyName "System / Category" -NotePropertyValue $row1.'System / Category'; $row2 | Add-Member -NotePropertyName "Sub-System V2.0" -NotePropertyValue $row1.'Sub-System V2.0'; $row2 | Add-Member -NotePropertyName "FULL VM DRP" -NotePropertyValue $row1.'FULL VM DRP'; break; } } } — jdweng, Apr 06 '23 at 10:08
@iRon: Thanks for the response but I am working with excel not csv. File_1 is already existing excel file and File_2 is which I am generating. — snehasish nandy, Apr 06 '23 at 10:14
Yes. I've done similar with VBA macros. You need to access each worksheet by cells (row/column). See following : https://woshub.com/read-write-excel-files-powershell/?force_isolation=true — jdweng, Apr 06 '23 at 10:26
@jdweng, correct. The OP might also use any of the other solutions in the refered dupplicate (or listed in duplicates in the answer) or just copy/paste the [source code](https://github.com/iRon7/Join-Object/blob/master/Join.psm1). But the general recommendation is to use a hashtable for performance. @snehasishnandy, does this mean you do have files with an `.xls` or `.xlsx` extension? (that doesn't appear from the question). For this you might use [`New-Object -ComObject "Excel.Application"`](https://stackoverflow.com/q/32020644/1701026) — iRon, Apr 06 '23 at 10:31
@iRon : The OP wants to modify an existing workbook. Not create a new workbook. In this case, creating a hash table then requires putting the hash table back into the worksheet. — jdweng, Apr 06 '23 at 11:29
@snehasishnandy, please let me know if the duplicated question doesn't help you and you like me to reopen the question in which case you will need to add more details (see: [how to ask](https://stackoverflow.com/help/how-to-ask)). In general, for performance and clarity, I recommend you to use a hashtable and pure PowerShell (or not at all, and use macros as @jdweng suggests). To convert `.xlsx` to `.csv` files: `$xl=New-Object -ComObject Excel.Application;$xl.Visible=$false;$wb=$xl.Workbooks.Open("$Path\File1.xlsx");$wb.Sheets(1).SaveAs("$Path\File1.csv",6);$wb.Close($False);$xl.Quit()` — iRon, Apr 06 '23 at 12:07
@iRon: Sure. and as I mentioned in the question, I don't have excel installed in server so can't so use ComObject — snehasish nandy, Apr 06 '23 at 13:08

iRon · Answer 1 · 2023-04-06T16:55:55.803

"I don't have excel installed in server so can't so use ComObject", that doesn't leave much options and changes the focus of the "merging" question considerable to a how to "how to read a .xls file without Excel" question.

Another option that is left is using an OleDbConnection:

function Import-Xls ($Path, $Sheet = 'Sheet1') {
    $Path = Resolve-Path $Path
    $ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Path;Extended Properties=""Excel 12.0;HDR=YES"";Persist Security Info=False"
    $Connection = new-object System.Data.OleDb.OleDbConnection($connectionString)
    $Connection.Open()
    $Command = $Connection.CreateCommand()
    $Command.CommandText = "select * from [$Sheet`$]"
    $Reader = $Command.ExecuteReader()
    $DataTable= new-object System.Data.DataTable
    $DataTable.Load($Reader)
    $Connection.Close()
    $DataTable
}

Than use one of the solutions in the what's the best way to join two tables into one? duplicate:

$Data1 = Import-Xls .\File1.xlsx
$Data2 = Import-Xls .\File2.xlsx
$Data1 |Join $Data2 -on VM |Format-Table

VM         System / Category Sub-System V2#0         FULL VM DRP powerState
--         ----------------- ---------------         ----------- ----------
001_MPEU15 001-Europe        001-011-Mobility-DRP-NP FULL VM DRP poweredOn
001_MPEU29 001-Europe        001-011-Mobility-DRP-NP FULL VM DRP poweredOn

Not sure if you want to write the results back to an Excel file (as @jdweng mentions in the question comments). Anyways, I left it out as this will probably take some more effort. — iRon, Apr 06 '23 at 16:05

Compare 2 excel files and add columns from one excel to other using powershell Import-Excel

1 Answers1