AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Pasting succesive line fileds from csv

 
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help
View previous topic :: View next topic  
Author Message
ScottEdge



Joined: 14 Aug 2005
Posts: 57
Location: Connecticut,USA
PostPosted: Sun Aug 14, 2005 1:09 am    Post subject: Pasting succesive line fileds from csv Reply with quote
I'm a complete newbie with no programming experience but find AHK pretty amazing.

I've spent all day trying to figure out how to load successive line fields from a csv to the clipboard. From the manual and examples/posts on the forum I have figured out how to specify the specific filed in a column from the following script. However I can't figure out how to go to the next line without obviously manually changing the script in the "%Order2%" Field. At this point I'm not even sure of the right way to do it.


Code:
  Clipboard =
      ArrayCount = 0
      Loop, Read, C:\RECORDS\Customer.csv
      {
        ArrayCount += 1 
        StringSplit, Item, A_LoopReadLine, `,
        Status%ArrayCount% = %Item1%
        Order%ArrayCount% = %Item2%
      }
      Clipboard = %Order2%


Any help would be greatly appreciated.

::What I'm trying to accomplish::
I'm trying to paste order numbers that are saved in the second column of a csv file into internet explorer and then copy information into a csv file once complete repeat for however many line numbers there are in the csv file. Right now I'm stuck on loading the clipboard.


Scott
Back to top
View user's profile Send private message
tpatel5



Joined: 27 Oct 2004
Posts: 65
Location: GA
PostPosted: Sun Aug 14, 2005 1:58 am    Post subject: Reply with quote
I am not sure what you are trying to do here exactly. Do you have more than one order numbers per line?

can you please describe your sitiuation in detail or give us an example.
_________________
-Tru Cool
Back to top
View user's profile Send private message
ScottEdge



Joined: 14 Aug 2005
Posts: 57
Location: Connecticut,USA
PostPosted: Sun Aug 14, 2005 7:41 am    Post subject: Reply with quote
I have a csv file that has customer and order data information that looks like this:

"Status","Order","Name","Date","Total",
"<img src="/images/empty.gif">","89093068","***","8/13/2005 10:20:11 PM","$125.00",
"<img src="/images/empty.gif">","89088278","***","8/13/2005 9:11:13 PM","$64.95",
"<img src="/images/empty.gif">","89087785","***","8/13/2005 9:04:07 PM","$69.95",
"<img src="/images/empty.gif">","89082983","***","8/13/2005 7:54:58 PM","$64.99",
"<img src="/images/empty.gif">","89079781","***","8/13/2005 7:08:51 PM","$65.00",
"<img src="/images/empty.gif">","89078991","***","8/13/2005 6:57:29 PM","$139.19",
"<img src="/images/empty.gif">","89078370","***","8/13/2005 6:48:32 PM","$41.95"

The number of Records will change each time but the columns will always be 5. For each Order Number in my csv file I want to run the same script. So the first line in my csv file I want to paste 89093068 into a designated tab screen on a webpage and run a script that will take customer information and send it to a text file. Then repeat for 89088278 and so on until it has collected the designated information for each Order #.

Our webdeveloper can't export customer information from their system at least not at the moment without charging $600 to create a new report. Although the time I've spent trying to figure out how to do all this probably cost me more. Not as fun though.

Once the customer billing and shipping information for each order number is saved into a csv file we can import that into our Point of Sale software in house. Right now we are manually coping and pasting the information from our web sales to our POS system and this is becoming a tedious task.

Currently we can export a report with an order number from our site into a csv file(example above). Taking the order number from this csv and pasting it into a form field on our site is the only way I can have the proper links consistently show up in the same place for mouse clicks.

Here is the code I would like to Loop for the number of record lines in the csv file.



Code:

WinWait, Order List - Maxthon Browser,
IfWinNotActive, Order List - Maxthon Browser, , WinActivate, Order List - Maxthon Browser,
WinWaitActive, Order List - Maxthon Browser,
Sleep, 100
MouseClick, left,  65,  505
Sleep, 100

;Want to Loop from here to the end of the script for all Orders in Customer.csv seperated by Linenumbers
Clipboard =
      ArrayCount = 0
      Loop, Read, C:\Records\Customer.csv
      {
        ArrayCount += 1 
        StringSplit, Item, A_LoopReadLine, `"
        Status%ArrayCount% = %Item1%
        Order%ArrayCount% = %Item6%
      }
      Clipboard = %Order2%
Sleep, 100
Send, {CTRLDOWN}v{CTRLUP}
MouseClick, left,  153,  507
Sleep, 100
MouseClick, left,  160,  509
Sleep, 100
WinWait, Requests - Maxthon Browser,
IfWinNotActive, Requests - Maxthon Browser, , WinActivate, Requests - Maxthon Browser,
WinWaitActive, Requests - Maxthon Browser,
MouseClick, left,  380,  316
Sleep, 100

