I would first determine what you need to do with the data and if this is a long term solution (ie you are going to need to do this import of data on a regular basis) or a one-off solution (ie once you merge the data, you will never need to merge the data again). You also need to determine what tool you are going to be using to review the data. Are you wanting to write TSQL to pull data out and review it that way? What about SSRS? Or are you looking to dump the data back into Excel in the end to review and report on?
Getting the data is only half the battle, but it is an important part of the battle because whatever tool you use for doing the analytics after will perform best if the data is in a single location. What I mean is if, for example, you wanted to pull the data into Tableau, that supports pulling data from Excel into it. But if you are pulling from 200 Excel files, it is not going to perform nicely and it will likely eat up a lot of memory. Plus, tableau is not a free reporting tool (note I do not work for Tableau or any of their parent companies, I just recently demoed the tool so it is fresh on my mind).
If it is long term, I would be looking at migrating the data to a database of some sort. Or, at the very least, automating the process of pulling data from the xlsx files and csv files into a single file.
If this is a one-off thing, I'd either manually merge the data (time consuming, but if it is a one off thing, it may be faster and cheaper than learning/buying a new tool) or look for some tools to merge spreadsheets into 1 with multiple workbooks or, if the data is similar enough, a single workbook in excel. Once all of the data is in one excel workbook, then you can manipulate it as you see fit.
If you have anyone on your team with scripting experience (VB Script or powershell for example), or coding experience (pretty much any .NET language like C#) then pulling the multiple XLSX files or multiple CSV files into 1 shouldn't be too hard to do. At the end of this process, your 200 files become 2 files. At this point, it is easy enough to open them both in Excel and copy-paste between the 2 to get 1 excel file to work with.