Calling excel in a batch file and processing many TXT files.

[ke7fck]

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

[soybean]

Hello and welcome to the forum.  I've used Excel for years but you have a rather complicated application there and I doubt I can help with it.  Someone else may have some advice.  In the mean time, I suggest you also register on http://www.excelforum.com/ and post your question there, too.

By the way, what version of Office are you using?

[Reno]

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.

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

so you are trying to calculate a 4-column CSV file and then convert it to 14-column TXT file. and it doesn't have to be done in excel right? because in excel it takes more overhead when you load the application, import each of the csv file and then run the macro eventually.

in the 14-column txt file, if you could tell what each of the formula to obtain the value, maybe someone can help you. and post a little bit more sample input data to check for pattern consistency.

[CWebguy]

Maybe a database and script?

It may take you more time to write though then you might save, I don't know