2
$\begingroup$

This might be a basic question but I couldn't get it to work.

I have a dataset in Excel that I import to mathematica using

dat = Import["etcetcetc.xlsx", "Dataset", "HeaderLines" -> 1]

However I can't get it to calculate length or extract slices from the dataset as Mathematica sees the whole dataset as one value and thus length is 1 with Length[dat].

What am I doing wrong?

Improve this question
$\endgroup$

3 Answers 3

Reset to default
7
$\begingroup$

Like the previous answer mentions, excel imports as a list Datasets corresponding to each sheet. There's a couple approaches you can take. If you have multiple sheets of interest, anything you do will need to be mapped.

data = Import[..];
Length /@ data

To get Rows/Cols count, do something like 

Dimensions /@ data

I wouldn't be surprised if upcoming versions had elements to get the number of sheets/dimensions of each sheet in a more efficient way as well....

However, in this case you only have one sheet of interest, so to import that sheet directly as a Dataset rather than list of Datasets, do this:

Import["etcetcetc.xlsx", {"Dataset", 1}, "HeaderLines" -> 1]

And you can work with your flat Dataset how you were expecting originally.

If you had multiple sheets and wanted to combine them into a dataset, the other answer was sort of on the right path, but you'd still need to separate the datasets so there's a clear barrier between sheets, something like:

Dataset@AssociationThread[Import[.., "Sheets"], Import[.., "Dataset", "HeaderLines" -> 1]]

You can easily trim off the totals by changing the "Dataset" to {"Dataset", All, ;; -3}

Improve this answer
$\endgroup$
0
$\begingroup$

On Importing an Excel file, you get a list of tabs, each of which is equivalent to a CSV file in its own right. You can then Map each tab to a Dataset.

toDataset[tabs_] := (
    With[{h = First@#, d = Rest@#},
        Map[Association@*(MapThread[Rule, {h, #}] &)][d]
    ] & /* Dataset
)[tabs];

xlsxFile = (
    Import[#, "XLSX"] & /*
    MapIndexed[(("tab" <> ToString[First[#2]]) -> toDataset[#1]) &] /*
    Association
)["path/to/xlsx/file"];

xlsxFile["tab1"] // Head (* Dataset *)
xlsxFile["tab1"] // Length (* 1234 *)
Improve this answer
$\endgroup$
2
  • $\begingroup$ This is very helpful but it puts all dataset into tab1. I can do a length calculation with this but it doesn't let me extract a value from a specific column or a row. $\endgroup$
    – indiffer
    Commented Feb 14, 2019 at 10:18
  • $\begingroup$ This is pretty overkill. First off, take a look at AssociationThread. Second, there's subelements to take parts within excel and dimensions elements. I am adding an answer. $\endgroup$
    – GenericAccountName
    Commented Feb 14, 2019 at 22:58
0
$\begingroup$

Using the Energy Information Agency's (EIA) 860 report that covers all generation assets in the US I may have came up with a simpler example. You can download the same data from EIA 860 Database.

wbLocation = "C:\\Users\\xxxxxx\\Documents\\eia8602019\\3_1_Generator_Y2019.xlsx";

sheetL = Import[wbLocation, "Sheets"]

{"Operable", "Proposed", "Retired and Canceled"}

You can nest the list of sheets within the "Dataset" command. The hiccup comes from needing to convert the initial datasets into a list of associations to be able to put the use the AssociationThread function appropriately. Then you can apply the Dataset function and get the desired results.

Dataset[
  AssociationThread[sheetL, 
    Normal[
      Import[wbLocation, 
         {"Dataset", sheetL},"SkipLines"->1,"HeaderLines" -> 1]]]]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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