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?

$\endgroup$
3
$\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}

$\endgroup$
-1
$\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 *)
$\endgroup$
  • $\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 Feb 14 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 Feb 14 at 22:58

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.