Merging two Excels where one is blank

[jctmetal1337]

I am having an issue when trying to merge two Excels. I have File1 that has a header and data and File2 that has a header and no data.

When my script runs, I get the following warning "WARNING: Worksheet '' in workbook 'C:\temp\ManipulationTesting\test\File2.xlsx' contains no data in the rows after top row '1'". The script then produces a file with two worksheets, the first worksheet contains everything from File1, but the second worksheet is blank. What I expect to happen is the second worksheet only contains the header from File2.

I am obviously missing something in my merge script, but I cannot see what it is. What can I add to ensure that if an excel is blank, that row 1 will come over?

As a note, File2 may or may not contain data. If I were to run my script to with File2 containing data on row 2, then everything works as I expect it to.

$sourceFolderPath = "C:\temp\ManipulationTesting\test"
$OutputFilePath = "C:\temp\ManipulationTesting\test\Final.xlsx"
$SheetNames = 'Sheet1','Sheet2'
$XLfiles = Get-ChildItem $sourceFolderPath -Filter *.xlsx

$i = 0
foreach ($XLfile in $XLfiles) {
Import-Excel $XLfile.FullName | Export-Excel $OutputFilePath -WorksheetName $SheetNames[$i]
$i++
}

return "Excel Manipulation Complete"