Importing multiple csv files using the csv filename as the worksheet name

I need to import multiple csv files into a single workbook with the data as a table using the first row as the headers. I can do this individually using data>from text/csv but I have over one hundred to import. I would also like that the created worksheets have the tab name the same as the csv filename. I can do a multiple query using get data from file but again this just gives me a list sheet1 - sheet127 which gives me no idea what data is in what sheet.
Is this a wish too far? I would be happy to use a macro if this is what is required.
My source for the get data from file option is a workbook with all the csv files imported to named tabs using ablebits tool (data is not a table) but I would be just as happy to be able to achieve this from the csv files directly.

Don't use the From Text/csv option, use the From Folder option to combine csv files into one sheet.

Thanks guys but both your suggestions are for combining multiple files into a single worksheet.
I need a separate sheet for each file named after the source file not including the extension.

for example for files a.csv to z.csv I need a workbook with tabs named a to z with the data in table format first row as header.

each csv file is an extract from a database table (no I do not have access to the actual db that it came from) and thus has a different set of data and a different number of columns and rows.