help with getting data from excel then replacing variable using the data

[starstuff]

Hello AutoIt community,

i would like to ask your help to my problem with getting the data from excel then replace a variable with the data.

I have an excel file in which the data resides, i would like to replace some variables based on the data i got from the excel file.

i would like to ask if there is someone willing to help me in private, i would give him/her the project i'm doing

Here's so far what i have

Func database()

$oExcel = _ExcelBookOpen("C:\sample.xls", 0, True)

Global $iLine = 2

While 1

Global $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)

If $aArray[0] = "" Then Exit ; Empty line detected - exit

$iLine += 1

;MsgBox(0,$aArray[0],$aArray[1] & @CRLF)

WEnd

EndFunc

TIA

[water]

Welcome to AutoIt and the forum!

We don't do coding for other users here. Usually it runs this way:

You start a project, run into a problem, ask on the forum and get help (most of the time), add the solution to your code, run into another problem, ask on the forum .... you get the picture.

The more experience you have in coding AutoIt this way the less you need to ask

[starstuff]

Hi water, thanks for the welcome!

i just don't know what to do next to solve my problem, i got stuck with trying to get the data in excel and doing something with the data. i'm not a programmer/developer but i appreciate how automation work.

i was able to do the half of the project, its working so far(not great but working), i'm asking for help in private because it has my login account and so that he/she can see what i'm really trying to do.

i'll just break down my problem here and ask for some guidance.

1, with the code above, i was able to see the data in the msgbox, --> OK

2, i would like to get data individually in a row --> OK

3. i would then pass the data to replace variables --> OK

4. then repeat on the next row --> PROBLEM

can you advice on what function i can use?

TIA

Edited December 12, 2012 by starstuff

[water]

What does "2, i would like to get data individually in a row" mean? You want to read all cells of a row and assign them to an array or a single variable?

[starstuff]

i have this fields on top / excel file

Category | Name |Price | Description

ROW1 Apple | iphone 5 | 890 | Fastest and thinnest smartphone

ROW2 Apple | iphone 4 | 700 | Great value for shoppers

ROW3 Nokia | 5000a | 600 | Dual-sim enabled smartphone

i meant on step two is to read all cells of a row and then assign each cell to replace a particular variable in the source code.

example

column Name - iphone 5 will replace $product_name

column Price - 890 will replace $product_price

column Description - Fastest and thinnest smartphone will replace $product_description

after that autoit will do its magic, automating the task at hand.

then repeat the process for ROW 2

TIA

Edited December 12, 2012 by starstuff

[water]

Why do you want to use individual variables? You could use the array elements $aArray[0], $aArray[1] and $aArray[2] in your script instead of $product_name, $product_price and $product_description.

If you need to enhance readability of your script assign the array elements to variables:

Global $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
$product_name  = $aArray[0]
$product_price = $aArray[1]
$product_description = $aArray[2

]

[starstuff]

thanks a lot for the help water! i was able to learn from your example, step 2 and 3 are OK now, i was successful in ROW1,

only step 4 left, how to repeat step 2,3, but get data in ROW 2

expandcollapse popup

Func post_it()
;new item variables to be change
Local $item_name = _IEGetObjById($oIE, "product")
Local $category = _IEGetObjById($oIE, "category")
Local $price = _IEGetObjById($oIE, "price")
Local $description = _IEGetObjById($oIE, "description")

;excel database
Local $oExcel = _ExcelBookOpen("C:sample.xls", 0, True)
Local $iLine = 2
Local $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
Local $product_name = $aArray[1]
Local $product_price = $aArray[2]
Local $product_description = $aArray[3]

;action starts here
_IEAction($item_name, "focus")
Send($product_name)
_IEAction($category, "focus")
Call("category_value")
_IEAction($price, "focus")
Send($product_price)
_IEAction($description, "focus")
Send($product_description)

