Better than Sex??

Today, I got back in touch with my inner geek to solve a problem that has been bothering me for weeks. The answer turned out to be so simple, so easy, that I was surprised I didn't think of it before. It does require thinking outside the proverbial box, and finding an alternate way to solve a problem. After it worked, I must say it beats ice cream and cake. Better than sex? Perhaps.

Here is the problem. I daily get a file of sales on the product I manage for my employer. This comes out of our data warehouse, and comes in the form of a text file with comma separated values. Every day, I get the previous day's sales. So over time, I accumulate many files with the same structure, but containing sales from different days. What I need is a way to easily catch up on combining these into a single file, and being able to maintain it on an ongoing basis.

To give you an idea of what I am talking about, I made some phony files that will illustrate this little trick. My work files are huge, but to show how this works, these are small. The method is the same. Here is an example of a file for one day:



As you can see, each field or column is separated by commas. Here is what it looks like when you open the file in Excel or a similar spreadsheet program.



Every day, another similar file comes in, and each may have different numbers of rows. I have been trying to avoid opening one file at a time, selecting the data, copying it into a new file, and repeating that process over and over. I figured I might be able to develop a macro to help, but even that is not easy for this project. This has been perplexing me for weeks.

Then today, something in the back of my mind popped out. I have been looking at this from an Excel-centric approach. For whatever reason, I remembered about using the DOS COPY command to concatenate files into a new file. This will only work if the files have the same layout, but in the case of my particular problem, they do. SO, I put all the files in the same folder (or Directory as DOS calls them), opened a DOS window, and using the DOS command:

COPY *.csv newfile.csv

I successfully combined the files into one file named newfile.csv. Here is how the command executed (which took only seconds).



As you can see, it took all three files and copied them into one new file. I then opened the new file in Excel, and here is what I now have.



You notice that since each file had a row of column headings, I now have three sets of headings scattered in the file. No problem. I will just sort the data and delete the rows with the duplicate headings. Infinitely faster than manually opening many files in Excel and doing the routine of select, copy, paste. I now have a single data file with which to work. WOOOOHOOOOO!!!!

Simple, elegant, fast and effective. What more could anyone want? Plus, I am surprised it took me so long to come up with this, as a few months ago, I posted on this very blog how to create a spreadsheet from the DOS command prompt. Sometimes the solution is so simple that you overlook it, getting stuck in one way of looking at things.