# Can I use Map here rather than Table?

I've got something that works, but it seems like it would be "nicer" if I didn't have to use Table (or For, etc.).

I've got 3 Excel files, each with multiple sheets. Each file also has a sheet called "Paste Here" that I don't want. Each sheet contains an array. I'm reading in the file names from the folder, then from each file I read in the sheet names.

Now, I'd like to read the array from each sheet and store them all in a single (nested) list. I'm currently doing the following, which works fine:

Module[{pmlPath},
pmlPath = ParentDirectory[NotebookDirectory[]] <> pmlFolder;
fileNames = Select[FileNames["*.xlsm", pmlPath], StringCount[#, "~"] == 0 &];
sheetNames = DeleteCases[(Import[#, "Sheets"] & /@ fileNames), "Paste Here", {2}];
pmls = Table[Import[fileNames[[i]],
{"Sheets", sheetNames[[i, j]]}], {i,Length[fileNames]}, {j,Length[sheetNames[[i]]]}];
];


It seems like I would have to use multiple slots to accomplish that, if it's possible, and I really don't know how to do that.

• Maybe something like Import[#, {"Sheets", #2}] & @@@ Transpose[{fileNames, sheetNames}]? – kglr Jun 11 '14 at 20:26
• Or Import[#1, {"Sheets", #2}] & ~MapThread~ {fileNames, sheetNames} – Simon Woods Jun 11 '14 at 20:33
• Okay, I'll toss in: Inner[Import[#, {"Sheets", #2}] &, fileNames, sheetNames, List] – Mr.Wizard Jun 11 '14 at 21:42

fileNames = "file" <> # & /@ RandomSample[CharacterRange["A", "Z"], 4]

(* {"fileS","fileU","fileJ","fileO"} *)

sheetNames = "sheet" <> # & /@ # & /@ (RandomSample[CharacterRange["A", "Z"],
RandomInteger[{1, 3}]] & /@ Range[4])

(* {{"sheetB","sheetR","sheetK"},{"sheetA"},{"sheetC","sheetO","sheetW"},
{"sheetY","sheetU"}}*)


Use Import in place of foo in the following:

foo[#, {"Sheets", #2}] & @@@ Transpose[{fileNames, sheetNames}]

(* {foo["fileS",{"Sheets",{"sheetB","sheetR","sheetK"}}],
foo["fileU",{"Sheets",{"sheetA"}}],
foo["fileJ",{"Sheets",{"sheetC","sheetO","sheetW"}}],
foo["fileO",{"Sheets",{"sheetY","sheetU"}}]} *)

• I don't understand the second "# & /@". I see that it doesn't work if I remove it. Denote "(RandomSample[CharacterRange" and everything to it's right as X. If I execute X, I get the same thing as when I execute # & /@ X. I really don't understand what's happening. – Mitchell Kaplan Jun 12 '14 at 18:52
• @MitchellKaplan, the function f="sheet"<>#& concatenates "sheet" with the input argument. (Btw you can get the same result using Map["sheet" <> # &, X, {2}] -- map f on X at level 2.) If you compare the outputs of f/@X and f/@#&/@X, you see that, when X is a list of lists, the first one applies f to the rows of X while the second applies it to the deeper elements inside each sublist. Hope this helps. – kglr Jun 12 '14 at 22:48