Importing CSV and other various

Hi all, sorry if this seems a bit long winded...

I am gathering a large amount (up to 1000) of CSV files. I would like to import all of these CSV files into rows in another spreadsheet, which will be a Master. i would like to do this for all 1000~ files with one click of a button (hopefully!)

Now, i'll describe as best i can. the CSV files look like this:

P12345
John Smith
Text Here
Number Here

As you can see, its just in column A, but i would like that data to fill across row 1, from A to D. it sounds simple, but i have figured out it isn't from trying all sorts...

key notes:what i tried to do was write a macro to import it onto one sheet in the workbook, then copy and paste the cell values to a new line on the master sheet. didn't work as well as id hoped.

- all 1000~ files will be stored in the same location and the master .xls will be stored there too.

- i would like it to loop for all files in the directory (so i don't have to click a macro button, select the file, etc. 1000 times)

- the first line in the CSV outputs as P12345, and i need to do a REPLACE function before storing the data in the master sheet (to get rid of the first 3 characters)

- from the 4 fields imported, i will use VLOOKUP's in the other fields to find those bits of data in other sheets and bring them back

- csv files are named as P00000 (letter then 5 numbers, all random numbers, in no particular order)

- any data imported must overwrite existing data (so if the filename P12345 has already been imported, it must overwrite that row in the master sheet.

please see attachment for my template (minus any code, which i desperately need)

thanks for any help, and feel free to grill me for any info needed. i understand this could be a challenge so any help would be brilliant!