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:

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

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

Procedure

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

Create a new bot:
1. On the left panel, click Automation.
2. Click Create new > 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.
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.
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.
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:
Disconnect from the database. Drag the Database > Disconnect action below the Loop container.
Close the spreadsheet. Double-click or drag Excel advanced > Close Spreadsheet action.
Click Save.

Automation 360

Example of migrating data from Excel to a database