Extract data from multiple Excel Files in same folder
Dear community,
I have n Excel-Files on a certain folder which I would like to open each by each and extract data to a variable.
All Excel-Files have the same amount of columns and I would like to open the first Excel-File, put the data of a static cell into a variable and afterwards use this variable to search something in SAP.
I’m fine with the SAP steps, just struggling to loop through the folder, open the first Excel-File, get the number of the cell A1 there and then use this number. Afterwards to the same process for the 2nd file.
I’m thankful for every hint!
Page 1 / 1
Hi @JulianRPAdev
You may try below steps for this use case
Loop through each file in folder and assign the output to a dictionary variable.
Inside the loop use excel open and specify the path. The file name will be the dictionary variable with key as “name”. The extension will be .xls or xlsx or you can use the dictionary variable with the key “extension”
You can perform the excel operations here like get cell etc. To get the number of row (hope this is what you wanted” you can use “Get number of rows” command under excel which will give you total row in the excel which has data.
Close the excel
Keep in mind to have same session in all the excel commands which will be inside the loop.
Hope this helps!
Hi @Abhay Naik
Thank you very much for the fast answer!
I now 1.: loop for each file in folder and assign the file name + extension into a new Dictionary variable which One key named “name” and empty Value. 2.: inside the loop I try to open the first excel file by adding the Desktop file path to the folder + ‘/’ + the dictionary variable, but does not work..
How should the Desktop file path in “Excel advanced Open”-action look like? Example: C:\Users\USER1\Desktop\folderwithfiles
Seems like I do not fully understand how to use the dictionary variable..
Afterwards I will perform the operation “go to cell xy” and pull the string from there into a variable in order to use this information to query something in SAP.
Hi @JulianRPAdev
Please find the Screenshots below.
Step 1.
Step 2. Enter the folder path then after “\” click on select variable button which is highlighted below. Select the dictionary variable which will ask you to enter the key where you can put “name” as the value.
(C:\Users\Abhay\Desktop\Temp\$filesInFolder{name}$.$filesInFolder{extension}$) → for example the file path in excel will look somewhat like this
Step 3.
Step 4.
Hope this will help!
Dear @Abhay Naik thanks for the single steps.
Steps 1-3 worked perfectly and I just wanted to ask for one more advise as I will continue differently in my bot.
How can I make sure, that AA360 gets the single value of cell XY10 (in your case) just for the first file in the folder and then puts this value into a variable where it is afterwards used to be inserted into SAP?
After I copied something from SAP I want to proceed with the 2nd excel file in the folder until the same process is done for all files.
I have no issue with connecting to SAP, works perfectly fine. I just understand that in the current use-case, we open all excel-files in the folder and get the value of the cell XY10 for each file.
Case is solved - it works like it should with your solution
@JulianRPAdev ,
Great to know! Were you able to figure out the previous question too? sorry, i couldn’t reply. I think it was pretty straight forward?
