I would like to import a large excel spreadsheet with many columns into Mathematica. The first row of the spreadsheet contains the column headings and the remaining rows contain the data. After importing I first separate the column headings and data into separate variables and would like to convert the data to a dataset. Here is an example of what I have tried.
These steps simulate of importing the spreadsheet and separating into columns and data
cols = {"col1", "col2", "col3", "col4", "col5"};
dat = RandomReal[{0, 1}, {10, 5}];
I now need to create an association to use to map the data to the columns but here is where I am having trouble. The best I am able to come up with is to generate
assoc = Table[cols[[k]] -> #[[k]], {k, 1, 5}]
This results in some error messages, but produces
{"col1" -> 1, "col2" -> #1[[2]], "col3" -> #1[[3]], "col4" -> #1[[4]],
"col5" -> #1[[5]]}
which is nearly the result desired. I can then copy that result, paste it into a new cell, and edit that cell by hand, changing the first element of the list, applying an association function, and applying a function to get the desired function to apply to the data
assoc1 = Association[{"col1" -> #[[1]], "col2" -> #1[[2]],
"col3" -> #1[[3]], "col4" -> #1[[4]], "col5" -> #1[[5]]}] &
Map[assoc1,dat]
With this association I can then generate the dataset. Is there any way of generating this function without the necessity of the hand editing step?