All my csv files have column headers. Some of them have same column header names too. Also i don't want the data to be collated one below the other. They should be one beside other. So if i open the excel sheet, all the data from all the csv files should be aligned one beside other.

My basic idea is, open all the csv files, activate one of the Csv worksheet, copy from starting cell (a1) to ending of sheet.now activate excel and paste them in sheet1. Now increase the offset to next line. Now activate other csv worksheet and do the same as the first and so on.
Note: As we know, the csv files will have only one sheet when opened. Also the sheet name will be same as csv file's name.

-Dhiraj