SQL Union in MS Excel 2007

Question

I have a number of CSV files of data that I want to Union together into a single table in MS Excel. All the CSV files have the same names and number of columns.

In a relational database like Access or SQL I could use a Union Query, but this has to be in Excel. How can I quickly merge all of these files into one in Excel?

Answer 1

You can write a macro in VBA to handle doing this.

Just have something that imports the CSV file into a temporary worksheet, then cuts and pastes it into place below the last final worksheet values, and deletes the newly imported, temporary worksheet.

Answer 2

Create a batch file with following text

@echo off
for %%1 in (*.csv) do findstr /v "a,b,c" %%1

Here "a,b,c" is the column headers.

After the batch file is created (assume the name of the batch file is test.bat), you could run the following on Command Prompt.

test.bat > combined.txt

This runs the batch file & pipes the output to a new file (combined.txt).
You will have to open the combined.txt file and paste the header ("a,b,c") at the top.

Hope this helps.

Answer 3

you can try a simple DOS command:

copy file1.csv + file2.csv newfile.csv