# Mathematica and Excel

I have a number of large data sets in Excel and .dat from OMEdit Modelica that then need to import some or all of it to Mathematica for manipulation and plotting. Importing the data is easy enough using Import were I only need the first sheet.

data = Import[filePath/Name.xls, {"Data", 1}];


I then use this to get a list of the column number and header names:

dims = Dimensions[data] ;

DataNumber = Table[{i, data[[1, i]]}, {i, 1, dims[[2]]}] // Grid


This yields:

This is useful, but with larger list, is tedious.

I am searching for a method to give me the column number given a certain column header name, such as header57. Much better would be to be able to give part of the name, such as a wildcard part of the header. Note that the data is not always in the same order, hence the search.

For example, one data column might be ‘Expander.medium.h’ and would like to be able to just find the column number. I might also want to find all the columns that have ‘medium’, so would like to have all the column that have ‘medium’ or similar syntax.

I have tried Cases[] and a few others without luck.

Cases[data[[1, All]], “header87”]


Any suggestions would be most appreciated.

data = Import["ExampleData/elements.xls", {"Data", 1}]


headers = data[[1]]

 {"AtomicNumber", "Abbreviation", "Name", "AtomicWeight"}


You can use PositionIndex and Lookup as follows:

posindex = Map[First] @ PositionIndex[headers]

 <|"AtomicNumber" -> 1, "Abbreviation" -> 2, "Name" -> 3, "AtomicWeight" -> 4|>

Lookup[posindex, Flatten @ Select[StringMatchQ["*eigh*"]] @ Keys @ posindex]

 {4}

• Thank - that works perfectly. - many hours saved... – Chris B May 5 '20 at 10:30