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:
John Williams firstname.lastname@example.org Sam Li email@example.com Carl Miller firstname.lastname@example.org
- 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)
To migrate values from a spreadsheet to a table in a database, perform the following steps:
Open a new bot:
- On the left panel, click Automation.
- In the Create Task Bot window, enter the bot name. For more information about choosing bot names, see bot naming conventions.
Accept the default folder location: \Bots\
To change where your bot is stored, click Choose and follow the prompts.
- Click Create and edit.
Open the spreadsheet:
- Double-click or drag the action .
Click Browse to provide the file path.
- Select the option to open the file in Read-write.
Connect to the database:
- Double-click or drag the action.
Select the User defined connection mode.
- Select the Microsoft Access database type.
- Click Browse to provide the file path.
Insert the Excel values into the database, row by row:
- Double-click or drag the Loop action.
- Select the iterator.
Select All rows from the Loop
In the Assign the current value to this variable, create the record
- Drag the action into the Loop container.
Enter the following SQL statement:
INSERT INTO CustomerT (FirstName,LastName,Email) values ('$rExcelCurrentRow$','$rExcelCurrentRow$','$rExcelCurrentRow$');
- Disconnect from the database. Drag the action below the Loop container.
- Close the spreadsheet. Double-click or drag action.
- Click Save.