Minimal Scripting Skills - Need help with a script to do Data Entry

[milkmoron]

Hello,

I do IT work at a small insurance agency. I am trying to create a script to search for data on another agency's Customer Database system and insert their information into ours. We are trying to move all their old client data and merge systems. The system we use is AMS360.

What I need to do is search for a customer from an excel list and then search for it in the system which is based on a web browser select the policy if it matches certain criteria (Date and Policy Type) and select the policy which launches a program that has all the data. I then need it to create a new customer and policy on our side and copy over the old data into our system. The information we need is in expandable drop downs and some must be clicked into for a new window.

Can anyone help with this? Has anyone tried to do something similar. Where should I start in order to learn the skills to create this. I have used AutoIt before but only basic things like mouse clicks.

Thanks

[JLogan3o13]

@milkmoron welcome to the forum. The best bet when you're new (and we were all on Day 1 once), is to break the problem down and tackle a step at a time. In your case, I would go with the Excel piece, as that is likely to be the easiest. AutoIt comes with a great Excel UDF, which should allow you to do what you need pretty painlessly. Take a look in the help file for the _Excel_* functions. In essence, you would do something like this to begin:

#include <Excel.au3>

Local $oExcel = _Excel_Open() ;Create an Excel Object
Local $oWorkbook = _Excel_BookOpen($oExcel, <Path to your Excel file>) ;Open an Excel Workbook

Sleep(2000)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

Try this, adding in the path to your file in the _Excel_BookOpen command. Then we'll add in some additional logic.

As you are trying this, think about a couple of things:

• If this is an infrequent task, or if the Excel spreadsheet is changed often, it is probably better to open the workbook, find the customer, pull all of their info, then close the book.
• If, on the other hand, this is something of a batch process - handling multiple customers at once - and especially if the data will not change often, it is probably better to open the book once, read all of it into an array (depending on size) and then close the book. Then your script can simply reference the array going forward.

If you run into any issues, post back here and we'll do our best to help

 

[milkmoron]

I got this far. How would I read the data from the excel sheet?

Quote

 

#include <Excel.au3>

Local $oExcel = _Excel_Open() ;Create an Excel Object
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Autoit\Renewal List.xls") ;Open an Excel Workbook

Sleep(2000)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

 

 

[FrancescoDiMuro]

@milkmoron
A warm welcome to the AutoIt forum
To read data from an Excel file, you can use _Excel_RangeRead()
I suggest to you to always read the Helo file, because I'm sure you will find it as one of your best friends for learning this amazing programming language
Specify your range in the function, or create a loop to navigate through it
If you have any question, please remember always to poste the code you are working with/on, so we can help you where you're at


Best Regards.

[milkmoron]

#include <Excel.au3>

Local $oExcel = _Excel_Open() ;Create an Excel Object
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Autoit\Renewal List.xls") ;Open an Excel Workbook

Local $aResult = _Excel_RangeRead($oWorkbook, Default, "A1:A200", 1)

Sleep(2000)

_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

Got this not sure if its reading but it runs with no errors

[FrancescoDiMuro]

1 minute ago, milkmoron said:

Got this not sure if its reading but it runs with no errors

You got it
To check if there is an error in when you run some function, there is a special macro in AutoIt, which is represented from @error.
So, if you look closely in the Help file about _Excel_* functions, you can see that almost every function returns an @error code, and some, an @extended code too.
Those information, according to the Help file, are saying what is going on with the function calling ( and the Return Value does it too! ).
So, when you call _Excel_* functions, I suggest to do something like:
 

; Calling Excel function
$objExcel = _Excel_Open()
; Here it is the Error Checking/Handling
If @error Then
    ; Do something related to the error...
    ; Display it, skip it...
Else
    ; Continue with the code...
EndIf

Best Regards.

[JLogan3o13]

@milkmoron so what would your answer be to my questions in my first response. Are you thinking you need to open this excel spreadsheet multiple times (changes frequently) or just once (infrequent changes, or small number of rows)?

[milkmoron]

3 minutes ago, JLogan3o13 said:

@milkmoron so what would your answer be to my questions in my first response. Are you thinking you need to open this excel spreadsheet multiple times (changes frequently) or just once (infrequent changes, or small number of rows)?

It doesn't change frequently but I need to read about 200 data points. The data does not change it just stays the same. I'm reading an excel of old clients that wouldn't include anything new

6 minutes ago, FrancescoDiMuro said:

