3
$\begingroup$

I need to import an Excel file as a dataset with row and column headers, and the following option using "Dataset" does not work. However, I can import the Excel file using "Data" option as a list with no error.

adata = Import["Adata.xlsx", {"Dataset", 1}]

Import::noelem: The Import element "Dataset" is not present when importing as XLSX.
$Failed

bdata = Import["Adata.xlsx", {"Data", 1}]

{{"No", "y1", "y2", "y3", "VIN"}, {10., 4., 5., 9., "x1"}, {11., 3., 
  6., 10., "x2"}, {12., 2., 7., 11., "x3"}, {13., 1., 8., 12., 
  "x4"}, {14., 0., 1., 2., "x5"}}

Then, I can use the Dataset function to change the list to a dataset but don't know how to set the first row and the first column as the column and the row headers. I read the documents for Import with no success. 

bdata = Dataset[Import["Adata.xlsx", {"Data", 1}]]

Thanks.

Sangdon

| improve this question | |
$\endgroup$
  • 1
    $\begingroup$ Import["Adata.xlsx", {"Dataset", 1} and Import["Adata.xlsx", {"Dataset", 1}, "HeaderLines" -> {1, 1}] works as expected in version 11.3 (windows 10/64bit). $\endgroup$ – kglr Jan 19 '19 at 4:59
3
$\begingroup$ 
imp = Import["Adata.xlsx", {"Data", 1}]; 
ds = Dataset[AssociationThread[imp[[2 ;;, 1]] -> 
   (AssociationThread[imp[[1, 2 ;;]] -> #] & /@ imp[[2 ;;, 2 ;;]])]]

enter image description here

Import["Adata.xlsx", {"Dataset", 1}] also works:

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

enter image description here

$Version

11.3.0 for Microsoft Windows (64-bit) (March 7, 2018)

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks for your answer. I read the documents and found the exact example in the "import option", however, strangely enough, the exact command does not work. I am using 11.2. reference.wolfram.com/language/ref/format/XLSX.html?view=all. Thanks again. Sangdon $\endgroup$ – Sangdon Jan 19 '19 at 5:30
  • $\begingroup$ @Sangdon, if your OS is windows10, it might be a glitch that is fixed in version 11.3. $\endgroup$ – kglr Jan 19 '19 at 5:35
  • $\begingroup$ Thanks. Yes. I believe the "Dataset" option not working is a glitch in 11.2. I use windows 10 and 11.2. $\endgroup$ – Sangdon Jan 20 '19 at 18:23
2
$\begingroup$

I think SemanticImport might work well. I don't have Excel, so I made an .xlsx file from your sample data and imported it with SemanticImport as proof of concept.

Here is the code:

data = 
  {{"No", "y1", "y2", "y3", "VIN"}, 
   {10., 4., 5., 9., "x1"}, 
   {11., 3., 6., 10., "x2"}, 
   {12., 2., 7., 11., "x3"}, 
   {13., 1., 8., 12., "x4"}, 
   {14., 0., 1., 2., "x5"}};

path = FileNameJoin[{$HomeDirectory, "Desktop", "data.xlsx"}];
Export[path, data];

ds = 
  SemanticImport[
    path, 
    Join[ConstantArray[Automatic, 4], {"String"}], 
    HeaderLines -> 1]

dataset

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks. I found that the "Dataset" option not working is a glitch in 11.2 and fixed in 11.3. I start learning the MMA few month ago and love that I can solve ODE with easy. $\endgroup$ – Sangdon Jan 20 '19 at 18:26

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.