Read Data from Excel | Loop command | Record & File Variable Type - Automation Anywhere A2019 #04

Поділитися
Вставка
  • Опубліковано 13 жов 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

КОМЕНТАРІ • 120

  • @qualityassurancelab7764
    @qualityassurancelab7764  4 роки тому +8

    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!

    • @yogesharora9247
      @yogesharora9247 4 роки тому

      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.

    • @mohammednaveed899
      @mohammednaveed899 3 роки тому +2

      Yes, Very help full, Can you please provide us the content docs, so we can keep for our record while learning the course,
      Thanks

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому

      @@mohammednaveed899 Sure, please ping me at labqualityassurance@gmail.com, I'll provide docs

    • @mohammednaveed899
      @mohammednaveed899 3 роки тому +1

      @@qualityassurancelab7764 , Thank you. I sent you email and requested the docs.

    • @sumit7977
      @sumit7977 3 роки тому

      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?

  • @andonii46
    @andonii46 3 роки тому +3

    You do a great job and help a lot of people (including me) with these videos, thank you!

  • @marathimulgi2
    @marathimulgi2 4 роки тому +1

    Hi, I enjoy watching your videos because you explain so clearly and with so much ease. Thank you.

  • @kartik4svpbuilder
    @kartik4svpbuilder 3 роки тому

    You explained things in details with very much ease.

  • @rajiv7
    @rajiv7 4 роки тому +1

    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.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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 :)

    • @rajiv7
      @rajiv7 4 роки тому +1

      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.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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!

  • @sakthivelcslm
    @sakthivelcslm 3 роки тому +1

    Nice. Video... Can you please post one video to open latest file from folder

  • @saisasee406
    @saisasee406 2 роки тому

    Thanks lots for making these kind of videos. Its very useful to us.🤩🤩🤩

  • @sultanSurya
    @sultanSurya 3 роки тому

    Learned new things today. Thanks for the tutorial.. Subscribed

  • @alickrodrigues9836
    @alickrodrigues9836 4 роки тому +1

    really informative and simple, please also put some complex use cases for a2019

  • @ashishsaraf3210
    @ashishsaraf3210 4 роки тому +2

    Very helpful video. If you can create a video on dictionary datatype, it will be helpful

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      I will explain the dictionary datatype with Email Automation, will share the video soon.
      Thank you!

  • @RAHULPATEL-oz3vg
    @RAHULPATEL-oz3vg 3 роки тому

    Thanks for sharing your knowledge

  • @zhenispirnepessov
    @zhenispirnepessov 3 роки тому +1

    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?

  • @lc-mc67
    @lc-mc67 4 роки тому +2

    Hi mam, greetings from Colombia, I had a question, this command only can used in 2000- 2016 version or the 2019 version works too?

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Hello and greetings to you too!
      This command should work for 2019 version too

    • @sajalgpt8
      @sajalgpt8 4 роки тому

      @@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''

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@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.

    • @sajalgpt8
      @sajalgpt8 4 роки тому

      MS Office 2019 it is

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@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.

  • @rahuljain6789
    @rahuljain6789 4 роки тому +1

    What's the use of session name under open excel action. I am starting from A2019 , no idea of A11.......

  • @arshadsiddieque7097
    @arshadsiddieque7097 4 роки тому +1

    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.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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

    • @arshadsiddieque7097
      @arshadsiddieque7097 4 роки тому +1

      @@qualityassurancelab7764 Thanks for your help. Really appreciate it.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Arshad Siddieque You are welcome

  • @Artech.Ranjit
    @Artech.Ranjit 2 роки тому

    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?

  • @davejuliant5550
    @davejuliant5550 4 роки тому +1

    you're great! very helpful, subscribed!

  • @Asd-wd7lr
    @Asd-wd7lr 3 роки тому

    Thank you so much for making this video 😄👍

  • @kirtanparmar9509
    @kirtanparmar9509 3 роки тому

    Can we use other action to read worksheet without using loop action?

  • @rohitsaigudimetla1267
    @rohitsaigudimetla1267 3 роки тому +1

    can we share our task to others in community edtion

  • @sousou617
    @sousou617 4 роки тому +1

    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!

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      Provide different session names in the actions for both excels and it should work.
      Please checkout this demo which performs the same task - ua-cam.com/video/Qsvm6OlBi9U/v-deo.html

  • @chandruviswanathan6636
    @chandruviswanathan6636 4 роки тому +1

    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.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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 - ua-cam.com/video/Qsvm6OlBi9U/v-deo.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!

    • @chandruviswanathan6636
      @chandruviswanathan6636 4 роки тому +1

      @@qualityassurancelab7764 Got it mam. Thanks for the response.

  • @abhijeetsingh089
    @abhijeetsingh089 4 роки тому +1

    Mam, please make a video on credentials,lockers and password usage in A2019

  • @rudranshrai27
    @rudranshrai27 2 роки тому +1

    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.

  • @rahuljain6789
    @rahuljain6789 4 роки тому +1

    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!!!

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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 - ua-cam.com/video/YEFtEyoGBkc/v-deo.html

    • @rahuljain6789
      @rahuljain6789 4 роки тому

      @@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🙂🙂

  • @adrijapramanik4932
    @adrijapramanik4932 4 роки тому +2

    Hi Mam in AA 2019 how to choose excel file from user and open that file?

    • @yugennair5533
      @yugennair5533 4 роки тому

      "Open" command under Excel advanced and browse for it.. It will connect with you machine local files directory window

  • @gunjanjohri132
    @gunjanjohri132 3 роки тому

    Hi , i need help to read data from multiple file and copy in 1 file. how can we do that

  • @ramn784
    @ramn784 3 роки тому +1

    if one of command is missed then loop will not wrk.what is the command to perform the loop?

  • @rajiv7
    @rajiv7 4 роки тому +1

    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.

    • @rajiv7
      @rajiv7 4 роки тому +1

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

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@rajiv7 Cool! you have already resolved the issue :) Yes, to pick correct delimiter is very important. Keep Learning!

  • @venkatkm8607
    @venkatkm8607 4 роки тому +1

    Able to see only the last row data [ie..3rd row data] as a message. Unable to see the previous 2 rows data.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Please confirm that you added the message box inside the loop, not outside

    • @venkatkm8607
      @venkatkm8607 4 роки тому

      @@qualityassurancelab7764 Thanks, It's working

  • @mitalipatel1462
    @mitalipatel1462 4 роки тому +1

    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'

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Hi, could you please let me know at which Action you got this error?

    • @mitalipatel1462
      @mitalipatel1462 4 роки тому

      Can you please give me your email author wise any contact so I contact to you mitalipatel244@gmail.com this is my mail id

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@mitalipatel1462 You can contact me at labqualityassurance@gmail.com

  • @RajKumar-yp9iq
    @RajKumar-yp9iq 4 роки тому

    Hello team can you help us with how excel filter activity works

  • @ashivishwakarma3985
    @ashivishwakarma3985 3 роки тому +1

    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?

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому +1

      Hi, please refer this video, similar case is explained here - ua-cam.com/video/2JhMAqnU1tQ/v-deo.html

    • @ashivishwakarma3985
      @ashivishwakarma3985 3 роки тому +1

      @@qualityassurancelab7764 thank you

  • @sandeepuniyal6817
    @sandeepuniyal6817 3 роки тому

    how can i upload multiple excel file if i want to automate multiple files from one folder?

  • @reychari
    @reychari 4 роки тому +1

    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

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      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 - ua-cam.com/video/xdwY9weTzXE/v-deo.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.

    • @reychari
      @reychari 4 роки тому +1

      @@qualityassurancelab7764 Thanks on step 2 I create the dictionary variable but where exactly do i put the dictionary Keys is it on step 3?

    • @reychari
      @reychari 4 роки тому +1

      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?

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@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.

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@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.

  • @rohantiwari95
    @rohantiwari95 3 роки тому +1

    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?

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому

      Did you get any error or the values are coming as blank?

    • @rohantiwari95
      @rohantiwari95 3 роки тому +1

      @@qualityassurancelab7764 The values are comig as blank

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому

      @@rohantiwari95 could you pls share all your actions screenshots at labqualityassurance@gmail.com?

    • @rohantiwari95
      @rohantiwari95 3 роки тому

      @@qualityassurancelab7764 sure

    • @rohantiwari95
      @rohantiwari95 3 роки тому

      @@qualityassurancelab7764 Team, I have shared you the screenshots on mail

  • @rashmi_lifestyle5573
    @rashmi_lifestyle5573 3 роки тому

    Instead of message box I have to write in notepad.. how ro do that

  • @somamahe4767
    @somamahe4767 4 роки тому +1

    Hi,
    I often get this message while running any bot in A2019- e. Can you please suggest a solution for this issue?

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Hi, please let me know the error

    • @somamahe4767
      @somamahe4767 4 роки тому +1

      @@qualityassurancelab7764 Bot Launcher for user session unresponsive message while running bots

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@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

  • @ramn784
    @ramn784 2 роки тому

    How can I transpose the Excel sheet data?

  • @sudhanshuvats329
    @sudhanshuvats329 4 роки тому +1

    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

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      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.

    • @sreevanikamarthi6302
      @sreevanikamarthi6302 4 роки тому

      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.

  • @venkateshnekkanti8213
    @venkateshnekkanti8213 3 роки тому

    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"

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому

      Hi, have you added Excel Advanced -> Close action under the loop? If yes, please add that outside the loop

    • @venkateshnekkanti8213
      @venkateshnekkanti8213 3 роки тому

      @@qualityassurancelab7764 Thank you madam it got resolved

  • @adrijapramanik4932
    @adrijapramanik4932 4 роки тому +1

    Hi Mam how to choose excel file from user

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      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 - ua-cam.com/video/oQ_ay0DeWw8/v-deo.html

  • @abhijeetsingh089
    @abhijeetsingh089 4 роки тому +1

    Thanks Mam

  • @abhijeetsingh089
    @abhijeetsingh089 4 роки тому +1

    Mam,where can I find bot store in A2019

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      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.

  • @pallavis4934
    @pallavis4934 3 роки тому +1

    How to create employees excel sheet

    • @qualityassurancelab7764
      @qualityassurancelab7764  3 роки тому

      Do you mean create excel sheet using AA? If yes, please use Excel Advanced -> Create Workbook, Create Worksheet

  • @adityakrishnan9620
    @adityakrishnan9620 4 роки тому

    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

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому +1

      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.

    • @adityakrishnan9620
      @adityakrishnan9620 4 роки тому

      @@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.

    • @adityakrishnan9620
      @adityakrishnan9620 4 роки тому +1

      @@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 !

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      @@adityakrishnan9620 Great, thanks for confirming!

  • @ramakrishnatirumala428
    @ramakrishnatirumala428 4 роки тому +1

    do more videos on A2019 madam

    • @qualityassurancelab7764
      @qualityassurancelab7764  4 роки тому

      Hi, please checkout A2019 playlist - ua-cam.com/play/PLkkGqdBS1r0_cvaLxbh0UvgkS20t9m2w5.html
      I will upload more A2019 videos.

  • @manoharnookala4212
    @manoharnookala4212 3 роки тому

    Why my bot reading only the last row data??

  • @mdistiyak6460
    @mdistiyak6460 4 роки тому

    Bot Launcher for user session HP-PC\MD RAMIZ unresponsive.
    If the problem persists, please contact your system administrator
    Help me solve this issue

  • @BillyJoeReactNPlays
    @BillyJoeReactNPlays 3 роки тому +1

    it sure was useful, but she sure does sound like a robot.

  • @KiranKumar-it1sb
    @KiranKumar-it1sb 2 роки тому

    Hey quality assurance girl,
    Where r u,i need to listen ur voice,if possible show ur face na