6
$\begingroup$

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*)
$\endgroup$
  • $\begingroup$ Had wrong title from previous question. Updated title. $\endgroup$ – Edmund Sep 17 '14 at 15:13
8
$\begingroup$

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

$\endgroup$
  • $\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 Sep 17 '14 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 Sep 17 '14 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 Sep 18 '14 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 Sep 18 '14 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 Sep 18 '14 at 19:19
2
$\begingroup$

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"}}*)
$\endgroup$
1
$\begingroup$

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}
$\endgroup$
  • $\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 Dec 10 '15 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.