Articles → AUTOMATION ANYWHERE → Excel As A Database In A2019

Excel As A Database In A2019






Prerequisite





Sample Excel




Picture showing the sample excel to be read as a database
Click to Enlarge


Example




  1. Connect to the excel.
  2. Export data to datatable.
  3. Loop through all rows of the data table.



Connect To Excel




  1. Drag and drop the "Connect" action of the "Database" activity.
    2. Picture showing the Connect action in the Database activity
    Click to Enlarge

  2. In the property window, set the "Connection Mode" as "Default" and "Connection String" as 
    3. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\karan\OneDrive\Desktop\a.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"


    Picture showing setting the connection string and connection mode of the connect action
    Click to Enlarge



Export Data To Datatable




  1. Drag and drop the "Export to data table" action from the "Database" command.
    2. Picture showing the Export to data table action from the Database command.
    Click to Enlarge

  2. In the property window, enter the select statement and assign the result of the select statement to a variable.
    3. Picture showing the select query in export to data table
    Click to Enlarge



Loop Through All The Rows Of The Datatable.




Picture showing the loop for iterating through the datatable
Click to Enlarge


Output





Posted By  -  Karan Gupta
 
Posted On  -  Sunday, December 27, 2020

Query/Feedback


Your Email Id
 
Subject
 
Query/FeedbackCharacters remaining 250