Using copy and paste to extract database data

[youknowwho4eva]

I'm robbing our database of all its data, but each part of the database I go to I have to tweak things like sleeps so that Autoit doesn't error. It has something to do with Autoit taking whats in the copy and putting it in the ini. It usually only does it with larger items. So usually I add time till I stop getting the Error. Is there a way to detect when the clipboard is ready for the next operation? Here is one of my codes

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <ButtonConstants.au3>
#Include <GuiListView.au3>
#include <ClipBoard.au3>
#include <Array.au3>


Run("c:\lotus\approach\approach.exe")
WinWaitActive("Welcome to Lotus Approach")
Send("!b")
WinWaitActive("Open")
Send("{tab}")
Sleep(3000)
Send("d")
Sleep(3000)
send("+{tab}")
Sleep(3000)
Send("S:\win95\work\matls.dbf")
Send("{enter}")
Sleep(3000)
WinWaitActive("Approach")
Sleep(3000)
Send("^{pgdn}")
Sleep(3000)
Send("{tab}")
Sleep(3000)
For $n = 1 to 3991 Step +1
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$model = _ClipBoard_GetData()
If IniRead(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Length","nope") <> "nope" Then
    $model = $model & $n
ElseIf $model = $item Then
    Send("{tab 10}")
    Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$model = _ClipBoard_GetData()
If IniRead(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Length","nope") <> "nope" Then
    $model = $model & $n
    EndIf
EndIf

WinWaitActive("Approach")
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Description",$item)
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
If $item <> $model Then
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Length",$item)
EndIf
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
If $item <> $model Then
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Width",$item)
EndIf
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
If $item <> iniread(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Width","") and $item <> $model then
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Height",$item)
EndIf
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
If $item <> iniread(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Height","") And $item <> iniread(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Width","") and $item <> $model Then
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Printed",$item)
EndIf
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
If $item <> iniread(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Height","") And $item <> iniread(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Width","") And $item <>IniRead(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Printed",$item) and $item <> $model Then
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Remarks",$item)
EndIf
Sleep(3000)
Send("{tab}")
Sleep(3000)
_ClipBoard_Empty()
Sleep(3000)
Send("!e")
Sleep(3000)
Send("c")
Sleep(3000)
$item = _ClipBoard_GetData()
Sleep(3000)
IniWrite(@desktopdir & "\database copy\cartons\productinfo.ini",$model,"Part Number",$item)
Sleep(3000)
Send("{tab 2}")
Next

Send("!f")
Send("x")
Sleep(3000)
Send("n")

I realize with this particular script I don't open the clip board to clear it and then close the clip board, but I have since fixed this. I know it should be ctrl c but for some reason in the database, it doesnt accept ctrl c. (Now you know why I hate it and am ripping its guts out) And there are a few other things I have cleaned up but I believe the issue lies at $item = _ClipBoard_GetData(), I have tried putting a msgbox(0,"",_ClipBoard_GetData()) infront of it, and it errors before the message box comes up.

[PsaltyDS]

Lotus Approach is an IBM relational database. Surely there is an ODBC or ADODB connection available so you can do this more easily with a COM interface?

[youknowwho4eva]

I don't know, I don't have any experience with COM interfaces. The version we have is old(not only that but there have been several version updates, I think its on 97 now), it's been over stuffed with data. Everything that should be simple with it so far I've had to code ways around road block after road block. This method has worked for I think 4 or 5 of the databases now, but now I'm on a very large database. I have the sleeps set to 8 seconds and it still Kills AutoIt. It's one of those things where it was set up years ago by someone, and for several years now people with no knowledge of the program have been playing with it. Now I get here, and I'm just trying to clean up the data so it's useful again.

[DaRam]

I Would second the COM approach. Never heard of using the ClipBoard to extract data , but whatever it takes right?

Presuming you have Visual FoxPro ODBC Driver's installed, Set your ADO connection String to something like

"Provider=VFPOLEDB.1;Data Source=S:\win95\work\;"

And in your SQL string refer to the DBF as a Table (without the DBF extension):

"SELECT * FROM matls"

[PsaltyDS]

I don't know, I don't have any experience with COM interfaces. The version we have is old(not only that but there have been several version updates, I think its on 97 now), it's been over stuffed with data. Everything that should be simple with it so far I've had to code ways around road block after road block. This method has worked for I think 4 or 5 of the databases now, but now I'm on a very large database. I have the sleeps set to 8 seconds and it still Kills AutoIt. It's one of those things where it was set up years ago by someone, and for several years now people with no knowledge of the program have been playing with it. Now I get here, and I'm just trying to clean up the data so it's useful again.

Go to one of the places you have Lotus Approach installed, run ODBCAD32.exe, and check the the Drivers tab. I'll bet there's an ODBC driver for it listed there.

Your life will be so much easier where you can just connect to the DB and run queries. Well worth the time to learn how!

[youknowwho4eva]

I think my brain just melted. Don't know anything about any of what you said. If I could find a solution to Autoit crashing when the clipboard data is read I'd be more then happy. I only have a few more databases to copy till I can finish up my Master Program to organize all the confused data into one easy to use interface.

Edit: @PSalty, I just did a search in the lotus directory and no results for odbcad32.exe

Edit again: @PSalty, now I see what you mean, ran it went to drivers. none listed for lotus approach.

Edited September 17, 2008 by youknowwho4eva

[PsaltyDS]

I think my brain just melted. Don't know anything about any of what you said. If I could find a solution to Autoit crashing when the clipboard data is read I'd be more then happy. I only have a few more databases to copy till I can finish up my Master Program to organize all the confused data into one easy to use interface.

Look in the help file at _ClipBoard_GetSequenceNumber(). Perhaps you could wait in a loop for a change in the sequence number vice Sleep()?

Edit: @PSalty, I just did a search in the lotus directory and no results for odbcad32.exe

Edit again: @PSalty, now I see what you mean, ran it went to drivers. none listed for lotus approach.

If you choose to pursue that, don't give up. Lotus Approach may not appear by name, but it may just use an existing generic driver that is listed.

>_<

[youknowwho4eva]

While searching through that program something helpful did pop-up that I'm ashamed I didn't think of before. I opened the file in excel, moved the sleeps from 8 seconds to 500ms and there she goes. Like I said previously, they had put way to much in there. Excel cut out the visio images that I was skipping over anyway and is working like a charm. I will look up all that com stuff later though. When I don't have a massive project that I'm trying to get out there and usable soon.

Edit: Typo

Edited September 17, 2008 by youknowwho4eva

[PsaltyDS]

While searching through that program something helpful did pop-up that I'm ashamed I didn't think of before. I opened the file in excel, moved the sleeps from 8 seconds to 500ms and there she goes. Like I said previously, they had put way to much in there. Excel cut out the visio images that I was skipping over anyway and is working like a charm. I will look up all that com stuff later though. When I don't have a massive project that I'm trying to get out there and usable soon.

Edit: Typo

Sounds like you may not need this anymore, but I tested the idea of using _ClipBoard_GetSequenceNumber(), since I had never used it before, and this seems pretty slick.

Start the script in SciTE (ESC will quit) and every time something goes to the clipboard it will be copied to the console:

#include <Clipboard.au3>

HotKeySet("{ESC}", "_Quit")
$iSeq = _ClipBoard_GetSequenceNumber()
ConsoleWrite("Starting seq no. = " & $iSeq & @LF)

While 1
    $iTest = _ClipBoard_GetSequenceNumber()
    If $iTest <> $iSeq Then
        $iSeq = $iTest
        ConsoleWrite("New seq no. = " & $iSeq & @LF)
        $sData = _ClipBoard_GetData()
        ConsoleWrite("Data:  " & $sData & @LF)
    EndIf
    Sleep(250)
WEnd

Func _Quit()
    Exit
EndFunc

If the new game is Excel, then you've got the Excel.au3 UDF and functions in the current Beta for reading cells(i.e. _ExcelReadCell() and _ExcelSheetReadToArray()) without simulated keystrokes too.

[youknowwho4eva]

Well for my next database I'll probably move over to the excel udf, but seeing I have this one already setup I think I'll add that getsequencenumber in there and see what I get. *crosses fingers* Cause I left on over night (as I said a lot of data the one ran for about a week and a half, with a few interruptions in between) I came in and it is still crashing in the same spot. It's a really long line. Thank you for all your help. I'm trying to learn it all, but for this one (if it works out) your solution is exactly what I was looking for.

[youknowwho4eva]

Same deal, so I'm going to have to go to the excel udf.