reading text and reformatting for excel

Another member of my team has created a modelling programme which outputs data that looks like this:

Train    MWh consumed    MWh regen   V_av.   Umean_tr   Min V    @ km       Max V     Max Delay(s) @ station
01_02_01    0.2498        -0.0209    1.5755    1.474   1.1937   0.027523    1.8    
01_02_02    0.2496        -0.0259    1.5717    1.4722   1.1937   0.027523    1.7963    
01_02_03    0.2496        -0.0277    1.569    1.4723   1.1937   0.027523    1.7963    
01_02_04    0.2494        -0.0299    1.5628    1.4614   1.1938   0.027523    1.798    
01_02_05    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_06    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_07    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_08    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_09    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_10    0.2497        -0.0299    1.5628    1.4621   1.1941   0.027523    1.7974    
01_02_11    0.2497        -0.0296    1.5629    1.4621   1.1941   0.027523    1.7985    
01_02_12    0.2497        -0.0272    1.5691    1.4646   1.1941   0.027523    1.8    

I want to read the 'table' and reformat it into excel. Is that likely to be simple or should I delve into the depths of his programme and print them to the excel sheet directly?

[Stephen23]

"outputs data that looks like this"

Where? In the command window, in a UITABLE, in a text file, or somewhere else?

[Mathieu NOE]

if the native file is ascii , txt , etc... you can open directly from within excel , why use matlab in the loop (unless you can complex math to perform ?)

[Timothy]

Sorry, the data is output in the command window but I am using diary to save it to a txt file. There is other data in the output such as titles and such so I will need to search through programatically

[Stephen23]

It rather depends: if you want to write smelly, one-time code that requires manual steps then feel free to process the results from DIARY or the command window. If you want the more generalised approach then modify the code to write to file directly using WRITETABLE or WRITECELL or similar.

[Mathieu NOE]

@Timothy

the data is output in the command window

then why not save it directly to a file with appropriate format - instead of using diary where all history of the command window can be annoying ?

if you would post such a diary file and say what you need / want , we may better help you (btw)

[Timothy]

This is because the back end processing stuff was written by a colleague and is in continual development so any changes I make to the code will be useless when he issues a new version

[dpb]

If this is a requirement of the app it would seem to work with the developer to merge into the base code, then. Otherwise, you'll still be reinventing the wheel to parse a changed output format.

ADDENDUM

Even if all you can convince him/her to do is to output a distinct keyword surrounding specific output sections of interest it would make parsing consistent and easy to add/remove sections instead of having to search for some format/title that may either change or have multiple instances to isolate the section of interest from or any other of an innumerable number of possibilites....and I've had to do much of that in the past on occasion and it ain't always fun nor trivial.

[Timothy]

Thanks for the advise

[dpb]

Glad to help guide if can...try parsing ANSYS output files of thousands of pages with headers, page numbers, different output options requested by user, etc., etc., ... <grin> ... been there done that and sometimes it is the only way, but it's almost never as trivial as it seems going in.

[Timothy]

Thanks, I have decided to rewrite my own versions of the functions so that I can just directly grab the data and I'm writing it in chunks that are easily copy and paste-able for any future versions

[dpb]

If this is, as sounds as if is, a continuing need, the upfront effort will undoubtedly pay longterm beneifts.

Answer 1

[dpb]

writetable will write the table itself directly; it would seem far simpler to find where inside the code the wanted data are being output and add code there to write to the spreadsheet. If there's other "stuff" to write as well, writecell is very useful if you just create a big container for everything and then write it out(*).

There is an 'Append' mode in the writeXXX family functions so if there are a limited number of pieces to write and they're not just one after another in sequence, you could make separate calls. However, each call is "standalone" in opening/closing the Excel file so that overhead is accrued with each call. Trying to do that in a tight loop can, in fact, result in an eventual hang/crash in my experience so that's not a panacea against doing some aggregation of output to minimize the number of distinct calls.

I'd think that a much simpler approach than it will be to parse the output to find the pieces-parts wanted.

(*) However, just as a side note, writecell, writematrix and friends are just wrapper functions that convert the input into a table and then call writetable to do the work.

ADDENDUM

Note that the issue regarding multiple calls to the high level output routines is pertinent to either approach; whether in the originating code or in parsing the output from a text file.