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.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
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}]
Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks a lot for your answer. Do you have a sense of why this is working for *.xls but not for *.xlsx ? $\endgroup$ Commented Oct 16, 2012 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$ Commented Oct 16, 2012 at 14:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.