Combine multiple CSV files by column, not by row

Question

I have ~100 CSV files that I want to combine into one single Excel spreadsheet (everything in one tab, not in separate tabs). These CSV files are identical in format: each contains 4,000 rows and 2 columns with header, about 60 KB in size.

All solutions I've found so far append the next CSV at the end of the last row of the active tab. I haven't seen one that can append new CSVs to the columns immediate to the right of last column.

Here're some examples.

1. DOS copy method: copy *.csv merge.txt

2. This solution. (VBA script.)

3. Using Excel's Data>New Query>From File>From Folder (Excel 2013) .

There are also examples with Windows Powershell scripts to merge CSV files on the internet.

All of the above create a single Excel spreadsheet with about 400,000 rows of data, which is useless to me.

I'd appreciate suggestions to solve this problem. Thanks!

Edit. An easy solution is found: using r's cbind() to combine the data in a data frame and then write it to a csv. The entire process took ~3 seconds. Right tool for the job!

Thanks to everyone for contributing. Cheers,

--tcollar

Answer 1

Not sure about "native" Windows 10, but if you have the Windows Subsystem for Linux installed, you can use the UNIX paste command, which should concatenate the files by columns, in the way you are asking.

Answer 2

Using your third option: 'Using Excel's Data>New Query>From File>From Folder (Excel 2013)'

Click on the transform data option, this opens the power query editor. It will show a list of the files.

Click on the button in the header of the column content to create a function that can transform every individual CSV file before appending them.

Select the right table or worksheet and do the following in the transform sample file function.

1. Insert the column names into the first row by using `use headers as first row'
2. Transpose Table

Then in the query where the function is invoked, the transposed CSV files are appended. Simply transpose the table back to original shape and promote the first row to headers.

Answer 3

Here's the r code to combine csv files by column:

setwd("C:/mycsvdir")
filenames <- list.files(full.name=TRUE)

## read csv, skipping the first two rows of every file
Everything <- lapply(filenames, function(i){read.csv(i, header=TRUE, skip=2)}
combined.df <- do.call(cbind.data.frame, Everything)

write.csv(combined.df, file = "combined.csv", row.names=FALSE)

If you want to combine csv files by row, just replace cbind with rbind.

Edit: About the sequence of merging: my csv files are named like mycsv 001.csv, mycsv 002.csv, etc. They were read in and merged exactly in that sequence, which was great for my purpose.