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.