How import Excel file from different folders

Question

I would like to know how I can import excel files from inside different folders and save the results inside the associated folder. For example, the name of my folders are fn1={001, 002, 0045, 005, 0081} and each one has an excel file "Mhd-1.CSV" (The name of excel file is constant in all folders). Folders are in the same directory (i.e D:\123\123). This is the code that I am trying to use to import the "Mhd-1.CSV" file inside of each folder but it is not working properly. Please note that I want the results to be saved inside the associated folder.

SetDirectory["D:\\123\\123"]
fn1 = FileNames[];
(*fn1={001, 002, 0045, 005, 0081}*) (*Result for fn1*)
numberoffiles = Length[fn1]
initial = 1;
For[counter = initial, counter < numberoffiles + initial, counter++,
filename = FileNameJoin[{Directory[], fn1[[counter]], "Mhd-1.CSV"}];
datafile = ToExpression[Import[filename]]
.
.
.];

Answer 1

I am not certain whether your folder management is the best for what you want, but I can try to give you a working example that you can then modify to suit your needs.

By no means does the code need to be as verbose as mine, but I only did it for the sake of clarity. Also the reason I am specifically using the 'chopped' string of the file is because when you import a list of files, the order might not match what you expect it to be. So to avoid confusion, i added the explicit path.

To replicate your system, I have created a folder called SE_question and inside it I have created 5 folders, each of which, contains a .csv file with the exact same name: "Mhd-1.csv". Typically it is good practise to distinguish the files based on the name rather than the folder, but maybe it makes sense in your context.

By using the full specificiations of the FileNames you can have the absolute relative directory, from which you can then extract the folder name of each file. I then import the files, modify them (in this case just a multiplication because I'm lazy) and then export them their respective locations.

SetDirectory["C:\\Users\\Man\\Desktop\\SE_question\\"]; (* sets the parent \
directory *)
fnames = FileNames["*.csv","*", \[Infinity]]; (* finds the relative path to each \ file *)
l = fnames // Length ;(* total number of folders *)
csvName = "Mhd-1.csv" ;(* sets the default name of the csv *)
csvLength = StringLength[csvName]; (*length of the csv file *)
pathLength = StringLength[fnames[[#]]] & /@Range@l; (* length of the total path for\ each folder*)
paths = StringTake[fnames[[#]], pathLength[[#]] - csvLength] & /@Range@l; (*directory\ of each folder *)

data = Flatten[Import[fnames[[#]], "CSV"]] & /@ Range@l;
newData = data[[#]]*# & /@ Range@l;
Export[paths[[#]] <> "\\_newData.csv", newData[[#]]] & /@ Range@l;
FileNames["*.csv", "*", \[Infinity]]

Output:

{"folder_1\\Mhd-1.csv", "folder_1\\_newData.csv", \
"folder_2\\Mhd-1.csv", "folder_2\\_newData.csv", \
"folder_3\\Mhd-1.csv", "folder_3\\_newData.csv", \
"folder_4\\Mhd-1.csv", "folder_4\\_newData.csv", \
"folder_5\\Mhd-1.csv", "folder_5\\_newData.csv"}

Let me know if you need more help.