M
magneticpoles
I recently 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.
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 .csv 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.
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. It
took seconds to complete.
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?
Sometimes the solution is so simple that you overlook it, getting
stuck in one way of looking at things.
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.
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 .csv 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.
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. It
took seconds to complete.
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?
Sometimes the solution is so simple that you overlook it, getting
stuck in one way of looking at things.