Three years ago, I posted CombineXLSheets.vbs, a vbScript which allows you to drop and drag Excel spreadsheets onto it and have them combined into a single workbook file with multiple worksheets. Now that I am working in PowerShell, I have found the need to consolidate CSV files. Parts of Combine-CSVFilesToWorksheets.ps1 come from code ported from the vbscript, and this script does a similar job.
I have been experimenting with input validation choices, and you can see some of it here. One of the things I learned is that a single parameter can have more than one script validations using ValidateScript. I also decide that the error handling for this is pretty ugly, and moved other input validation into the body of the script.
The script requires that you specify the folder with the CSV files, and the output file for the combined data. By default the worksheets are named with the base name of the CSV file, and autofit is applied to the columns. You may optionally turn off autofit, and can delete all of the CSV files when done or the CSV folder itself. Because this is an advanced function, you can use Get-Help for full help. The script also supports ShouldProcess (-WhatIf ).
This script requires Excel.