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!
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