How can I merge multiple CSV files into a single Excel worksheet?

[rmbmst1972]

Hello...My name is Robert, and I am trying to discover a way for me to merge multiple CSV files into a single Excel worksheet? I don't want the merged files to appear as tabs on the bottom of the new worksheet. I simply want the data for each file to be stacked on top of each other. For example, Let's say I have the following 5 CSV files:


CSV 1 contains the words blue, orange, red, purple, magenta, white, gray, brown in the first row.
CSV 2 contains the words blue, orange, red, purple, magenta, white, gray, brown in the second row.
CSV 3 contains the words blue, orange, red, purple, magenta, white, gray, brown in the third row.
CSV 4 contains the words blue, orange, red, purple, magenta, white, gray, brown in the fourth row.
CSV 5 contains the words blue, orange, red, purple, magenta, white, gray, brown in the fifth row.


I want the merged file to look like the following:


blue orange red purple magenta white gray brown
blue orange red purple magenta white gray brown
blue orange red purple magenta white gray brown
blue orange red purple magenta white gray brown
blue orange red purple magenta white gray brown

 

[Bratamoli]

I apologize if this is a silly question.
Is it practical to just open the files and do a quick copy-paste?

Very Respectfully,
Bratamoli

PS - Would the new file need to consistently update based on the old ones, or would the old files be garbage once the new file is created?

 

[rmbmst1972]

I am a school teacher who has a test-generating program that exports assessment results data to CSV files. I teach five classes, and there is a lot of data to be displayed for each of my almost 140 students. I don't mind copying and pasting. However, I'd rather copy-paste one file for all of my 5 classes versus copying and pasting each of my 5 classes. I will be less annoyed with having a VBA that will produce my desired results with only having to perform 1 copy-paste for all of my 5 classes at the same time.

 

[rmbmst1972]

I apologize if this is a silly question.
Is it practical to just open the files and do a quick copy-paste?

Very Respectfully,
Bratamoli

PS - Would the new file need to consistently update based on the old ones, or would the old files be garbage once the new file is created?

Bratamoli...I almost forgot to mention to you the old files will be garbage once the newly merged file is created.

Robert

 

[Bratamoli]

You could put this in cell A1 of the finished product:

=IF(NOT(ISBLANK([Book1]Sheet1!A1)),[Book1]Sheet1!A1,IF(NOT(ISBLANK([Book2]Sheet1!A1)),[Book2]Sheet1!A1,IF(NOT(ISBLANK([Book3]Sheet1!A1)),[Book3]Sheet1!A1,IF(NOT(ISBLANK([Book4]Sheet1!A1)),[Book4]Sheet1!A1,IF(NOT(ISBLANK([Book5]Sheet1!A1)),[Book5]Sheet1!A1,"")))))

Then drag it across the top row as far as you need to, then down however many rows you need.

It basically says for each of the 5 workbooks (in order): "If this cell is not blank, use whatever is there; otherwise, check the next workbook. If this cell is blank in all 5 workbooks, leave it blank here."
The only issue I see arising would be if the same cell of two different workbooks both have something in it, then one of the two data-points will be lost.

[Edit]
Oh! Another possible issue. I do not know if all the data in each workbook is on one sheet or if each workbook has multiple sheets.
Also, after your done, I would suggest copying the entire sheet and pasting values. (or possibly something about [F9] might do that or something, but I'm not very clear on that one; I just do the copy/paste-values thing any time I need results to be static)
[End Edit]

Do you think this works for what you need it for?
If not, let me know what you think would go wrong with it, and I will try to update it...