;Copy Customer Billing/Shipping  (Add If/Else for Address 2 exception)


FileDelete, C:\Records\CustomersRP.txt
Sleep, 300
FileAppend, ShipFirst ShipLast;Ship Street;ShipCity ShipZip ShipState; Ship Country; Phone #; BillFirst BillLast; Bill Street; BillCity BillState BillZip; Bill Country; E-mail`n, C:\Records\CustomersRP.txt
WinWait, Order Detail - Maxthon Browser,
IfWinNotActive, Order Detail - Maxthon Browser, , WinActivate, Order Detail - Maxthon Browser,
WinWaitActive, Order Detail - Maxthon Browser,
Clipboard =
sleep, 100



Last edited by ScottEdge on Sun Aug 14, 2005 8:03 pm; edited 1 time in total
Back to top
View user's profile Send private message
ScottEdge



Joined: 14 Aug 2005
Posts: 57
Location: Connecticut,USA
PostPosted: Sun Aug 14, 2005 7:42 am    Post subject: Reply with quote
Here is the code I've written so far for the whole thing.

Code:
^#e::
setkeydelay, 0
IfWinExist, Order List - Maxthon Browser
{
   WinWait, Order List - Maxthon Browser,
   IfWinNotActive, Order List - Maxthon Browser, , WinActivate, Order List - Maxthon Browser,
   WinWaitActive, Order List - Maxthon Browser,   
   sleep, 100   
   WinMaximize
   Goto Label1
}
else
{
   Run,https://******.************.com/login.asp     
   WinWait, ****** ****** Store ****** Login - Maxthon Browser, ****** ****** Store
   IfWinNotActive, ****** ****** Store ****** Login - Maxthon Browser, ****** ****** Store , WinActivate, ****** ****** Store ****** Login - Maxthon Browser, ****** ****** Store
   WinWaitActive, ****** ****** Store ****** Login - Maxthon Browser, ****** ****** Store
   WinMaximize
   Sleep, 1000
   Send, ****{TAB}
   Sleep, 100
   Send, ***{ENTER}
   Sleep 5000
   Goto Label1
}


;Load Order List

Label1:
WinWait, ****** ****** Store ****** - Maxthon Browser,
IfWinNotActive, ****** ****** Store ****** - Maxthon Browser, , WinActivate, ****** ****** Store ****** - Maxthon Browser,
WinWaitActive, ****** ****** Store ****** - Maxthon Browser,
Sleep, 100

Send, {CTRLDOWN}l{CTRLUP}
Sleep, 100
Send,https://******.************.com/orders/orderlist.aspx?type=0&dir=3{ENTER}

;Delete/Create Customer CSV

FileDelete, C:\Records\Customer.csv

WinWait, Order List - Maxthon Browser,
IfWinNotActive, Order List - Maxthon Browser, , WinActivate, Order List - Maxthon Browser,
WinWaitActive, Order List - Maxthon Browser,

MouseClick, left,  788,  237
Sleep, 100

WinWait, File Download,
IfWinNotActive, File Download, , WinActivate, File Download,
WinWaitActive, File Download,
Sleep, 200
Send, s

WinWait, Save As,
IfWinNotActive, Save As, , WinActivate, Save As,
WinWaitActive, Save As,

Send, {SHIFTDOWN}c{SHIFTUP}{SHIFTDOWN}:{SHIFTUP}\{SHIFTDOWN}R{SHIFTUP}ecords\{SHIFTDOWN}c{SHIFTUP}ustomer{ENTER}{ENTER}


;Load Customer Orders

WinWait, Order List - Maxthon Browser,
IfWinNotActive, Order List - Maxthon Browser, , WinActivate, Order List - Maxthon Browser,
WinWaitActive, Order List - Maxthon Browser,
Sleep, 100
MouseClick, left,  65,  505
Sleep, 100

