# Prepare a dataset in Excel then import to Mathematica

I need to prepare a dataset, which will be much easier using e.g. Excel. Then I want to import and use it in Mathematica. One row in the dataset would look like this in Mathematica:

{"A2",{"a2","b3"},"K6",{"a6","b2"}}


When doing this in Excel, it would look like this.

However, importing this into Mathematica produces:

{"A2","{"a2","b3"}","K6","{"a6","b2"}"}


The problem is the curly brackets, which get some extra quotation mark. I need these curly brackets in several columns. Using a combination of ToExpression and then back to ToString becomes very clumsy, in particular as during such a procedure a2 gets a space in between etc. Ideally, I'd like to import it as typed in Excel. I tried various cell formats in Excel without success. I also could not find a way in Mathematica for a plain import. Even exporting and then importing it as .csv doesn't get rid of this.

• I wonder if it wouldn't actually be much more expedient to generate the dataset in Mathematica directly. Is there an algorithmic rule that generates each row? – MarcoB May 14 '18 at 20:48
• What is the final output you're expecting? Something like this? {"A2",{"a2","b3"},"K6",{"a6","b2"}} – GenericAccountName May 15 '18 at 7:14

As indicated in the comments, when importing the xlsx file you get rather something like :

xsl={{{"A2", "{a2,a3}", "K6", "{a6,b2}"}}};

and basically what you need is only to transform strings like "{a2,a3}" to a list of strings {"a2","a3"}.

String manipulation functions can help. Here is a solution:

xsl /. x_String?(StringMatchQ["{*}"]) :> StringSplit[StringTake[x, {2, -2}], ","]


{{{"A2", {"a2", "a3"}, "K6", {"a6", "b2"}}}}

• Great! However ... I was wondering if this is also possible within a dataset. Say, there are many xs1, then xs2, then xs3, ... Then two columns contain "{a2,a3}" in each row. Now I'd like to apply your function to the entire column, so that in the dataset the column contains the "{a2,a3}" correctly. I tried a few things from the 'dataset' help menu, but was not successful. – Mockup Dungeon May 16 '18 at 8:00
• @MockupDungeon Minimal example: ds = Dataset[{ <|"a" -> 1, "b" -> "x", "c" -> "{1}"|>, <|"a" -> 2, "b" -> "y", "c" -> "{2,3}"|>}]; ds[All, {"c" -> (# /. x_String?(StringMatchQ["{*}"]) :> StringSplit[StringTake[x, {2, -2}], ","] &)}]. The "c" column has been transformed. Read the Dataset docs. – SquareOne May 16 '18 at 8:50
• Thanks! Tried something very similar, but made some minor mistake. – Mockup Dungeon May 16 '18 at 9:48

Using a .xlsx file with the data pictured below:

data = Import["thedata.xlsx"];
Flatten[data,2]


Should produce:

{A2, {a2,b3}, K6, {a6,b2}}


Are you not getting a similar result?

• You are being tricked by the way strings are shown in the output. Try Flatten[data, 2] // InputForm to see what is really being imported, which is equivalent to the problem in the OP. – MarcoB May 14 '18 at 20:44
• It is tricky, but I get something slightly different from the OP: {"A2", "{a2,b3}", "K6", "{a6,b2}"}, I don't see quotes around the elements of the inner lists. – Lee May 14 '18 at 21:03
• I agree with you on the last point. I suspect that the OP has misplaced their quotes when copy/pasting, since the pattern they indicated is unlikely. Nevertheless, what you get is still not what they want, unfortunately. – MarcoB May 14 '18 at 21:48
• @Lee it just looks right but unfortunately isn't, as will be seen when executing the output. I probably have the quotes because of a different input format in Excel. The main problem is the quotation marks around the entire cell content (e.g. "{a2,b3}") which I cannot get rid of. – Mockup Dungeon May 15 '18 at 5:25
• @MockupDungeon Yes, I learned that after following the suggestion from MarcoB. I tried many, many different methods with Import and SemanticImport, but nothing imports the lists properly. – Lee May 15 '18 at 10:43