1
$\begingroup$

This is my data mwedata.dat

I do this:

mwe = Import["mwedata.dat"];
dataforexcel20 = DeleteCases[mwe, a_ /; a[[3]] > 20];
dataforexcel80 = DeleteCases[mwe, a_ /; a[[3]] > 80];

Dimensions[dataforexcel80]
Dimensions[dataforexcel20]

{48011, 6}
{2384, 6}

Export["mwe20.xlsx", dataforexcel20]
"mwe20.xlsx"

Export["mwe80.xlsx", dataforexcel80]
$Failed

The Export works for the smaller file but not the larger file. The error message says

Export::fmterr: "Invalid \!\(\"XLS\"\) format. "

When I do this

Table[dataforexcel[k] = DeleteCases[mwe, a_ /; a[[3]] != k], {k, 1,100}];
Table[Export["mwetest.xlsx", dataforexcel[k]], {k, 1, 100}]

I get

{"mwetest.xlsx", "mwetest.xlsx", "mwetest.xlsx",   "mwetest.xlsx" ....}

and so on a 100 times. So there is nothing wrong with the data when a[[3]]>20

In a unrelated question, when doing an Export inside the Table.. would it have been possible to index the excel files with k?

 Table[Export["mwetest[k].xlsx", dataforexcel[k]], {k, 1, 100}]

Ok.. back to my earlier problem, I thought that excel could handle up to 65,000 rows. I'd appreciate some help in figuring out what I am doing wrong here or what can I do to fix my Export of a large file.

$\endgroup$
2
$\begingroup$

This may seem like a solution that's too easy to be true, but I tried the following:

Export["mwe80.xls", dataforexcel80]

It worked for me - the Excel document got exported quickly. I think that the problem was in the format, as the error said.

EDIT

As to your second question, the code you're looking for involves StringJoin:

 Table[Export["mwetest["<>ToString[k]<>"].xlsx", dataforexcel[k]], {k, 1, 100}]
$\endgroup$
  • $\begingroup$ Thanks a lot for your answer. Do you have a sense of why this is working for *.xls but not for *.xlsx ? $\endgroup$ – Amatya Oct 16 '12 at 7:04
  • $\begingroup$ I can't honestly say why one format works. .xlsx should be able to support $2^{20}$ entries, which is even more than its .xls predecessor, but if I had to hazard a guess, I'd say that Mathematica's Export function is at fault here. In the docs the suggested spreadsheet extension is .xls, so perhaps the Export function was built around that. $\endgroup$ – VF1 Oct 16 '12 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.