Jump to content
milkmoron

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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 :)

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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)

 

 

Share this post


Link to post
Share on other sites
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.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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)?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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. :)

 

Share this post


Link to post
Share on other sites
JLogan3o13
Posted (edited)

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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.

Search Picture.png

Share this post


Link to post
Share on other sites
milkmoron

Tried the array display it works.

Share this post


Link to post
Share on other sites
FrancescoDiMuro
Posted (edited)
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 by FrancescoDiMuro
  • Like 1

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
FrancescoDiMuro

ControlSend() for sure :)

Best Regards.

  • Like 1

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
milkmoron
Posted (edited)
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 by milkmoron

Share this post


Link to post
Share on other sites
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.


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×