0
$\begingroup$

This is probably quite a simple question for you guys but I've just started M and I've been mulling this problem for a few days and I think I am well and truly stuck. So any help would be greatly appreciated.

As the subject say I have a datasheet type excel file, original file have 90+ Sheets and I need to compile all that to one single Dataset for further processing. Bellow is a sample of the file. As you can see, it have a mix of col and row header. As an additional note, the Sheet name all have dash(-) and can't be changed.

test-1={{"Data1", "Value1", "", "", "", "", ""}, {"Data2", "Value2", "", "", "", "", ""}, {"Data3", "Value3", "", "", "", "", ""}, {"Data4", "Value4", "", "", "", "", ""}, {"Data5", "Value5", "", "", "", "", ""}, {"", "", "Data6", "Data7", "Data8", "Data9", "Data10"}, {"", "", "Value6", "Value7", "Value8", "Value9", "Value10"}} test-2={{"Data1", "Value26", "", "", "", "", ""}, {"Data2", "Value27", "", "", "", "", ""}, {"Data3", "Value28", "", "", "", "", ""}, {"Data4", "Value29", "", "", "", "", ""}, {"Data5", "Value30", "", "", "", "", ""}, {"", "", "Data6", "Data7", "Data8", "Data9", "Data10"}, {"", "", "Value31", "Value32", "Value33", "Value34", "Value35"}}

This is what I got so far, but the end result only display one set of data instead of both sheet. end result

sh = Import["TestBook1.XLSX", "Sheets"]; i = Table[Import["TestBook1.XLSX", {"Data", #, All}]] & /@ sh; rowName20 = (#[[;; 5, 1]]) & /@ i; rowValue20 = (#[[;; 5, 2]]) & /@ i; colName20 = (#[[6, 3 ;; 7]]) & /@ i; colValue20 = (#[[7, 3 ;; 7]]) & /@ i; AssociationThread[(Join[rowName20, colName20]) -> (Join[rowValue20, colValue20])] // Dataset

First time posting so I hope I am giving all information needed, if not apologies.

Many thanks in advance! D

Few posting i have referred to: How to SemanticImport Multiple Excel Sheets how can i associate the headings of columns/rows with content of itself and then recall what i want after i've imported the excel file in mathematica

Improve this question
$\endgroup$
1
  • $\begingroup$ Welcome to Mathematica.SE! I suggest the following: 1) As you receive help, try to give it too, by answering questions in your area of expertise. 2) Take the tour! 3) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! $\endgroup$ Commented Feb 9, 2021 at 21:21

1 Answer 1

Reset to default
1
$\begingroup$

Your Table expression works out of sheer luck, i.e. just because Table[f] simply returns f and Table does nothing there. Apart from that, here is an example of importing multiple sheets at once. It is unclear to me what you want to do with it next and how you want to merge them into a dataset, but I suspect that might turn out to actually not be necessary.

First, let's create a two-sheet Excel file file.xlsx with named sheets (with a hyphen in the name, although that won't matter much, since we will import and use the sheet labels as strings):

Export["file.xlsx", {{ "test-1" -> Table[{x, 2 x}, {x, -10, 10}], "test-2" -> Table[{x, x^2 - 2 x}, {x, -5, 5}] }}, {{"Sheets"}} ]

Now we can import the data as "Rules" (try it on its own and see what it does), then use the "Data" element (it contains a list of lists of data, one for each sheet), and the "Sheets" element (sheet labels as strings), and combine them in an Association data structure:

rules = Import["file.xlsx", "Rules"]; structured = AssociationThread["Sheets" -> "Data" /. rules] (* Out: <|"test-1" -> {{-10., -20.}, {-9., -18.}, {-8., -16.}, ..., {9., 18.}, {10., 20.}}, "test-2" -> {{-5., 35.}, {-4., 24.}, ..., {5., 15.}}|> *)

You can then use the whole association at once:

ListLinePlot[structured]

line plots of both datasets

... or you can access each sheet one at a time, by label, for instance to run a non-linear fit on it:

NonlinearModelFit[structured["test-2"], a x^2 + b x + c, {a, b, c}, x]

fitted model result

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.