2
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.
DOS copy method: copy *.csv merge.txt
This solution. (VBA script.)
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
tcollar - If I had time to play with it more I would to confirm the exact syntax and to keep it as simple as possible logic wise but I don't right now. Look over this post and play with some of these variations of the PowerShell logic.... https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ebadf6e6-2efc-461f-8aca-fe1fba7bd1cc/unix-pasting-in-powershell?forum=ITCG
– Pimp Juice IT – 2019-09-24T01:34:02.367I haven't seen one that can append new CSVs to the columns immediate to the right of last column. Consolidate data in multiple worksheets – Akina – 2019-09-24T05:11:37.373
Your issue is contradicts your need,,, you got solution and unable to alter it as your need,,, I think your basic need is append CSV file data side by side !! – Rajesh S – 2019-09-24T07:18:41.900
This VBA code works,,
nextColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column + 1
– Rajesh S – 2019-09-24T08:04:26.063https://support.office.com/en-us/article/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0a8d – Lee – 2019-09-24T08:50:27.977
tcollar - Go ahead and add your answer as an actual answer with examples, detail, reference, and so forth to ensure it's clear what answer worked for you. Yes, you are just fine to add an answer to your own question, so go for it if you are saying you found an answer yourself that worked. – Pimp Juice IT – 2019-09-24T23:13:43.460