Merging Excel file

[Vimal348]

Hello,

Can someone please tell me how

I am using the below script to merge excel file, got from LucD​

script is working for the first 10 excel files but not working for another set.

$sourceFolderPath = "D:\RVTool\Consolidated\2"

$OutputFilePath = "D:\RVTool\Consolidated.xlsx"

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

foreach ($XLfile in $XLfiles) {

       Import-Excel $XLfile.FullName | Export-Excel $OutputFilePath -WorksheetName $XLfile.BaseName 

}

Set-PSRepository cmdlet

This is the error I am getting :

Failed importing the Excel workbook 'D:\RVTool\Consolidated\2\Test.xlsx' with worksheet '': Duplicate column headers found on row '1' in columns '70 71'. Column headers must be unique, if this

is not a requirement please use the '-NoHeader' or '-HeaderName' parameter.

At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.1.1\Public\Import-Excel.ps1:216 char:21

+ ...     catch { throw "Failed importing the Excel workbook '$Path' with w ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OperationStopped: (Failed importin...ame' parameter.:String) [], RuntimeException

    + FullyQualifiedErrorId : Failed importing the Excel workbook 'D:\RVTool\Consolidated\2\Test.xlsx' with worksheet '': Duplicate column headers found on row '1' in columns '70 71'. Column he

   aders must be unique, if this is not a requirement please use the '-NoHeader' or '-HeaderName' parameter.

[LucD]

I think the error message is quite clear, you have apparently two columns with the same name.

Did you check D:\RVTool\Consolidated\2\Test.xlsx?

[Vimal348]

Hello LucD​

Yes you are right, there are 2 columns that has the same name (Name and name).

This is an RVTool report generated automatically for many vCenters (More than 30 in weekly basis). So deleting one column is really time consuming. Hence is there any other option we can mention in the script to overcome this issue?

(I see it is mentioned : aders must be unique, if this is not a requirement please use the '-NoHeader' or '-HeaderName' parameter.) But I am not sure what is that.

[LucD]

The headers in the spreadsheet will be read as another row.

For example

Col1 Col2 Col2

---- ---- ----

   1    2    3

   4    5    6

will be read as

P1   P2   P3 

--   --   -- 

Col1 Col2 Col2

1    2    3  

4    5    6

If you know the name of the column that is duplicated and you know which one to leave out, you can script that.

[Vimal348]

Thats works,

Like I mentioned, I have two columns 'name' and 'Name', which I dont really care about.

So if we can eliminate those 2 columns and merge that would be great.

Note: Those two columns are not generating for all the reports, but yes for few reports

[LucD]

This should eliminate both duplicate columns.

$inXlsx = 'C:\Temp\test.xlsx'

$outXlsx = 'C:\Temp\test2.xlsx'

$data = Import-Excel -Path $inXlsx -NoHeader

$notExcluded = $data | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name |

ForEach-Object -Process {

  $data[0]."$_"

} | Group-Object | where { $_.Group.Count -eq 1 } |

Select-Object -ExpandProperty Name

Import-Excel -Path $inXlsx -HeaderName $notExcluded -StartRow 2 |

  Export-Excel -Path $outXlsx -WorksheetName Test -ClearSheet