Read and Review Automation Anywhere Documentation

Automation 360

Close Contents

Contents

Open Contents

Example of migrating data from Excel to a database

  • Updated: 11/17/2020
    • 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.

Prerequisites

Before you start building your bot, create the following:

  • Excel spreadsheet with the following values:
    John Williams jwilliams@gmail.com
    Sam Li sam.li@xyz.org
    Carl Miller carl@carlmillerllc.com
  • Access database table named CustomerT with the following columns:
    • FirstName
    • LastName
    • Email

Procedure

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.
    3. In the Create Task Bot window, enter a bot name.
    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. In the Assign the current value to this variable, create the record variable rExcelCurrentRow.
    4. Drag the Database > Insert/Update/Delete action into the Loop container.
    5. 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