;Want to Loop from here to the end of the script for all Orders in Customer.csv seperated by Linenumbers
Clipboard =
      ArrayCount = 0
      Loop, Read, C:\Records\Customer.csv
      {
        ArrayCount += 1 
        StringSplit, Item, A_LoopReadLine, `"
        Status%ArrayCount% = %Item1%
        Order%ArrayCount% = %Item6%
      }
      Clipboard = %Order2%



Last edited by ScottEdge on Sun Aug 14, 2005 8:03 pm; edited 1 time in total
Back to top
View user's profile Send private message
Guest
PostPosted: Sun Aug 14, 2005 12:00 pm    Post subject: Reply with quote
Tip: do a search for JavaScript on this forum, you can beautify and speed up the insertion in the form fields with it... Cool
Back to top
BoBo
Guest
PostPosted: Sun Aug 14, 2005 1:35 pm    Post subject: Reply with quote
I'd prefer to do the "parsing" directly with the source(s) and to prevent to get the data using mouse&cut&paste.

Download webpages (URLDownloadToFile) instead of pulling data from their frontends. Parse (Loop, Parse) the downloaded webpages to extract data of interest.

Try to input/import/push data to the requested webserver via the browsers command line aka address line. Eg. to read this thread you're requesting page number http://www.autohotkey.com./forum/viewtopic.php?t=4930 from the AHK web server. Check if your webserver is getting its request using the GET/POST method.

But maybe I've lost you Rolling Eyes
Back to top
tpatel5



Joined: 27 Oct 2004
Posts: 65
Location: GA
PostPosted: Sun Aug 14, 2005 3:40 pm    Post subject: Reply with quote
Since you wanna loop for this part only put that inside your original loop like tihs:

Code:

;Want to Loop from here to the end of the script for all Orders in Customer.csv seperated by Linenumbers
Clipboard =
ArrayCount = 0
Loop, Read, C:\Records\Customer.csv
  {
    ArrayCount += 1
    StringSplit, Item, A_LoopReadLine, `"
    Status%ArrayCount% = %Item1%
    Order%ArrayCount% = %Item6%
    Clipboard = %Order2%
    Sleep, 100
    Send, {CtrlDown}v{CtrlUp}
    MouseClick, Left,  153,  507
    Sleep, 100
    MouseClick, Left,  160,  509
    Sleep, 100
    WinWait, Requests - Maxthon Browser,
    IfWinNotActive, Requests - Maxthon Browser, , WinActivate, Requests - Maxthon Browser,
        WinWaitActive, Requests - Maxthon Browser,
    MouseClick, Left,  380,  316
    Sleep, 100

    ;Copy Customer Billing/Shipping  (Add If/Else for Address 2 exception)


    FileDelete, C:\Records\CustomersRP.txt
    Sleep, 300
    FileAppend, ShipFirst ShipLast;Ship Street;ShipCity ShipZip ShipState; Ship Country; Phone #; BillFirst BillLast; Bill Street; BillCity BillState BillZip; Bill Country; E-mail`n, C:\Records\CustomersRP.txt
    WinWait, Order Detail - Maxthon Browser,
    IfWinNotActive, Order Detail - Maxthon Browser, , WinActivate, Order Detail - Maxthon Browser,
        WinWaitActive, Order Detail - Maxthon Browser,
    Clipboard =
    Sleep, 100
    MouseClickDrag, Left,  754,  244, 536,  244, 0 ;Ship to Name
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  772,  260, 534,  260, 0  ;Ship to Street Address
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  772,  275, 535,  275, 0 ;Ship to City, Zip, State
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  772,  294, 536,  294, 0 ;Ship to Country
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  772,  313, 576,  313, 0 ;Phone #
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  518,  243, 283,  244, 0 ;Bill to First, Last Name
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  522,  258, 282,  258, 0 ;Bill to Street
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  522,  275, 283,  275, 0 ;Bill to City, ZIp, State
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  398,  290, 282,  290, 0 ;Bill to COuntry
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%;, C:\Records\CustomersRP.txt
    Clipboard =
    MouseClickDrag, Left,  467,  550, 291,  550, 0 ;E-mail address
    Sleep, 100
    Send, {CtrlDown}c{CtrlUp}
    Sleep, 100
    FileAppend, %Clipboard%`n, C:\Records\CustomersRP.txt
    Clipboard =
    ArrayCount = 0
  }



This way it will read a line from csv file and does all these step for it then reads the next line, and so on until it reaches the end of csv file. since order # are always in the same column you don't even have to change the numbers manually as arraycount will reset itself after going through each line of csv file, just like clipboard.

But as BoBo said it is better to use UrlDownloadToFile than cut\copy & paste method.
_________________
-Tru Cool
Back to top
View user's profile Send private message
ScottEdge



Joined: 14 Aug 2005
Posts: 57
Location: Connecticut,USA
PostPosted: Sun Aug 14, 2005 7:16 pm    Post subject: Reply with quote
Thank you for your help!
Back to top
View user's profile Send private message
ScottEdge



Joined: 14 Aug 2005
Posts: 57
Location: Connecticut,USA
PostPosted: Sun Aug 14, 2005 7:25 pm    Post subject: Reply with quote
BoBo

are you talking about pasting the order# into the url to download directlyusing (URLDownloadToFile)?


Unfortunetly the webpage is not pulled up by the order#.

So for Order# 87038773

the webpage is
https://*****.******.com/Orders/OrderDetail.aspx?orderid=5KAHJ01BXCG29N1ANTH22N4WU3A

I assume this would not be the get/post method. Thanks for the suggestions.

I think I will save to a text file and try to parse the information that way. I've come up with some errors using the mouseclick drag function. Now I just have to figure out how to parse the saved text properly. Just when I think I'm out of the woods.

scott
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    AutoHotkey Community Forum Index -> Ask for Help All times are GMT
Page 1 of 1

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group