You got it
To check if there is an error in when you run some function, there is a special macro in AutoIt, which is represented from @error.
So, if you look closely in the Help file about _Excel_* functions, you can see that almost every function returns an @error code, and some, an @extended code too.
Those information, according to the Help file, are saying what is going on with the function calling ( and the Return Value does it too! ).
So, when you call _Excel_* functions, I suggest to do something like:
 

; Calling Excel function
$objExcel = _Excel_Open()
; Here it is the Error Checking/Handling
If @error Then
    ; Do something related to the error...
    ; Display it, skip it...
Else
    ; Continue with the code...
EndIf

Best Regards.

Thanks I'll try that next time.

[wolflake]

1 hour ago, milkmoron said:

Got this not sure if its reading but it runs with no errors

@milkmoron I like to see what I've got in my array so if you #include <array.au3>

you can use _ArrayDisplay($aResult)  to see what you got and then comment it out when you are satisfied.

 

[JLogan3o13]

So then I would do something like this:

#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\CustomerDB.xlsx")
Local $aRange = _Excel_RangeRead($oWorkbook, Default)
    
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

_ArrayDisplay($aRange) ;Purely for testing so you can see what the Array looks like

So at this point you have the entire contents of the Default worksheet within the workbook in an Array, and you can work with the elements of that array at your leisure without keeping the Workbook open.

From here, we need more info on what you want to do exactly with the info in the array. Posting the spreadsheet (or a reasonable facsimile that shows the architecture of it) would help greatly.

Edited July 11, 2018 by JLogan3o13

[milkmoron]

46 minutes ago, JLogan3o13 said:

So then I would do something like this:

#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\CustomerDB.xlsx")
Local $aRange = _Excel_RangeRead($oWorkbook, Default)
    
_Excel_BookClose($oWorkbook)
_Excel_Close($oExcel)

_ArrayDisplay($aRange) ;Purely for testing so you can see what the Array looks like

So at this point you have the entire contents of the Default worksheet within the workbook in an Array, and you can work with the elements of that array at your leisure without keeping the Workbook open.

From here, we need more info on what you want to do exactly with the info in the array. Posting the spreadsheet (or a reasonable facsimile that shows the architecture of it) would help greatly.

Thanks I'll try it out

Im trying to search business and client names from a single column and paste it into the search bar of the online web system. After that I want to copy text fields from the other agencies system into our system.

[milkmoron]

Tried the array display it works.

[FrancescoDiMuro]

6 hours ago, milkmoron said:

Im trying to search business and client names from a single column and paste it into the search bar of the online web system. After that I want to copy text fields from the other agencies system into our system.

Look at Control* functions, and Win* functions in the Help file

Edited July 12, 2018 by FrancescoDiMuro

[milkmoron]

10 hours ago, FrancescoDiMuro said:

Look at Control* functions, and Win* functions in the Help file

Thanks for the response. There's a lot of functions to look up do you happen to know off the top of your head the ones I should look at?

[FrancescoDiMuro]

ControlSend() for sure

Best Regards.

[milkmoron]

5 hours ago, FrancescoDiMuro said:

ControlSend() for sure

Best Regards.

Is title the title at the top of the window? If so what would it be on webbrowser

[wolflake]

5 hours ago, milkmoron said:

Is title the title at the top of the window? If so what would it be on webbrowser

Yes and you can see using the Au3Info.exe program in the autoit root directory.  When you run the prg drag the "finder tool" over the browser window to see what the title would be.  On my browser it's the name of the active tab.

[milkmoron]

On 7/12/2018 at 8:36 PM, wolflake said:

Yes and you can see using the Au3Info.exe program in the autoit root directory.  When you run the prg drag the "finder tool" over the browser window to see what the title would be.  On my browser it's the name of the active tab.

Thanks It worked. How do i find the controlid of a search field in a chrome browser? Is it easier to just use mouse coords?

 

Edited July 16, 2018 by milkmoron

[FrancescoDiMuro]

9 minutes ago, milkmoron said:

How do i find the controlid of a search field in a chrome browser? Is it easier to just use mouse coords?

You should take a look at the source code of the page ( shouldn't be so hard with actual browsers ).
To automate, you can use the _IE* functions, or other UDFs

Best Regards.

[milkmoron]

3 minutes ago, FrancescoDiMuro said:

You should take a look at the source code of the page ( shouldn't be so hard with actual browsers ).
To automate, you can use the _IE* functions, or other UDFs

Best Regards.

<input type="text" size="24" autocomplete="off" id="search-text" name="search-text" class="x-form-text x-form-field x-form-focus" style="width: 213px;" tabindex="1001">

Is the ID search-text?