Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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}]
share|improve this answer
    
Thanks a lot for your answer. Do you have a sense of why this is working for *.xls but not for *.xlsx ? –  Amatya Oct 16 '12 at 7:04
    
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. –  VF1 Oct 16 '12 at 14:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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