In this example, you build a bot to transfer values from an Excel spreadsheet to a database using actions from the Database, Excel advanced, and Loop packages.

Prerequisites

Before you start building your bot, create the following:

  • Create an Excel spreadsheet with the following values and save it (For example: Test_migration_db.xlsx)
    John Williams jwilliams@cmail.com
    Sam Li sam.li@xyz.org
    Carl Miller carl@carlmillerllc.com

    Excel to db migration
  • Create an Access database table named CustomerT with the following columns: (create a database similar to the image below and save it - for example: save it as Test_Migration_db.accdb)
    • FirstName
    • LastName
    • Email
      Access db

Procedure

To migrate values from a spreadsheet to a table in a database, perform the following steps:

  1. Create a new bot:
    1. On the left panel, click Automation.
    2. Click + Create > Task Bot.
    3. In the Create Task Bot window, enter the bot name.
    4. Accept the default folder location: \Bots\
      To change the default bot storage location, click Choose and follow the prompts.
    5. Click Create and edit.
    Note: The default maximum size for individual bot is 50 MB.
  2. Open the spreadsheet:
    1. Double-click or drag the Excel advanced > Open action .
    2. Click Browse to provide the file path.
      Provide filepath
    3. Select the option to open the file in Read-write.
  3. Connect to the database:
    1. Double-click or drag the Database > Connect action.
    2. Select the User defined connection mode.
      User defined connection
    3. Select the Microsoft Access database type.
    4. Click Browse to provide the file path.
  4. Insert the Excel values into the database, row by row:
    1. Double-click or drag the Loop action.
    2. Select the Excel advanced > For each row in worksheet iterator.
    3. Select All rows from the Loop through drop-down.
      Loop through the Rows
    4. In the Assign the current value to this variable, create the record variable rExcelCurrentRow.
    5. Drag the Database > Insert/Update/Delete action into the Loop container.
    6. Enter the following SQL statement:
      Sample SQL
      Enter SQL statement
  5. Disconnect from the database. Drag the Database > Disconnect action below the Loop container.
  6. Close the spreadsheet. Double-click or drag Excel advanced > Close Spreadsheet action.
  7. Click Save.