T69 - Migrate data from Excel to Database
|
Task Description |
Migrate data from Excel to Acess database.
 T69.atmn  MyExcel.xls  T69MyDB.mdb
Note: Save the xls in C: drive, the .atmn file in My Documents -> Automation Anywhere -> Automation Anywhere -> My Tasks
|
| Solution |
 |
Open Task Editor to create a new task
- Click on New -> Task Editor
|
Screenshot 1
Screenshot 2
Screenshot 3 |
 |
Get data from Excel
- Using Excel:Open Spreadsheet command, open excel file containing data. You can find Excel command under ‘Applications’ command category.
- Use ‘Get Multiple Cells’ option of Excel command to get the range of data you need to transfer. Specify the range as shown in Screenshot1.
|
 |
Connect to the database and import the data
- Using 'Database:Connect' command, specify the connection string to connect to the Database with Session Name, Default.
- When you have retrieved a range of cells from Excel using Excel > Get Cells > Get Multiple Cells, the rows and columns of the range are stored in the system variable $Excel Column (index)$, which you can use in “Loop for each row in an Excel Dataset”. Insert “Loop for each row in an Excel Dataset” command from ‘Conditions/Loops’ command category.
- Double click Database command from the Application category, select Insert/Update/Delete Option and specify Insert SQL Statement to insert the data from Excel Dataset into OrderMaster table. Screenshot2
|
 |
How to run the task:
- In case you do not have Automation Anywhere installed, please download and install it from, http://www.automationanywhere.com/download/freeTrial.htm
- Save T69.atmn under location, My Documents\Automation Anywhere\Automation Anywhere\My Tasks
- Save MyExcel.xls and T69MyDB.mdb under C:\..
- Launch Automation Anywhere and from Task List select T69.atmn and click on RUN button to run the task.
|
|
| |
| 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 >> |
|
