Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

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


  • Please log in to reply
16 replies to this topic

#1 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 12 December 2012 - 08:59 AM

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







#2 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 12 December 2012 - 09:14 AM

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 :graduated:

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#3 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 12 December 2012 - 10:55 AM

Hi water, thanks for the welcome! :bye:

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 by starstuff, 12 December 2012 - 03:14 PM.


#4 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 12 December 2012 - 11:28 AM

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?

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#5 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 12 December 2012 - 12:11 PM

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 by starstuff, 12 December 2012 - 12:12 PM.


#6 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 12 December 2012 - 01:09 PM

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
]

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#7 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 12 December 2012 - 03:16 PM

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

Plain Text         
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, <a href='http://website.com/postnewitemhere.html' class='bbc_url' title='External link' rel='nofollow external'>http://website.com/postnewitemhere.html</a>) ; 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 by starstuff, 12 December 2012 - 03:21 PM.


#8 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 12 December 2012 - 03:28 PM

Something like this. Function Post_It contains all steps to process a single row.
AutoIt         
#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 by water, 12 December 2012 - 03:29 PM.

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#9 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 13 December 2012 - 01:10 PM

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

#10 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 13 December 2012 - 01:23 PM

This reproducer works quite well for me.
AutoIt         
#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 by water, 13 December 2012 - 01:24 PM.

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#11 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 21 December 2012 - 01:45 AM

Hello Sir Water,

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

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

i reviewed the codes, how it handles the data and how it performs in the actual website, its very clunky :sweating:, 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

#12 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 21 December 2012 - 08:47 AM

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.

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#13 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 21 December 2012 - 09:51 AM

Or give this code a try:
Local $item_name = _IEGetObjById($oIE, "product") $item_name.value = "Here goes your value"

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#14 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 21 December 2012 - 12:46 PM

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 by starstuff, 22 December 2012 - 01:18 AM.


#15 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 21 December 2012 - 01:19 PM

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

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts


#16 starstuff

starstuff

    Seeker

  • Active Members
  • 10 posts

Posted 22 December 2012 - 01:25 AM

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.

#17 water

water

    ?

  • MVPs
  • 14,584 posts

Posted 22 December 2012 - 08:31 AM

_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.

UDFs:
Active Directory (NEW: 2014-02-17 - Version 1.4.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2013-11-10 - Version 0.10.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel (NEW: 2014-03-22 - Beta 5) - Download - General Help & Support - Example Scripts





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users