Read and Review Automation Anywhere Documentation

Automation 360

Close Contents


Open Contents

Example of migrating data from Excel to a database

  • Updated: 2022/05/26
    • Automation 360 v.x
    • Build
    • RPA Workspace
    • Examples

Example of migrating data from Excel to a database

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.


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
    Sam Li
    Carl Miller

  • 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


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

  1. Open a new bot:
    1. On the left panel, click Automation.
    2. Click Create new > Bot.
      Create a new bot
    3. In the Create Task Bot window, enter the bot name. For more information about choosing bot names, see bot naming conventions.
    4. Accept the default folder location: \Bots\
      To change where your bot is stored, click Choose and follow the prompts.
    5. Click Create and edit.
  2. Open the spreadsheet:
    1. Double-click or drag the Excel advanced > Open action .
    2. Click Browse to provide the file path.

    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.

    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.

    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:
      INSERT INTO CustomerT (FirstName,LastName,Email) values ('$rExcelCurrentRow[0]$','$rExcelCurrentRow[1]$','$rExcelCurrentRow[2]$');

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