The simplest way is to record a macro and DO IT!
You'll see that when you open the CSV file, it will
open in a new workbook, with the sheet  name the same
as the file  name. Rename the sheet, then move it to
your workbook. The macro should work the same.
You might need to write a code snippet to
look to see if the sheet name exists first and delete it.

let me know if you need more help.