Importing an Excel file as a structured dataset

Question

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

Answer 1

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)

Answer 2

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]