# How import Excel file from different folders

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]]
.
.
.];


• If you've written fn1 entries as integers, then this is likely the cause of your problems. Try IntegerString[fn1[[counter]], 10, 3] in your FileNameJoin command. – dwa Jul 21 '20 at 5:43
• I tried, it still gives the error message that First argument FileNameJoin[{D:\ ... ,IntegerString[002,10,3],Mhd-1.CSV}] is not a valid file, directory, or URL specification. – Mehdi Ebadi Jul 21 '20 at 6:53
• What does Head[#]& /@ fn1 look like? – dwa Jul 21 '20 at 8:18
• Please explain the problem clearly. "it is not working properly" is meaningless. What is the error message? Try datafiles = ToExpression@Import@FileNameJoin[{Directory[], #, "Mhd-1.CSV"}] & /@ fn1 – Rohit Namjoshi Jul 21 '20 at 13:43

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.

• Sure! Thanks a lot Alex. – Mehdi Ebadi Jul 21 '20 at 16:40
• @ Alex , Thank you again for your code. I learned that in some folders other than Mhd-1.csv, there is also another .csv file but I want only to use the Mhd-1.csv file in my analysis. Is there any way to apply your code for that case? – Mehdi Ebadi Jul 22 '20 at 16:36
• I think I found how to apply that. In your script I just need to change fnames line to fnames = FileNames["Mhd-1.csv","", [Infinity]]. – Mehdi Ebadi Jul 22 '20 at 16:44
• yes, that is exactly it. Good luck! – Alex Jul 22 '20 at 18:13