# Importing an Excel file as a structured dataset

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 • Import["Adata.xlsx", {"Dataset", 1} and Import["Adata.xlsx", {"Dataset", 1}, "HeaderLines" -> {1, 1}] works as expected in version 11.3 (windows 10/64bit). – kglr Jan 19 '19 at 4:59 ## 2 Answers imp = Import["Adata.xlsx", {"Data", 1}]; ds = Dataset[AssociationThread[imp[[2 ;;, 1]] -> (AssociationThread[imp[[1, 2 ;;]] -> #] & /@ imp[[2 ;;, 2 ;;]])]]  Import["Adata.xlsx", {"Dataset", 1}] also works: Import["Adata.xlsx", {"Dataset", 1}, "HeaderLines" -> {1, 1}]  $Version


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

• 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 Jan 19 '19 at 5:30
• @Sangdon, if your OS is windows10, it might be a glitch that is fixed in version 11.3.
– kglr
Jan 19 '19 at 5:35
• Thanks. Yes. I believe the "Dataset" option not working is a glitch in 11.2. I use windows 10 and 11.2. Jan 20 '19 at 18:23

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]


• 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. Jan 20 '19 at 18:26