Export to Excel gives error for large lists

Question

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.

Answer 1

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}]