T69 - Migrate data from Excel to Database
|
| Task Description |
Migrate data from Excel to Database.Solution
|
| Solution |
 |
Create new task
Click on New -> Task Editor. |
Screenshot 1
Screenshot 2
Screenshot 3 |
 |
Connect to the Database
Using the Database command from the Application category, specify a connection string to connect to the Database.
Connect to '<database connection string>' |
 |
Copy records from Excel
- Let's say you want to transfer Excel records.
- Create 4 variables, $Firstname$, $Lastname$, $City$ and $Country$ of type value from Tools->Variable Manager.
- Copy each cell from Excel and assign the value of Clipboard to respective variables using 'Assign From Clipboard' option in Clipboard command.
|
 |
Transfer records to the Database
- Specify Insert query to insert records in destination database. The insert query for this example looks like as follows
- INSERT INTO Addresses ( FirstName, [Last Name], City, Country ) VALUES ('$FirstName$', '$LastName$', '$City$', '$Country$');
- Put these commands in a Loop for as many times as you want to transfer the records from Excel.
- The final task would look as shown in Screenshot 3
|
|
| |
| When you run this task, it connects to the source database, copies the excel records, with insert query in loop it will add all the records to destination table in the database. This tutorial demonstrates use of one of the very powerful features of Automation Anywhere, 'Database' applications. Use this when you want to migrate large numbers of records from excel or other applications to any database. |
|
 |
| This has to be the best automation software around. I tried the competition and even combined they offer only a tiny fraction of the functionality of this program! The first macro I wrote will save me about 20 minutes every day. This updates a database from online resources that I did manually. Windows scheduler could never perform this task because it requires user input. Setting up the macro took about 2 minutes. Editing it about another 2 minutes. I schedule it to run after midnight and now I don't have to worry about it. I am now creating a list of regular tasks that can be done using Automation. Excellent work guys! |
- Mike Montgomery,
MjM Data Recovery Ltd
|
| Read Customer Testimonies >> |
|