;wait for the user if he wants to edit the post or go to the next item in excel
Dim $iMsgBoxAnswer
$iMsgBoxAnswer = MsgBox(1,"Post it or not?" ,"Are you sure you want to post it?")
Select
Case $iMsgBoxAnswer = 1 ;OK
Send("{TAB}") ; will go to the submit button
Send("{ENTER}") ; will click the submit button to post
_IENavigate($oIE, http://website.com/postnewitemhere.html) ; will go to the post new item page
;Go the the next row of the excel file? -->> how to do it?

Case $iMsgBoxAnswer = 2 ;Cancel
EndSelect
Sleep(1000)
_ExcelBookClose($oExcel) ; And finally we close out
EndFunc

Edited December 12, 2012 by starstuff

[water]

Something like this. Function Post_It contains all steps to process a single row.

expandcollapse popup

#include <Excel.au3>
#include <IE.au3>

Global $aArray
Global $oExcel = _ExcelBookOpen("C:sample.xls", 0, True)
Global $iLine = 2
While 1
    $iReturn = Post_It($iLine)
    If $iReturn = 0 Then Exit
    $iLine += 1
WEnd
_ExcelBookClose($oExcel) ; And finally we close out

Func Post_It($iLine)

    ;new item variables to be change
    Local $item_name = _IEGetObjById($oIE, "product")
    Local $category = _IEGetObjById($oIE, "category")
    Local $price = _IEGetObjById($oIE, "price")
    Local $description = _IEGetObjById($oIE, "description")

    ;excel database
    Local $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
    If $aArray[0] = "" Then Return 0 ; Empty line detected - exit
    Local $product_name = $aArray[1]
    Local $product_price = $aArray[2]
    Local $product_description = $aArray[3]

    ;action starts here
    _IEAction($item_name, "focus")
    Send($product_name)
    _IEAction($category, "focus")
    Call("category_value")
    _IEAction($price, "focus")
    Send($product_price)
    _IEAction($description, "focus")
    Send($product_description)

    ;wait for the user if he wants to edit the post or go to the next item in excel
    Dim $iMsgBoxAnswer
    $iMsgBoxAnswer = MsgBox(1, "Post it or not?", "Are you sure you want to post it?")
    Select
        Case $iMsgBoxAnswer = 1 ;OK
            Send("{TAB}") ; will go to the submit button
            Send("{ENTER}") ; will click the submit button to post
        Case $iMsgBoxAnswer = 2 ;Cancel
    EndSelect
    Return 1

EndFunc   ;==>Post_It

Edited December 12, 2012 by water

[starstuff]

Good day sir water,

I've tried the code above, it doesn't process the next row of the excel file, only the first row is being processed, i tested this in notepad, and it only writes the first row, when i click on the OK button of $iMsgBoxAnswer, it will again write the first row in the notepad.

i was thinking of a solution, and came up with using _ExcelRowDelete($oExcel, 1, 1) ;Delete Row 1 and only delete 1 Row, using this in notepad, it wrote the second row and third row.

;wait for the user if he wants to edit the post or go to the next item in excel
Dim $iMsgBoxAnswer
$iMsgBoxAnswer = MsgBox(1, "Post it or not?", "Are you sure you want to post it?")
Select
Case $iMsgBoxAnswer = 1 ;OK
Send("{TAB}") ; will go to the submit button
Send("{ENTER}") ; will click the submit button to post

;;added here the delete row

_ExcelRowDelete($oExcel, 1, 1)

Case $iMsgBoxAnswer = 2 ;Cancel
Exit
EndSelect
Return 1

do you know why it loops but it doesnt read the next row of the excel file?

TIA

[water]

This reproducer works quite well for me.

#include
#include

Global $aArray
Global $oExcel = _ExcelBookOpen(@ScriptDir & "sample.xlsx", 0, True)
Global $iLine = 2
While 1
    $iReturn = Post_It($iLine)
   If $iReturn = 0 Then ExitLoop
   $iLine += 1
WEnd
_ExcelBookClose($oExcel, 0) ; And finally we close out

Func Post_It($iLine)

   ;excel database
   Local $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
   If $aArray[0] = "" Then Return 0 ; Empty line detected - exit
   Local $product_name = $aArray[0]
   Local $product_price = $aArray[1]
   Local $product_description = $aArray[2]
   MsgBox(0, "", $product_name & "-" & $product_price & "-" & $product_description)
   Return 1

EndFunc ;==>Post_It

Edited December 13, 2012 by water

[starstuff]

Hello Sir Water,

apology for not getting back at you much earlier, i was busy at work .

thank you for the code above, i was able to repeat the process- step 4. im happy because i wrote a program that runs , but.....

i reviewed the codes, how it handles the data and how it performs in the actual website, its very clunky , again, i would like to ask for some advice to make this not too clunky.

1.) the major flaw i see, is that the send command types the text from excel to the website. the "Description" of the item is problematic because autoit doesn't send the CRLF properly and if the description is long, it will take a while for autoit to type it in.

