Read Data from Excel | Loop command | Record & File Variable Type - Automation Anywhere A2019 #04
ฝัง
- เผยแพร่เมื่อ 30 ก.ย. 2024
- Hi Guys!
This video explains -
Introduction to Excel Advanced Package of A2019
Introduction to Excel Basic Package of A2019
Demo to read Excel data
Introduction to Loop Command in A2019
Introduction to new Variable Type Record in A2019
Introduction to new Variable Type File in A2019
==================================================================
JMeter Tutorial Playlist:
• JMeter Tutorial
Git and GitHub Tutorial Playlist:
• Git and GitHub Tutorial
Automation Anywhere Tutorial Playlist:
• Automation Anywhere A1...
UiPath Tutorial Playlist:
• UiPath Tutorial
Subscribe for more tutorials - / @qualityassurancelab7764
Hi Guys! Hope you found this video useful. Please like, share and subscribe to my channel. This will motivate me to create more good contents. Thanks!
Yes it's good just having a query if we have one excel on shred path and multiple bots need to use it how we can use that excel sheet for writing the data from different bots. Example from one bot personal info is coming and from another both professional info is coming..we need to write data on same sheet at same time.
Yes, Very help full, Can you please provide us the content docs, so we can keep for our record while learning the course,
Thanks
@@mohammednaveed899 Sure, please ping me at labqualityassurance@gmail.com, I'll provide docs
@@qualityassurancelab7764 , Thank you. I sent you email and requested the docs.
Mam, in my local laptop I have microsft office student 2019..still I m not able to use excel advanced commands? .. Any ideas what's the issue?
if one of command is missed then loop will not wrk.what is the command to perform the loop?
To perform Loop, use Loop action and select the Iterator as per requirement
I have a use case wherein I have three values in a single cell separated by a comma delimiter...I want to fetch single value from that cell based on pattern.....for eg I have A,B,C as values in a single cell...now I wnat to fetch B only...how to do that!!!
Please use String -> Split Action with delimiter as ,
A, B and C will be stored in a list variable say myList.
To fetch B, use myList[1]
Reference - th-cam.com/video/YEFtEyoGBkc/w-d-xo.html
@@qualityassurancelab7764 thanks for this...surely gonna try with this approach....One more thing Please make a Video on IQ bot for the document type as "other" wherein we have less standard fields....and explain the different types of OCR like GooglevisionAPI, ABBY etc....will be really looking forward for these 2 videos🙂🙂
You do a great job and help a lot of people (including me) with these videos, thank you!
Hi Mam how to choose excel file from user
Please use Forms and Interactive Forms Action to provide the list of Excel Files (Complete file paths) to the user to choose.
User can choose the required Excel( File Path) from the Form, and provide that Excel( File Path) in Advanced Excel -> Open Action to open the file.
To know about Forms and Interactive forms, please refer - th-cam.com/video/oQ_ay0DeWw8/w-d-xo.html
Hi ma'am
I was trying to run the same code in my system but after displaying one row it is showing error "Session ' null' is already closed.Please create another session and try".
In my Excel Advanced: Open there is option of Creat Excel session in that there are 3 options Local session,Global session and Variable I choose Local session.
Same error I got ,tell me if u found solution
He'll mam I'm follow same staps as you created but while I'm running my bot 1 time it will show the 1 index after that I getting error that connot find any spreadsheet linked to session 'Session 1'
Hi, could you please let me know at which Action you got this error?
Can you please give me your email author wise any contact so I contact to you mitalipatel244@gmail.com this is my mail id
@@mitalipatel1462 You can contact me at labqualityassurance@gmail.com
HI, I'm running a bot that loops through given data(Excel1) and after each successful run, i want to copy the Excel1(row) data to a new Excel1 sheet. For some reason I can't get it right. Can you suggest an easy way; maybe i'm just overthinking this process. Thx!
Provide different session names in the actions for both excels and it should work.
Please checkout this demo which performs the same task - th-cam.com/video/Qsvm6OlBi9U/w-d-xo.html
Hi Mam in AA 2019 how to choose excel file from user and open that file?
"Open" command under Excel advanced and browse for it.. It will connect with you machine local files directory window
can we share our task to others in community edtion
No, this option is present in enterprise edition
What's the use of session name under open excel action. I am starting from A2019 , no idea of A11.......
it sure was useful, but she sure does sound like a robot.
Thanks for the feedback, I'll take care in my future videos
Able to see only the last row data [ie..3rd row data] as a message. Unable to see the previous 2 rows data.
Please confirm that you added the message box inside the loop, not outside
@@qualityassurancelab7764 Thanks, It's working
Nice. Video... Can you please post one video to open latest file from folder
If i am downloading table data from different links of a page, how do i store in multiple sheets. Each table data should be in each sheet. Any suggestions?
Hi i got this message on run
Bot Launcher for user session HP-PC\HP unresponsive.
If the problem persists, please contact your system administrator.
i am using window 7
Hi Sudhanshu, In A2019, bots are configured to run on Windows 10 only, so you received this error. Try to switch to Windows 10, you won't get this error.
Hi, You can run the bot on Windows 8 as well but before you run every bot you need to restart the AA services in services on local computer.
Very helpful video. If you can create a video on dictionary datatype, it will be helpful
I will explain the dictionary datatype with Email Automation, will share the video soon.
Thank you!
Hey quality assurance girl,
Where r u,i need to listen ur voice,if possible show ur face na
How to create employees excel sheet
Do you mean create excel sheet using AA? If yes, please use Excel Advanced -> Create Workbook, Create Worksheet
really informative and simple, please also put some complex use cases for a2019
you're great! very helpful, subscribed!
Hi , i need help to read data from multiple file and copy in 1 file. how can we do that
Hi QALab, great video! Let us assume that I want to get only:
1 - John
2 - Nick
3 - Joseph
How should I do that, because using cell range A1 to B3 will not work?
Hi, thanks. A1 to B3 will work. Let me know if you face any issue
Instead of message box I have to write in notepad.. how ro do that
Hi Mam. In Message box we are displaying the 3 columns with the help of -. Eg : " Record1 - Record2 - Record3". What if we have to read 10 columns instead of 3. Is there any option to give the range for the columns in Message box. Please advise.
Hi, If you simply want to read data from excel and write it somewhere(csv /another excel), use Table Variable in this way where no row/column index need to be provided - th-cam.com/video/Qsvm6OlBi9U/w-d-xo.html
Now to display the data in message box using record variable, column index is required. or, using table variable both row and column index are required.
If you have lot of columns say 10, you can add message box under the Loop with Iterator - For n times, put times filed as 10. In the message box, you can write $DataRecord[n]$
Hope this helps!
@@qualityassurancelab7764 Got it mam. Thanks for the response.
Why my bot reading only the last row data??
Hi mam, greetings from Colombia, I had a question, this command only can used in 2000- 2016 version or the 2019 version works too?
Hello and greetings to you too!
This command should work for 2019 version too
@@qualityassurancelab7764 Maám I do have a similar question as well.
I followed the same steps like you've told in the video and below error is coming up every time I run the bots:
''There was a problem at line 1
This may be due to the following reason:
An unknown error occurred when executing the 'OpenExcel' command. To continue, contact your system administrator.
To continue, edit the bot and fix the error. Then, try again.
If you continue to see this message, please contact your system administrator.
Code: bot.execution.error''
@@sajalgpt8 Which version of excel are you using?
As per AA Doc, Actions in the Excel advanced package supports Microsoft Office 2016 through Microsoft Office 2019.
MS Office 2019 it is
@@sajalgpt8 As replied to your previous comment: I observe many people are facing this issue recently, not exactly sure the cause of the issue.
But, could you please try by creating new excel file? Also, please share the file path you used for the excel.
Please confirm if you are able to build the bot using excel basic package instead of excel advanced. Excel basic package has several similar actions as excel advanced.
How can I transpose the Excel sheet data?
Need help please. I have a csv which I am trying to read through A2019 and display output as message through message box. Here csv, during read operation, will get converted to a table and while displaying output through message box, entire row will get displayed. What I want is bifurcation of each value comprising a row so that I will display only particular fields with help of index. Same as you shown here for record variable.
Please ignore, just resolved the issue... I took tab delimiter while reading csv instead of comma, which created issue.. Surprisingly csv file I have look the same like normal excel, but is actually an output of ssms.. Delimiter changed to comma and issue resolved...
@@rajiv7 Cool! you have already resolved the issue :) Yes, to pick correct delimiter is very important. Keep Learning!
Hi,
I often get this message while running any bot in A2019- e. Can you please suggest a solution for this issue?
Hi, please let me know the error
@@qualityassurancelab7764 Bot Launcher for user session unresponsive message while running bots
@@somamahe4767 You will have to install the bot agent again. Please follow the below steps:
1. Delete the Device from your AA Control Room (Go to Device > My Devices under the A2019 Control room , click on 3 dots on your device row and delete the device )
2. Uninstall AA Bot Agent from Windows Control Panel
3. Restart your windows operating system
4. Go to the Control Panel and see if the AA Bot Agent is completely uninstalled
5. Go to Services and check if the AA Bot Agent Service is completely removed
6. . Go to the folder: C:\Windows\System32\config\systemprofile\AppData\Local\
. Check if you are able to see: AutomationAnywhere folder, If present then rename it to: AutomationAnywhere_Old
7. Once 4, 5, and 6 are confirmed then download and install the new AA Bot Agent
8 .Click on laptop icon present on top right corner of the control room and click on Connect device.
9. Follow the steps, your device will be connected
Mam, please make a video on credentials,lockers and password usage in A2019
Sure, I will share the requested video.
Hi, I enjoy watching your videos because you explain so clearly and with so much ease. Thank you.
I am glad my videos are helpful to you, keep learning!
Hello mam,
In a excel I have 3 sheets, sheet1 one has name, city and sheet2 has city and population so want to copy only name and population whose city value is matching.
How can we achieve this in AA?
Hi, please refer this video, similar case is explained here - th-cam.com/video/2JhMAqnU1tQ/w-d-xo.html
@@qualityassurancelab7764 thank you
Bot Launcher for user session HP-PC\MD RAMIZ unresponsive.
If the problem persists, please contact your system administrator
Help me solve this issue
Mam,where can I find bot store in A2019
Bot store is also not available in A2019 as of now. But, in the control room, it is mentioned that Bot store is coming soon. So, it would be available in the future versions.
Thanks lots for making these kind of videos. Its very useful to us.🤩🤩🤩
Hi,
My record variable is not reading values from excel like 'jcf12' , '79000abc', '.,1mxka
', 'abcd' and it is reading values like '45000','73000'. I am storing the individual values of record in a string variable.I am fetching the values from excel through column name. What could be the reason?
Did you get any error or the values are coming as blank?
@@qualityassurancelab7764 The values are comig as blank
@@rohantiwari95 could you pls share all your actions screenshots at labqualityassurance@gmail.com?
@@qualityassurancelab7764 sure
@@qualityassurancelab7764 Team, I have shared you the screenshots on mail
Learned new things today. Thanks for the tutorial.. Subscribed
Excellent video. 3 options for file path are very well explained. Index thing for record variable, no where practically shown except this video. I created the same kind of bot, only difference is, I calculated total number of rows in an excel and then proceeded to iteration.
Please keep creating videos for A2019. All the best.
Thanks Rajiv for the feedback, I will continue to create other videos on A2019. Let me know if you have any specific topic on A2019. Also, thanks for sharing the scenario of the bot which you created. Number of row count becomes very important in some cases :)
Thanks mam.. We need video on DB commands including sql server and ssms setup, if possible. You also can consider usage of joins on tables. This is because many non technical guys face issue for initial installation and connecting it to A2019 and while writing sql queries. Also cross DB query can be a good example. This is bit more towards sql but a seperate video will also do as you created one for google 2 step verification.
Other videos you made with V11 can also be made for newer version, may be in short as functionality has been changed a lot. Please include 'A2019' in video title, will help others to search the same easily.
Thanks.
Rajiv Lokare Thank you for such detailed comment. I will plan database and other A11 videos in A2019. Also, I will create a seaparate playlist for A2019 to avoid any confusion. Thanks again for your time and feedback!
Mam please help me im getting below error after execution of first row. I have tried all the three types loop through option and getting same error.
"This may be due to the following reason:
Session 'Session3' is already closed. Please create another session and try again.
To continue, edit the bot and fix the error. Then, try again.
If you continue to see this message, please contact your system administrator.
Code: bot.execution.error"
Hi, have you added Excel Advanced -> Close action under the loop? If yes, please add that outside the loop
@@qualityassurancelab7764 Thank you madam it got resolved
How we can take variables values through config file? Is there any way? Please let me know.
Thank you for your efforts. Really appreciate it.
Hi Arshad, You are welcome.
Config file can be read by converting it into .txt file. Then, Actions - Open , Read and Close under CSV/Txt package can be used to read the variables in config file
@@qualityassurancelab7764 Thanks for your help. Really appreciate it.
Arshad Siddieque You are welcome
do more videos on A2019 madam
Hi, please checkout A2019 playlist - th-cam.com/play/PLkkGqdBS1r0_cvaLxbh0UvgkS20t9m2w5.html
I will upload more A2019 videos.
how can i upload multiple excel file if i want to automate multiple files from one folder?
Hi, How do I loop for each file in folder? I basically want to open a file capture cell record close file and then move to the next file and do the same
Hi, please follow the below steps:
Step 1: Add the Loop Action and select the Iterator type as For each file in Folder. Please checkout the demo here to work on Loop -> For each file in folder - th-cam.com/video/xdwY9weTzXE/w-d-xo.html
Step 2: Assign the current value to a Dictionary variable. This variable will hold the value of filename and file extension for further use
Step 3: Under the loop , add the commands to open the file, capture cell record and close the file.
@@qualityassurancelab7764 Thanks on step 2 I create the dictionary variable but where exactly do i put the dictionary Keys is it on step 3?
My main issue is that "Open Excel" asks for specific file path how to open all excels in a folder irrespective of their file name?
@@reychari In step 3: when you will add Open Action and you will have to provide path of multiple excels, there Dictionary variable will be helpful.
Lets say that your excel file paths are:
C:\New folder\AA_Notes\Excel1.xlsx
C:\New folder\AA_Notes\Excel2.xlsx , and so on..
And the Dictionary variable is FileDetails
So, you will provide the Dynamic Path as: C:\New folder\AA_Notes\$FileDetails{name}$.$FileDetails{extension}$
Hope this helps.
@@reychari Dictionary variable will be used to get the excel file names dynamically (Since there will be multiple excel files). Please find my other comment , where I have explained how to use Dictionary Variable.
You explained things in details with very much ease.
Thank you so much for making this video 😄👍
Hello team can you help us with how excel filter activity works
Can we use other action to read worksheet without using loop action?
Thanks for sharing your knowledge
Hey mam,
I followed the video instruction and did the same. But after running the bot, it shows an error, It opened the excel sheet,but the message box part didn't work. Kindly help..
Error MSG :
"There was a problem at Line1.An Unknown error occured when executing OpenExcel command"
Code : bot.execution.error
I observe many people are facing this issue recently, not exactly sure the cause of the issue.
But, could you please try by crating new excel file? Also, please share the file path you used for the excel.
Please confirm if you are able to build the bot using excel basic package instead of excel advanced. Excel basic package has several similar actions as excel advanced.
@@qualityassurancelab7764 Hey,
I have tried all the options -
1) Created a new excel file but still it did not open.
2)Tried creating using basic excel package but again the same error.
3) Also had no luck with trying new extensions(.xls).
I tried different all types of filepath,yet the same error. I am stuck here,could not at all proceed further..and my situation is I need to fix it asap.
I am using Licensed version for MS products. :(
Kindly help.
@@qualityassurancelab7764 hey, It seems there was an error in my version of MS Excel. I uninstalled MS products from my Lap and reinstalled from Microsoft website,now it is working properly. Hope it helps !
@@adityakrishnan9620 Great, thanks for confirming!
Thanks Mam
Abhijeet Singh You are welcome
Please make more video for A2019 Bots
@@vbalearningtutorials9758 Here are the latest videos -
th-cam.com/video/VD7tziFRoss/w-d-xo.html
th-cam.com/video/sDTqoB1Q93Y/w-d-xo.html