How to SemanticImport Multiple Excel Sheets

Question

I have an Excel workbook with multiple sheets that I'd like to import into a dataset each. With Import I can target individual sheets by their name into a nested list.

firstSheet=Import[NotebookDirectory[]<>"data.xlsx",{"Sheets","SomeData"}];
secondSheet=Import[NotebookDirectory[]<>"data.xlsx",{"Sheets","SomeMoreData"}];

I'd like to use SemanticImport to import the sheets with some control over their data types. I tried

firstSheet = SemanticImport[NotebookDirectory[]<>"data.xlsx", 
 {"String", "Date", "String", "Number"}]

This appears to return a Dataset with the first sheet (something odd with it not understanding the date but I think if I change the Excel format for that column it will be ok).

How do I get the second sheet? I tried:

SemanticImport[{NotebookDirectory[]<>"data.xlsx", {"Sheets", "SomeMoreData"}}, 
 {"String", "Date", "String", "Number"}]

It does not like this. Is my only option to:

1. Do the two Imports
2. Make a function with Association to format the lists as a lists of associations
3. Pass the association lists to Dataset to create the datasets

Also, is there a way to get all the sheets (with either Import or SemanticImport) by only opening the file once. Something along the lines of:

{firstSheet, secondSheet} = Import[NotebookDirectory[]<>"data.xlsx", "XLSX"]

but where it gives datasets instead of lists. I tried:

{firstSheet, secondSheet} = 
 SemanticImport[NotebookDirectory[]<>"data.xlsx", 
  {"String", "Date", "String", "Number"}]

Well, I should be able to do this myself once I find out how to get SemanticImport to give up the other sheets.

Thanks,

Edmund

Update:

With @PatoCriollo usage of Set, Evaluate, and Symbol (and some further research in the mean time) I've gotten closer to what I'd like to do using Dataset. It still does not use SemanticImport but it does let me get an arbitrary number of sheets with different header rows into datasets.

dataFile = NotebookDirectory[] <> "\\data.xlsx";
sheetList = {"someData", "someMoreData"};
data = Import[dataFile, {"Sheets", sheetList}];
Set[Evaluate[Symbol[#] & /@ sheetList],
 Table[
  Dataset[Association /@
   Table[
    First[data[[sheet]]][[col]] -> Rest[data[[sheet]]][[row, col]], 
    {row, Length[Rest[data[[sheet]]]]},
    {col, Length[First[data[[sheet]]]]}
  ]],
 {sheet, Length[sheetList]}]
];

someData (*is now a Dataset of sheet someData with headers from 1st row*)
someMoreDate (*is now a Dataset of sheet someMoreData with headers from 1st row*)

Answer 1

I don't think SemanticImport has been designed to deal with multiple Excel sheets. I work around this problem using Import to get the number of sheets and their names, and to import them and ExportString to export them as "TSV" which, on its turn, can be imported by SemanticImportString. An intermediate conversion of DateObject/TimeObject to DateString is necessary, because this data type is not handled well.

My test file:

fileName =  FileNameJoin[{"C:", "Users", "Sjoerd", "Desktop", "test.xlsx"}];

sheets = Import[fileName, "Sheets"];

# -> SemanticImportString[
       ExportString[
         Import[fileName, {"Sheets", #}] 
            /. x : DateObject[__] :>  DateString[x, {"Year", "-", "Month", "-", "Day"}], 
         "TSV"], 
         HeaderLines -> 0
       ] & /@ sheets // Association // Dataset

Answer 2

sheets = Import["sample.xlsx", "Sheets"]
(*{"DataSet1", "DataSet2"}*)

data = Import["sample.xlsx", "Data"]
(*{{{"Country", "Value"}, {"AR", 10.}, {"BE", 20.}, {"SG", 30.}, {"TW", 
   40.}}, {{"City", "Value", "Color"}, {"Amsterdam", 10., 
   "Blue"}, {"London", 20., "Red"}, {"Paris", 30., "Yellow"}}}*)

Set[Evaluate[Symbol[#] & /@ sheets], data];
DataSet1
(*{{"Country", "Value"}, {"AR", 10.}, {"BE", 20.}, {"SG", 30.}, {"TW", 
  40.}}*)
DataSet2
(*{{"City", "Value", "Color"}, {"Amsterdam", 10., "Blue"}, {"London", 
  20., "Red"}, {"Paris", 30., "Yellow"}}*)

Answer 3

I think this is a problem that may arise often. I have read your update. If someone is interested in Importing an arbitrary number of Excel worksheets, each one having a relational table of data with a header-body, and transforming them into Datasets, then the following solution is just 5 lines of code, it can read any worksheet name, and it is easy to follow ;-)

sheets    = Import["sample.xlsx", "Sheets"] ;

headerSet = (#[[1]] &) /@ sheets;
bodySet   = (#[[2 ;;]] &) /@ sheets;

rel2ds[header_, body_]:=Dataset[AssociationThread[header, #] & /@ body];

MapThread[rel2ds[#1,#2]&,{headerSet, bodySet}