1
$\begingroup$

I am trying to export a non-rectangular nested list of data as an xlsx spreadsheet. If I export an un-nested list, it shows up as a single column. However when I go to export the nested list, it makes each sublist a row where I would like each sublist to be a column. My datasets are large and XLSX has a limit on the number of columns, so Mathematica will not even attempt the export and instead throws error Export::toomany3. This means I cannot swap the rows for columns in Excel. Furthermore, Mathematica won't let me transpose before exporting since the list is not rectangular. I can export the data as a CSV then use an online tool to transpose it, but it just seems like there's got to be a simple way to get Mathematica to do it. Any help would be appreciated.

data = Table[Range[i], {i, {51125, 26336, 10932, 3834, 1579}}];(*Example data*)
Export["data.xlsx", data[[1]]] (* Gives me a spreadsheet with 1 column *)
Export["data.xlsx", data] (* Tries to export data[[i]] as row instead of column *)
(* throws error because exceeds column number limit for xlsx *)

Solution provided by george2079:

Export["data.xlsx", Function[{cols}, Transpose[PadRight[#, Max[Length /@ cols], ""] & /@ cols]]@data]

Thanks for your help!

Improve this question
$\endgroup$
3
  • 1
    $\begingroup$ Please do not post images of your work, especially when the images display at a size that make them difficult to read. Please post your actual Mathematica code in the form of text that can be copied and pasted into a Mathematica notebook. Without such, it will be difficult to reproduce your problem and to experiment with possible solutions. $\endgroup$
    – m_goldberg
    Nov 28 '17 at 3:45
  • 1
    $\begingroup$ pad all the lists so they are the same length (PadRight), then transpose, then export. I think if you pad with empty strings excel will be happy. $\endgroup$
    – george2079
    Nov 28 '17 at 4:12
  • $\begingroup$ I am new here so I appreciate the advice @m_goldberg. I have edited the question as per your suggestion. Thanks! $\endgroup$
    – bRost03
    Nov 29 '17 at 17:46
2
$\begingroup$

following my comment here is how to use PadRight 

c1 = {1, 2}
c2 = {3, 4, 5}
Export["test.xls", 
  Function[{cols}, 
    Transpose[PadRight[#, Max[Length /@ cols], ""] & /@ cols]]@{c1,c2}]

( verified the "" pad gives an empty cell in excel )

also if you don't mind padding with zeros even more simply:

Export["test.xls", Transpose@PadRight@{c1, c2}]

unfortunately you can't specify the pad element with the array form of PadRight

Improve this answer
$\endgroup$
2
$\begingroup$

First, you must convert your rows to columns. For instance:

data1 = {#} & /@ RandomReal[{0, 1}, 51125];
data2 =  {#} & /@ RandomReal[{0, 1}, 26336];

then, export:

Export["data.xlsx", "Sheets" -> {"random 1" -> data1, "random 2" -> data2}, "Rules"]
Improve this answer
$\endgroup$
3
  • 1
    $\begingroup$ Alternative way to convert a list to a column: dataColumn = Transpose[{RandomReal[{0, 1}, 51125]}] $\endgroup$
    – Sjoerd Smit
    Nov 28 '17 at 16:05
  • $\begingroup$ This gives each column in a separate sheet which is closer to what I am trying to do but is still inconvenient to work with. Thanks for taking the time to answer! $\endgroup$
    – bRost03
    Nov 29 '17 at 17:54
  • $\begingroup$ You want to export the data as columns in the same sheet? If so the modification of Export should be easy. $\endgroup$
    – José Antonio Díaz Navas
    Nov 29 '17 at 18:25

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.