Forum Discussion
Combining Text Files Into Single CSV
Hi, I have a business situation where I have to combine several TXT files into one CSV. I'm looking for a way to automate the process of combining them as much as possible, even if it means a 3rd party batch file or something. The text files are essentially already csv files but someone is saving them wrong and there's nothing I can do about that. I'm using Office 16. The details are important so here they are:
When you open each text file with Excel, they all have the same columns and first row, which is a header. It looks something like:
important# | date | etc
123 | 12/25/18 | etc
345 | 12/25/18 | etc
Usually what I do is open the first text file up in Excel using the "Open With" right-click option, then "Save As" a CSV with a new name, then individually open each file and copy/paste the rows with content one after another into the first one. The result is a CSV with all the rows filled out and one header row.
Does anyone know of a way to combine these files together into one CSV (for example, all the files in a given directory) with just a few clicks? I presume this would involve scripting but maybe someone has already written such a thing and would be willing to share it?
I have no idea how to write such a script, but I know what it would do: for a given group of files, pull the data from all rows on the first one and put it in a new Excel file, then for all files after, pull the data below row 1 and append it to the first one. Would anyone be kind enough to have a crack at it?
I'm attaching a sample text file for example, however I had to change it to a CSV in order for the forum to allow me to upload it. Also the data is changed for privacy.
Thank you for your help!
(edited to add version info)
Hi Nathan,
Are you on Windows or on Mac?
Assuming all you csv file are in one folder for Windows you may use command line like
copy *.csv output.csv
to combine all files. The only point first row of each file will be kept within the resulting file, but most probably you may find more complicated command to resolve that.
I believe something similar shall be for Mac, that is Unix after all.
For Windows you may use Power Query to combine and transform, if necessary, all your files in one resulting Excel table, copy of that resulting file you may save as csv. See, for example, https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder.
Not sure if Power Query is available for Mac now.
Hi Nathan,
Are you on Windows or on Mac?
Assuming all you csv file are in one folder for Windows you may use command line like
copy *.csv output.csv
to combine all files. The only point first row of each file will be kept within the resulting file, but most probably you may find more complicated command to resolve that.
I believe something similar shall be for Mac, that is Unix after all.
For Windows you may use Power Query to combine and transform, if necessary, all your files in one resulting Excel table, copy of that resulting file you may save as csv. See, for example, https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder.
Not sure if Power Query is available for Mac now.
Hi Sergei,
Thank you for that, I'm on PC and Power Query does pretty much what I need as it turns out. Much simpler than I imagined.
Thanks again for your help!