i was thinking of using clipget() and clipput()

do you have any advice?

TIA

[water]

I would try to use one of the _IE* functions to post data.

As you didn't post the HTML source of your website I can only guess that you want to insert data into a form. So function _IEFormElementSetValue should do what you need.

[water]

Or give this code a try:

Local $item_name = _IEGetObjById($oIE, "product")
$item_name.value = "Here goes your value"

[starstuff]

Hey water,

i was thinking of using _IEForm* before but the source has no form? not sure though, please see source txt file.

i've been using the _IEGetObjById and _IEGetObjByName

Local $item_name = _IEGetObjByName($oIE, "name")
Local $category = _IEGetObjByName($oIE, "cat")
Local $price = _IEGetObjByName($oIE, "pricebudget")
Local $description = _IEGetObjById($oIE, "caption")

;excel database
Local $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
If $aArray[0] = "" Then Return 0 ; Empty line detected - exit
Local $product_name = $aArray[1]
Local $product_price = $aArray[2]
Local $product_description = $aArray[3]

;action starts here
_IEAction($item_name, "focus")
Send($product_name)
_IEAction($category, "focus")
Call("category_value")
_IEAction($price, "focus")
Send($product_price)
_IEAction($description, "focus")
Send($product_description)

the clunky part is in the last part the variable $description, the descriptions are long,

with the SEND command, autoit types the data from excel, instead i would like to copy the data from the excel file and just paste the data.

thats how i do it manually and it retains all the formatting of the text, and i think it would be faster.

Found this vista.tutorialref.com/autoit/autoit-copy-paste.html

Func printOutput2()
Local $fTest
$fTest = ClipPut($outputArrayRS[0][2]) ;get value of table's field by index number
Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
Send("^v")
EndFunc

this might work. i will check it out.

TIA

Edited December 22, 2012 by starstuff

[water]

How about:

Local $item_name = _IEGetObjByName($oIE, "name")
Local $category = _IEGetObjByName($oIE, "cat")
Local $price = _IEGetObjByName($oIE, "pricebudget")
Local $description = _IEGetObjById($oIE, "caption")

;excel database
Local $aArray = _ExcelReadArray($oExcel, $iLine, 1, 4, 0)
If $aArray[0] = "" Then Return 0 ; Empty line detected - exit
Local $product_name = $aArray[1]
Local $product_price = $aArray[2]
Local $product_description = $aArray[3]

;action starts here
$item_name.value = $product_name
$price.value = $product_pric
$description.value = $product_description

[starstuff]

hello water,

sorry i dont quite understand the *.value, i search on index,its not on the manual of autoit,

$item_name.value = $product_name
$price.value = $product_price
$description.value = $product_description

MsgBox(0,"What is this?",$item_name.value)
MsgBox(0,"What is this?",$price.value)
MsgBox(0,"What is this?",$description.value)

what does it do? i run it in the msgbox and it says 0.

[water]

_IEGetObjByName returns an object variable. Objects have properties and methods.

You simply set a property of the object.

This can't be found in the AutoIt help file, MSDN gives you this information - search for IE COM.