Hello,
I have a situation I'm dealing with at work, and all I know to use for this is Excel. If there is a better way, please fill me in.
I have a few THOUSAND CSV files in a standardized format. There are fields that I use for calculations in Excel, and then I use those finished calculations to output a new txt file.
I have an Excel workbook with two worksheets in it.
The first worksheet is my input from the CSV and formula worksheet. I copy/paste the text from my CSV and the formulas calculate some new numbers that appear in fields further down the row. There is no macro, the target fields just have the formulas in them.
The second worksheet has the pertinent data taken from the input/formula worksheet placed into an organized linear format. From this I copy/paste that data into a text editor so I wind up with lines of the correct data.
It all works quite well, but even at 1 minute per CSV file input, that's a few THOUSAND minutes, and a lot of monotonous work.
Even after that I run a macro in the text editor to change my line data to block field data. Adding another half a minute and a few more mouse clicks to an already tedious task.
First question, how do I batch the CSV files through my Excel workbook so that Excel will take each CSV file in turn and create the desired output TXT file?
Second question, can Excel output the line data into my desired block field data at the same time?
As you can see, this would take a lot of stress out of my life. I'm no programmer, but I know a little, very little perhaps, and I would appreciate any help.
My CVS input looks like this, except there are between 20 and 210 lines of this per file:
43115b3,0338_4792,633584.220,4789580.988
My linear output looks like this (if you ignore the word wrap) for each of the CSV lines:
0338_4792 UL 631303.260 4790848.188 UR 635865.180 4790848.188 LR 635865.180 4788313.788 LL 631303.260 4788313.788 -99
My text editor macro turns the linear data into block field data like this for each of the linear data lines:
0338_4792 UL 631303.260 4790848.188
UR 635865.180 4790848.188
LR 635865.180 4788313.788
LL 631303.260 4788313.788
-99
(if your viewer doesn't show nice neat even columns and rows, well, it's supposed to)
Thanks for ANY help you can give me.
Carl