Copy from excel to ticketing system

[bum2kev]

The company I work for has a horrible IT ticketing system. it takes forever to enter information especially since a lot of it is redundant...

I created a script that would speed things up A LOT but im looking to make it much better.

this is what i have so far and it works pretty well....the only thing is that im trying to do is instead of having to type in the tech, inumber, short, long is to just have it copy from a spreadsheet in excel. and when it reaches the end of the spreadsheet or hits a blank spot it just stops. is there any way to do this??

expandcollapse popup

$TECH = InputBox("IT", "Please enter Technician I/C#") 
$INUMBER = InputBox("IT", "Please enter users I/C/D #") 
$SHORT = InputBox("IT", "Please enter the short text")  
$LONG = InputBox("IT", "Please enter the long text") 
Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Inbox:","")
If Not WinActive("Inbox:","") Then WinActivate("Inbox:","")
WinWaitActive("Inbox:","")
MouseMove(448,499)
MouseDown("left")
MouseUp("left")
WinWait("Create Messages for Other Employees or External Users","")
If Not WinActive("Create Messages for Other Employees or External Users","") Then WinActivate("Create Messages for Other Employees or External Users","")
WinWaitActive("Create Messages for Other Employees or External Users","")
Send($INUMBER)
Send("{ENTER}{ENTER}")
sleep("1000")
Send("{ENTER}")
WinWait("Create IT Support Ticket","")
If Not WinActive("Create IT Support Ticket","") Then WinActivate("Create IT Support Ticket","")
WinWaitActive("Create IT Support Ticket","")
Send("{SHIFTDOWN}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}

{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{SHIFTUP}")
Send("{DEL}pc-work_sw{TAB}{TAB}{TAB}")
Send($TECH)
Send($SHORT)
Send("{TAB}{TAB}")
Send($LONG)
Send("{CTRLDOWN}s{CTRLUP}")
sleep("200")
Send("{ENTER}")
WinWait("Please enter the message in English","")
If Not WinActive("Please enter the message in English","") Then WinActivate("Please enter the message in English","")
WinWaitActive("Please enter the message in English","")
Send("{ENTER}")
MouseDown("left")
MouseUp("left")
WinWait("Processing Organization","")
If Not WinActive("Processing Organization","") Then WinActivate("Processing Organization","")
WinWaitActive("Processing Organization","")
Send("{TAB}{SPACE}")
WinWait("Favorites","")
If Not WinActive("Favorites","") Then WinActivate("Favorites","")
WinWaitActive("Favorites","")
Send("{TAB}{TAB}{TAB}{TAB}{DOWN}{DOWN}{F2}")
WinWait("Processing Organization","")
If Not WinActive("Processing Organization","") Then WinActivate("Processing Organization","")
WinWaitActive("Processing Organization","")
Send("{TAB}{TAB}")
Send("{SPACE}")

you will notice that there are a lot of tabs in the script, its because there are not any button identifiers within the application window in this app.

this is what im trying to get it to copy off of.

Thanks!

[herewasplato]

Welcome to the forums.

You can specify multiple tabs (and some other things) like this:

Send("{SHIFTDOWN}{TAB 15}{SHIFTUP}")

If you saved the spreadsheet in the CSV file format, you can read it in as text... or you can dive in to the Excel UDFs and still read the info into AutoIt for posting to your company's ticket app.

If you opt for the CSV route, take a look in the help file for these functions/UDFs:

_FileReadToArray

for/next loops

StringSplit

[bum2kev]

Thanks for the tip on getting rid of all my TAB keys

I'm trying to understand the _FileReadToArray but im not really sure where to start

this is my first script with autoit so im pretty much a newbie.

So if i have my csv file like this.

I820441,i816221,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,i002133,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,C098717,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,D000002,This is a test ticket,testing to make sure it creates a ticket correctly

can i send the I820441 to $TECH, i816221 to $INUMBER and so on?

[bum2kev]

so i was looking through how _FileReadToArray works but im still not sure

so if i have the command

_FileReadToArray($sFilePath, ByRef $aArray)

would i set my $TECH to $aTech?

[PsaltyDS]

so i was looking through how _FileReadToArray works but im still not sure

so if i have the command

_FileReadToArray($sFilePath, ByRef $aArray)

would i set my $TECH to $aTech?

You only need to read the file into an array once. Then you loop through the array one element (line from the file) at a time and StringSplit() the line at the commas. This will produce another array containing the individual CSV elements of that line. Short demo:

#include <File.au3>
#include <Array.au3>; Only for _ArrayDisplay()

Global $sCSVFile = @ScriptDir & "\MyFile.csv"
Global $avCSVFile, $avCSVLine, $sTech, $iNumber

_FileReadToArray($sCSVFile, $avCSVFile)
If @error Then
    MsgBox(16, "Error", "Failed to read file:  " & $sCSVFile)
    Exit
Else
    _ArrayDisplay($avCSVFile, "Debug: $avCSVFile")
EndIf

For $n = 1 To $avCSVFile[0]
    $avCSVLine = StringSplit($avCSVFile[$n], ",")
    _ArrayDisplay($avCSVLine, "Debug: Line = " & $n)
    If $avCSVLine[0] >= 2 Then
        $sTech = $avCSVLine[1]
        $iNumber = $avCSVLine[2]
        MsgBox(64, "Results", "Line = " & $n & "  $sTech = " & $sTech & "  $iNumber = " & $iNumber)
    Else
        MsgBox(16, "Error", "Line " & $n & " is malformed, not enough fields.")
    EndIf
Next

Cheers!

[Bert]

This ticket system wouldn't be HP ServiceCenter would it?

[bum2kev]

nope its an old SAP backend system

[bum2kev]

Ok so i put the rest of the fields into the script.

#include <File.au3>
#include <Array.au3>; Only for _ArrayDisplay()

Global $sCSVFile = @ScriptDir & "\Tickets.csv"
Global $avCSVFile, $avCSVLine, $tech, $iNumber, $short, $long

_FileReadToArray($sCSVFile, $avCSVFile)
If @error Then
    MsgBox(16, "Error", "Failed to read file:  " & $sCSVFile)
    Exit
Else
    _ArrayDisplay($avCSVFile, "Debug: $avCSVFile")
EndIf

For $n = 1 To $avCSVFile[0]
    $avCSVLine = StringSplit($avCSVFile[$n], ",")
    _ArrayDisplay($avCSVLine, "Debug: Line = " & $n)
    If $avCSVLine[0] >= 4 Then
        $tech = $avCSVLine[1]
        $iNumber = $avCSVLine[2]
        $short = $avCSVLine[3]
        $long = $avCSVLine[4]
        MsgBox(64, "Results", "TICKET " & $n & "  $tech = " & $tech & "  $iNumber = " & $iNumber & "  $short = " & $short & "  $long = " & $long)
    Else
        MsgBox(16, "Error", "Line " & $n & " is malformed, not enough fields.")
    EndIf
Next

How do i get it to input the arrays into the fields instead of just outputting to a popup?

thanks for all the help guys!

[PsaltyDS]

Ok so i put the rest of the fields into the script.

How do i get it to input the arrays into the fields instead of just outputting to a popup?

thanks for all the help guys!

Have you looked at the controls in the app with AU3Info.exe? What kind of controls are they? Most likely ControlSend(), ControlClick(), etc. will get the job done.

[bum2kev]

There are none, thats the problem.

thats why i have the menu items and $tech $inumber $short $long and in the actual script that writes to the ticketsystem its tabbing to the area it needs to be in and sending the keys.

ill tell you how this all is working in my head might make it easier

I have a CSV file that i update, and at the end of the day i have a list of 40 or so. (FYI, i820441 is my ID number)

like this...

I820441,i816221,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,i002133,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,C098717,This is a test ticket,testing to make sure it creates a ticket correctly

I820441,D000002,This is a test ticket,testing to make sure it creates a ticket correctly

1.I open the ticketing system to the point where autoit can click on the create ticket button (this is to create a ticket in someone elses name)

2 AUTOIT reads the info in the CSV into an array. it takes the info from line 1 column 2 which is the users ID number. so it copies i816221 into the user field....hits enter twice and it opens the ticket info box

3. it tabs about 30 times to the next field and by default types in pc-work_sw... hits tab a few more times and inputs the info from line 1 column 1 (i820441) and types it into the tech field

4 hits tab and inputs from line 1 column 3 into ($short)

5 tab again and inputs from line 1 coumn 4 into ($long)

it saves the ticket and it goes right back to the create ticket window and repeats with lines 2, 3, 4 and so on.

i hope this help.

Edited January 21, 2009 by bum2kev

[PsaltyDS]

There are none, thats the problem.

thats why i have the menu items and $tech $inumber $short $long and in the actual script that writes to the ticketsystem its tabbing to the area it needs to be in and sending the keys.

Then you'll just have to TAB to the control and ControlSend() the data (control IDs are not required, but ControlSend is still better than Send). Something like:

WinWait("Create IT Support Ticket","")
$hWin = WinGetHandle("Create IT Support Ticket","")
WinActivate($hWin)
WinWaitActive($hWin)
ControlSend($hWin, "", "", "+{TAB 30}")
ControlSend($hWin, "", "", $avCSVLine[2])

The string "+{TAB 30}", by the way, sends Shift-TAB 30 times, and beats the heck out of this:

Send("{SHIFTDOWN}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}




{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{SHIFTUP}")

P.S. If the form is very consistent you might also use MouseClick() with X/Y coordinates in the window to select fields to edit.

Edited January 21, 2009 by PsaltyDS

[bum2kev]

im not really sure how to implement the array script into my script.

I changed all the tab keys so there arent as many.

here is an example of what i changed.

If Not WinActive("Create IT Support Ticket","") Then WinActivate("Create IT Support Ticket","")
WinWaitActive("Create IT Support Ticket","")
Send("{SHIFTDOWN}{TAB 30}{SHIFTUP}")
Send("{DEL}pc-work_sw{TAB 3}")
Send($avCSVLine[1])
Send("{TAB}")
Send($avCSVLine[3])
Send("{TAB 2}")
Send($avCSVLine[4])
Send("{CTRLDOWN}s{CTRLUP}")

but when i put the array part at the top of my script all i get is errors....

[PsaltyDS]

im not really sure how to implement the array script into my script.

I changed all the tab keys so there arent as many.

here is an example of what i changed.

If Not WinActive("Create IT Support Ticket","") Then WinActivate("Create IT Support Ticket","")
WinWaitActive("Create IT Support Ticket","")
Send("{SHIFTDOWN}{TAB 30}{SHIFTUP}")
Send("{DEL}pc-work_sw{TAB 3}")
Send($avCSVLine[1])
Send("{TAB}")
Send($avCSVLine[3])
Send("{TAB 2}")
Send($avCSVLine[4])
Send("{CTRLDOWN}s{CTRLUP}")

but when i put the array part at the top of my script all i get is errors....

Well, you can clean up the Send Key code a little more:

Send("+{TAB 30}")
Send("{DEL}pc-work_sw{TAB 3}")
Send($avCSVLine[1])
Send("{TAB}")
Send($avCSVLine[3])
Send("{TAB 2}")
Send($avCSVLine[4])
Send("^s")

Check out the help file on it under Send(). I would still prefer ControlSend() to Send(), but you should be able to work with that.

As for your errors, the IPB_Crystal_Ball function was disabled by Valik months ago. What does that part of the code look like? Exactly what errors?

[bum2kev]

I tried control send, but the problem is that there arent any controls inside the program window itself. hence all the tabs lol.

the window info tool doesnt give me any info except for a few buttons....and they arent ones that i use at all.

i cleaned up the initial script like you suggested. Much better!

what im stuck on now is getting

this

Global $sCSVFile = @ScriptDir & "\Tickets.csv"
Global $avCSVFile, $avCSVLine, $tech, $iNumber, $short, $long

_FileReadToArray($sCSVFile, $avCSVFile)
If @error Then
    MsgBox(16, "Error", "Failed to read file:  " & $sCSVFile)
    Exit
Else
    _ArrayDisplay($avCSVFile, "Debug: $avCSVFile")
EndIf

For $n = 1 To $avCSVFile[0]
    $avCSVLine = StringSplit($avCSVFile[$n], ",")
    _ArrayDisplay($avCSVLine, "Debug: Line = " & $n)
    If $avCSVLine[0] >= 4 Then
        $tech = $avCSVLine[1]
        $iNumber = $avCSVLine[2]
        $short = $avCSVLine[3]
        $long = $avCSVLine[4]
        MsgBox(64, "Results", "TICKET " & $n & "  $tech = " & $tech & "  $iNumber = " & $iNumber & "  $short = " & $short & "  $long = " & $long)
    Else
        MsgBox(16, "Error", "Line " & $n & " is malformed, not enough fields.")
    EndIf
Next

to read this

I820441,i816221,This is a test ticket,testing to make sure it creates a ticket correctly
I820441,i002133,This is a test ticket,testing to make sure it creates a ticket correctly
I820441,C098717,This is a test ticket,testing to make sure it creates a ticket correctly
I820441,D000002,This is a test ticket,testing to make sure it creates a ticket correctly

to put them into the avCSVline

expandcollapse popup

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",4)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
WinWait("Inbox:","")
If Not WinActive("Inbox:","") Then WinActivate("Inbox:","")
WinWaitActive("Inbox:","")
MouseMove(448,499)
MouseDown("left")
MouseUp("left")
WinWait("Create Messages","")
Send($avCSVLine[2])
Send("{ENTER 2}")
sleep("1000")
Send("{ENTER}")
WinWait("Create IT Support Ticket","")
Send("+{TAB 30}")
Send("{DEL}pc-work_sw{TAB 3}")
Send($avCSVLine[1])
Send("{TAB}")
Send($avCSVLine[3])
Send("{TAB 2}")
Send($avCSVLine[4])
Send("^s")
sleep("200")
Send("{ENTER}")
WinWait("Please enter the message in English","")
Send("{ENTER}")
MouseDown("left")
MouseUp("left")
WinWait("Processing Organization","")
Send("{TAB}{SPACE}")
WinWait("Favorites","")
sleep("200")
Send("{TAB 4}{DOWN 2}{F2}")
WinWait("Processing Organization","")
sleep("300")
Send("{TAB 2}")
Send("{SPACE}")

sorry if im pestering you guys a lot, im new the autoit.

[herewasplato]

I tried control send, but the problem is that there arent any controls inside the program window itself. hence all the tabs lol. ...

You have to read every word of every post in your thread :-)

Post 11

(control IDs are not required, but ControlSend is still better than Send).

Then PsaltyDS shows what that syntax might look like for you:

ControlSend($hWin, "", "", "+{TAB 30}")

Don't let the WinGetHandle throw you off track - read about it - it is a good coding method.

As for you question about how to use the array in your main script - I'll have to leave that to someone that is more awake than I am - or - who can write code in their sleep :-)

[PsaltyDS]

I was trying to point bum2kev in this direction:

Opt("WinWaitDelay", 100)
Opt("WinTitleMatchMode", 4); 4 = Advanced mode
Opt("WinDetectHiddenText", 1); Detect hidden text
Opt("MouseCoordMode", 0); 0 = Relative coords to the active window

WinWait("Inbox:", "")
$hInbox = WinGetHandle("Inbox:", "")
WinActivate($hInbox)
WinWaitActive($hInbox)
MouseClick("left", 448, 499)

WinWait("Create Messages", "")
$hCMsg = WinGetHandle("Create Messages", "")
WinActivate($hCMsg)
WinWaitActive($hCMsg)
ControlSend($hCMsg, "", "", $avCSVLine[2])
ControlSend($hCMsg, "", "", "{ENTER 2}")
Sleep("1000")
ControlSend($hCMsg, "", "", "{ENTER}")

But several lines for identifying the window, getting the handle and making sure it's active are repeated more than twice, so I would put that in a function:

expandcollapse popup

Opt("MouseCoordMode", 0); 0 = Relative coords to the active window

$hInbox = _WinWaitAndGet("Inbox:", "")
MouseClick("left", 448, 499)

$hCMsg = _WinWaitAndGet("Create Messages", "")
ControlSend($hCMsg, "", "", $avCSVLine[2])
ControlSend($hCMsg, "", "", "{ENTER 2}")
Sleep("1000")
ControlSend($hCMsg, "", "", "{ENTER}")

$hCTkt = _WinWaitAndGet("Create IT Support Ticket", "")
ControlSend($hCTkt, "", "", "+{TAB 30}")
ControlSend($hCTkt, "", "", "{DEL}pc-work_sw{TAB 3}")
ControlSend($hCTkt, "", "", $avCSVLine[1])
ControlSend($hCTkt, "", "", "{TAB}")
ControlSend($hCTkt, "", "", $avCSVLine[3])
ControlSend($hCTkt, "", "", "{TAB 2}")
ControlSend($hCTkt, "", "", $avCSVLine[4])
ControlSend($hCTkt, "", "", "^s")
Sleep("200")
ControlSend($hCTkt, "", "", "{ENTER}")

$hMessage = _WinWaitAndGet("Please enter the message in English", "")
ControlSend($hMessage, "", "", "{ENTER}")
MouseClick("left")

$hProcOrg = _WinWaitAndGet("Processing Organization", "")
ControlSend($hProcOrg, "", "", "{TAB}{SPACE}")

$hFav = _WinWaitAndGet("Favorites", "")
Sleep("200")
ControlSend($hFav, "", "", "{TAB 4}{DOWN 2}{F2}")

$hProcOrg = _WinWaitAndGet("Processing Organization", "")
Sleep("300")
ControlSend($hProcOrg, "", "", "{TAB 2}")
ControlSend($hProcOrg, "", "", "{SPACE}")

Func _WinWaitAndGet($sTitle, $sText = "")
    WinWait($sTitle, $sText)
    Local $hWnd = WinGetHandle($sTitle, $sText)
    WinActivate($hWnd)
    WinWaitActive($hWnd)
    Return $hWnd
EndFunc

[herewasplato]

... so I would put that in a function: ...

I hate mentioning UDFs to most new users - the same is true for arrays (like I mentioned above). But sometimes the code just begs for it and you almost hate to post repetitive code that points them toward a bad coding practice... let alone making long code post well to the forum.

You might need to weave it all together in response to the OP's post #14... where to stick your code :-)

[i would try to put it all together, but you are holding the spoon - you get to decide how to feed the OP :-] Easy thread for me - I just post small stuff and let you do all the real work :-)