5 Replies Latest reply on Sep 29, 2020 10:44 AM by LucD

    Merging Excel file

    Vimal348 Novice

      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.

        • 1. Re: Merging Excel file
          LucD Guru
          User ModeratorsvExpertCommunity Warriors

          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?

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Merging Excel file
            Vimal348 Novice

            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.

            • 3. Re: Merging Excel file
              LucD Guru
              User ModeratorsvExpertCommunity Warriors

              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.

              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Merging Excel file
                Vimal348 Novice

                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

                • 5. Re: Merging Excel file
                  LucD Guru
                  vExpertUser ModeratorsCommunity Warriors

                  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

                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz