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:

Do the two Imports
Make a function with Association to format the lists as a lists of associations
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*)

$\begingroup$ Had wrong title from previous question. Updated title. $\endgroup$

Edmund
– Edmund

2014-09-17 15:13:55 +00:00
Commented Sep 17, 2014 at 15:13 — Edmund
– Edmund, Commented Sep 17, 2014 at 15:13

Sjoerd C. de Vries · Accepted Answer · 2014-09-17 21:27:14Z

12

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:

Mathematica graphics

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

Mathematica graphics

edited Sep 17, 2014 at 21:27

answered Sep 17, 2014 at 21:08

Sjoerd C. de Vries

66.4k15 gold badges192 silver badges337 bronze badges

$\begingroup$ Very concise. I like how you get one Dataset with all the sheets layered in it. Not up on were and when of delayed rule yet. I'll need to look at that some more. Thanks. $\endgroup$

Edmund
– Edmund

2014-09-17 22:56:03 +00:00
Commented Sep 17, 2014 at 22:56
$\begingroup$ sorry but I'm not completely understanding what is going on after the import. It seems that you are saying that for all entries pattern match them into DateObject. I don't understand how this is working? Is this doing the same thing as /.x:__?DateObjectQ which pattern matches anything that is a DateObject (I think). If so, what order are things happening in the replace all? Thanks. $\endgroup$

Edmund
– Edmund

2014-09-17 23:34:32 +00:00
Commented Sep 17, 2014 at 23:34
$\begingroup$ @edmund x:DateObject[__] just means anything with Head DateObject which we will name x (could also have been written as x__DateObject). Import generates such DateObjects for Excel date fields, and the following Export doesn't handle that in such a way that in can be easily be imported again. $\endgroup$

Sjoerd C. de Vries
– Sjoerd C. de Vries

2014-09-18 15:31:27 +00:00
Commented Sep 18, 2014 at 15:31
$\begingroup$ Ah, so you are pattern matching all DateObject functions in the InputForm output. I didn't realise you could do that. So it is first naming pattern DateObject[__] as x. Then when it finds this pattern it replaces it with the DateString statement with the x symbol in it. Then (after that replacement) does another replacement to change x to what it found for the pattern of DateObject[__]. Giving the desired result of reformatting the dates. Ok. One more question: Is x : _?DateObjectQ :> equivalent to x : DateObject[__] :> ? Is one faster than the other? Thanks. $\endgroup$

Edmund
– Edmund

2014-09-18 18:18:35 +00:00
Commented Sep 18, 2014 at 18:18
$\begingroup$ @Edmund They are functionally equivalent. ? is PatternTest and performs the test that follows it on the matched (!) pattern that precedes it. I would expect the test DateObjectQ to be somewhat slower in this case, but I might be mistaken. $\endgroup$

Sjoerd C. de Vries
– Sjoerd C. de Vries

2014-09-18 19:19:30 +00:00
Commented Sep 18, 2014 at 19:19

| Show 4 more comments

Zviovich · Accepted Answer · 2014-09-17 19:32:29Z

enter image description here

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"}}*)

Athanassios · Accepted Answer · 2015-12-12 12:36:01Z

2

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}

edited Dec 12, 2015 at 12:36

answered Dec 10, 2015 at 15:36

Athanassios

1,31110 silver badges26 bronze badges

$\begingroup$ Solution posted by @Zviovich is clever indeed but one may run into problems when a worksheet has names that do not follow Symbol naming conventions e.g. wages-2015 $\endgroup$

Athanassios
– Athanassios

2015-12-10 15:46:52 +00:00
Commented Dec 10, 2015 at 15:46

Add a comment |

Stack Exchange Network

How to SemanticImport Multiple Excel Sheets

Update:

3 Answers 3

Your Answer

Linked

Hot Network Questions

How to SemanticImport Multiple Excel Sheets

Update:

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions