How to save the results in an identical Excel file (.xls) in each iteration of a Do loop

Question

Assume the following code (Below code is just an example to clearly illustrate my problem):

Res = {};
Iterations = 5;

Do[
Results = AppendTo[Res, i^2]
, {i, 1, Iterations}];

I have 2 questions:

I want to save the results in each iteration in an identical excel file (.xls file). In the end, when the iterations are finished, I will have one Excel file with all the results in it.
I tried to make the code more faster and elegant using Scan[] , but I failed to do it and I couldn't do that.(It is good to mention that I am just a rookie Mathematica programmer and I don't have much experience in it!).

Could you please help me with these poroblems ?

Uh oh, your user name reminds me that I never responded to this in the way I said I would. :-( — Mr.Wizard, Apr 30 '14 at 08:22
I'm not sure if it's possible to export data to a pre-existing Excel file. Personally, I'd collect the results from the loop and export them at the end. Also, Do is in no way a recursive construct. Are you sure you mean recursive? — Oleksandr R., Apr 30 '14 at 08:23
@Mr.Wizard: Take it easy, my dear friend !! You did a great favor and showed me the real way to program in Mathematica. I managed to handle that problem, but as you said, it wasn't an elegant way and now, I face a run time problem !! My code takes many days to give the results !! — Shellp, Apr 30 '14 at 08:27
@Oleksandr R: So you mean there is no way to save the data before completing the Do loop?! And thank you for mentioning " recursive construct ". It was just a mistake and I edited it!! — Shellp, Apr 30 '14 at 08:30
the standard Export for excel files can only write content in one go. Of course you can completely overwrite the file for each line, which is inefficient but probably not too problematic. Other than that I see the following possibilities: write to a text file (or CSV) line by line, for these appending is simple and efficient. Depending on the data, you won't loose much compared to an excel file. If you really need excel, you could use NETLink to do what you want, if you search for "NET excel" you'll find many Q+A to start... — Albert Retey, Apr 30 '14 at 08:38
What about different Excel files ?! I mean overwrite each iteration in different excel files?! For example, if I want to do 5 iterations, In the end, I will have 5 Excel files with results. The point is that I want to save my results in each iteration done. — Shellp, Apr 30 '14 at 08:44
@Shellp: yes, you could write a new file per iteration, but if iterations are "quick", you'll waste time for file operations. — ciao, Apr 30 '14 at 09:46
@Rasher: I have a question! In my real code, 1 iteration takes 5 minutes. 2 Iterations approximately take 11 minutes. So if we consider it as a linear function, For 50 iterations run time is supposed to be 50*5=250 minutes. But in practice, for 50 iterations, it takes more than 33 hours!!! Do have any idea why it is happening ??!!And Do you think it's a good idea to save result in each iteration and use it for next step ?! — Shellp, Apr 30 '14 at 12:07
If one iteration takes 5 minutes and two iterations take 11 minutes then the second iteration must be taking 6 minutes. If the pattern continues (third iteration takes 7 minutes and so on) the total time should be over 24 hours. So the question is why does each iteration take longer than the last? We can't answer that without knowing what you're actually doing. — Simon Woods, Apr 30 '14 at 16:09

score 3 · Accepted Answer · edited Apr 13 '17 at 12:56

Here are a couple of schemes suggested in the comments, plus one of my own, namely, to save the results every ten minutes or whatever:

(* save after every iteration *)
Do[
  Results = AppendTo[Res, i^2];
  Export["/tmp/thesamefile.xls", Results],
  {i, 1, Iterations}];

(* save after ten minutes *)
time = AbsoluteTime[];
Do[
  Results = AppendTo[Res, i^2];
  If[AbsoluteTime[] - time > 600,    (* 600 sec = 10 min *)
   Export["/tmp/thesamefile.xls", Results];
   time = AbsoluteTime[]],
  {i, 1, Iterations}];
Export["/tmp/thesamefile.xls", Results];

(* all results at the end *)
Do[
  Results = AppendTo[Res, i^2],
  {i, 1, Iterations}];
Export["/tmp/thesamefile.xls", Results];

If the result of each iteration consists of a large amount of data, one might be better off writing to a different file each time; see for instance, How to export a large number of files?

How to save the results in an identical Excel file (.xls) in each iteration of a Do loop

1 Answers1