Jump to content

Help Combining Two Scripts

Alupis

By Alupis,
in AutoIt General Help and Support

 Share

Recommended Posts

Alupis Seeker

Alupis

Posted
Posted (edited)

I have a working script that i'm trying to edit to account for some data discrepancies in a CSV file i process. I have been changing the CSV to a TXT file, but it might be easier to just work with the CSV. but the problem is sometimes there are extra Quoted ("") or commas (,) depending on however our customer filled out their form.

So to account for this i think it would be easy if my script could read the unaltered CSV file, change the quotes to something else like a | and then split the data into lines so the rest of my script can read it properly.

This is my full script (i know its dirty, trying to work the kinks out before cleaning it up, but if you have suggestions let me know!)

#Include <File.au3>
#Include <Array.au3>
#include <Debug.au3>

_debugSetup ( "Debug!")

Global $NewOrderFile, $onum = "", $line = "", $arrTemp = 0
$oshipmethod = 0
$NewOrders = FileOpen("NEW_ORDERS.TXT", 0)
$cctype = 0
$visaNum = "4111111111111111"
$amexNum = "378282246310005"
$discNum = "6011111111111117"
$masterNum = "5555555555554444"

Func setupasw()
WinActivate("Session A - [24 x 80]", "")
    Send("go asw")
    Send("{ENTER}")
    Send("go sales1")
    Send("{ENTER}")
    Send("1")
    Send("{ENTER}")
    Send("JDB")
    Send("{TAB}")
    Send("{TAB}")
    Send("JDB")
    Send("{TAB}")
    Send("{TAB}")
    Send("JB")
    Send("{ENTER}")
; End ASW Setup Section
EndFunc

Func splitnow()

If _FileReadToArray("NEW_ORDERS.TXT", $NewOrderFile) == 0 Then
    MsgBox(0,"error", "Error reading in file.")
    Exit
EndIf


For $curLine = 1 To $NewOrderfile[0]

    ;clean and split the current line
    $line = StringSplit(StringStripWS($NewOrderFile[$curLine],7), ",")

    If $onum <> $line[2] Then
        ;the order number is different
        $onum = $line[2]

        ;send the array for the last order number on for processing
        ;added isArray check to prevent sending on the first loop
        If IsArray($arrTemp) Then
            processOrders($arrTemp)
        EndIf

        ;start a new temp array for the new order number
        Dim $arrTemp[1] = [$NewOrderFile[$curLine]]
    Else
        ;the order number is the same, add the line to the temp array for the current order number
        _ArrayAdd($arrTemp,$NewOrderFile[$curLine])
    EndIf

Next

;send along the last order number after the FOR loop hits the last line in the file
If UBound($arrTemp) Then
    processOrders($arrTemp)
EndIf
EndFunc

Func processOrders($arrOrders)

    ;work with array $arrOrders here, all elements will be lines with the same order number
    ; ( $arrOrders is array of plain text lines, each element is an entire line from the file )
    _ArrayDisplay($arrOrders)


    For $order In $arrOrders

            $order = StringSplit(StringStripWS($order,7), ",")

            WinActivate("Session A - [24 x 80]", "")

            ;WinActivate("Untitled - Notepad")

                Send($order[16])  ; item number
                Send("{NUMPADADD}")
                Send($order[18])  ; item qty
                Send("{NUMPADADD}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send($order[19])  ; per item price
                Send("{NUMPADADD}")
                Send("{ENTER}")
            Next
            ;WinWait("Array: ListView Display")
            dship($order)
EndFunc

Func dship(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    ;UPS Variables


    If StringInStr($order[12], "UPS - Ground") Then  ; array[12] is for shipping method type
        $oshipmethod = 300
    EndIf
    If StringInStr($order[12], "Ground") Then
        $oshipmethod = 300
    EndIf
    If StringInStr($order[12], "UPS - 2nd Day Air") Then
        $oshipmethod = 210
    EndIf
    If StringInStr($order[12], "UPS - 3 Day Select®") Then
        $oshipmethod = 220
    EndIf
        ;USPS Variables
    If StringInStr($order[12], "USPS - Priority Mail") Then
        $oshipmethod = 910
    EndIf
    If StringInStr($order[12], "Priority Mail") Then
        $oshipmethod = 910
    EndIf
    f9($order)
EndFunc

Func f9(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    WinActivate("Session A - [24 x 80]", "")
    Send("{F9}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send($order[1])  ; Invoice-Prefix
    Send($order[2])  ; Invoice Num
    Send("{NUMPADADD}")
    Send("{ENTER}")
        If $oshipmethod=300 Then
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send($order[11])  ; shipping cost
            Send("{NUMPADADD}")
            Send("{ENTER}")
            Send("{ENTER}")
        Else
            Send("{TAB}")
            Send("{TAB}")
            Send($oshipmethod)
            Send("{ENTER}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send($order[11])  ; shipping cost
            Send("{NUMPADADD}")
            Send("{ENTER}")
            Send("{ENTER}")
        EndIf
    f8($order)
EndFunc

Func f8(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")
    Send("{F8}")
    Send("{TAB}")
    Send("1")
    Send("{ENTER}")
    Send("{TAB}")
    Send("999")
    Send("{NUMPADADD}")
    Send($order[3])  ; first name
    Send("{SPACE}")
    Send($order[4])  ; last name
    Send("{NUMPADADD}")
    Send($order[6])  ; shipping address Line 1
    Send("{NUMPADADD}")
    Send($order[7])  ; shipping address Line 2
    Send("{NUMPADADD}")
    Send($order[5])  ; Company name
    Send("{NUMPADADD}")
    Send($order[8])  ; City
    Send("{NUMPADADD}")
    Send($order[10])  ; State
    Send("{NUMPADADD}")
    Send($order[9])  ; Zip
    Send("{NUMPADADD}")
    Send("{ENTER}")
    WinWait("Session A - [24 x 80]", "")
    Send("{ENTER}")
    WinWait("Session A - [24 x 80]", "")
    Send("{F6}")
    Send("{ENTER}")
    WinWait("Session A - [24 x 80]", "")
    Send("{F12}")
dpay($order)
EndFunc

Func dpay(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    ;WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")


    If StringInStr($order[14], "5") Then   ; array[14] is paymethodtype
        gcheckout($order)
    EndIf

    If StringInStr($order[13], "Visa") and StringInStr($order[14], "2") Then  ; array[13] is cardtype if present
        $cctype = $visaNum
        ccheckout($order)
    EndIf
    If StringInStr($order[13], "American Express") and StringInStr($order[14], "2") Then
        $cctype = $amexNum
        ccheckout($order)
    EndIf
    If StringInStr($order[13], "Discover") and StringInStr($order[14], "2") Then
        $cctype = $discNum
        ccheckout($order)
    EndIf
    If StringInStr($order[13], "Mastercard") and StringInStr($order[14], "2") Then
        $cctype = $masterNum
        ccheckout($order)
    EndIf

    If StringInStr($order[14], "4") Then ;and StringInStr($array[14], "") Then
        pcheckout($order)
    EndIf
EndFunc

Func cataxfix($order)


;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    ;WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")

    If StringInStr($order[10], "CA") Then
        Do
        $continue = MsgBox(0, "CA TAX FIX", "CA Tax Detected, Verify Rounding and Click 'OK'.")
        Until $continue = 1
    EndIf
EndFunc

Func gcheckout(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")

    Send("{F7}")
    Send($order[15])  ; array[15] is order total $ amount
    Send("{NUMPADADD}")
    Send($order[2])  ; order[2] is order number stripped of prefix
    Send("{NUMPADADD}")
    Send("2")
    cataxfix($order)
    Send("{ENTER}")
    Send("{F12}")
    Send("+{TAB}")
    Send("+{TAB}")
    Send("22")
    Send("{ENTER}")
    Send(".")
    Send("{ENTER}")
    Send("Google Checkout")
    Send("{ENTER}")
    Send("{F12}")
    Send("{F12}")
    Send("{ENTER}")
EndFunc

Func ccheckout(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")

    Send("{F7}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send($order[15])  ; array[15] is order total $ amount
    Send("{NUMPADADD}")
    Send($cctype)
    Send("{NUMPADADD}")
    Send("1213")
    Send("{NUMPADADD}")
    Send("{TAB}")
    Send("1")
    cataxfix($order)
    Send("{ENTER}")
    Send("{F12}")
    Send("{F12}")
    Send("{ENTER}")
EndFunc

Func pcheckout(ByRef $order)
;   $line = FileReadLine($NewOrders)
;    If @error = -1 Then Exit
;   $clean = StringStripWS($line,"")
;   $array = StringSplit($clean, ",")
    WinActivate("Session A - [24 x 80]", "")
    ;WinActivate("Untitled - Notepad")

    Send("{F7}")
    Send($order[15])  ; array[15] is order total $ amount
    Send("{NUMPADADD}")
    Send($order[2])
    Send("{NUMPADADD}")
    Send("2")
    cataxfix($order)
    Send("{ENTER}")
    Send("{F12}")
    Send("+{TAB}")
    Send("+{TAB}")
    Send("22")
    Send("{ENTER}")
    Send(".")
    Send("{ENTER}")
    Send("PayPal")
    Send("{ENTER}")
    Send("{F12}")
    Send("{F12}")
    Send("{ENTER}")
    ;"Array: ListView Display"
EndFunc


setupasw()
splitnow()

This is the section i'm trying to edit:

#Include <File.au3>
#Include <Array.au3>

Global $NewOrderFile, $onum = "", $line = "", $arrTemp = 0

;Read in the entire file to an array

Func splitnow()

If _FileReadToArray("TEST.CSV", $NewOrderFile) == 0 Then
    MsgBox(0,"error", "Error reading in file.")
    Exit
EndIf


For $curLine = 1 To $NewOrderfile[0]

    ;clean and split the current line
    $line = StringSplit(StringStripWS($NewOrderFile[$curLine],7), ",")


    If $onum <> $array[2] Then
        ;the order number is different
        $onum = $array[2]

        ;send the array for the last order number on for processing
        ;added isArray check to prevent sending on the first loop
        If IsArray($arrTemp) Then
            processOrders($arrTemp)
        EndIf

        ;start a new temp array for the new order number
        Dim $arrTemp[1] = [$NewOrderFile[$curLine]]
    Else
        ;the order number is the same, add the line to the temp array for the current order number
        _ArrayAdd($arrTemp,$NewOrderFile[$curLine])
    EndIf

Next

;send along the last order number after the FOR loop hits the last line in the file
If UBound($arrTemp) Then
    processOrders($arrTemp)
EndIf
EndFunc


Func processOrders($arrOrders)

    ;work with array $arrOrders here, all elements will be lines with the same order number
    ; ( $arrOrders is array of plain text lines, each element is an entire line from the file )
    _ArrayDisplay($arrOrders)


    For $order In $arrOrders

            $order = StringSplit(StringStripWS($order,7), ",")

            WinActivate("Session A - [24 x 80]", "")

            ;WinActivate("Untitled - Notepad")

                Send($order[16])  ; item number
                Send("{NUMPADADD}")
                Send($order[18])  ; item qty
                Send("{NUMPADADD}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send("{TAB}")
                Send($order[19])  ; per item price
                Send("{NUMPADADD}")
                Send("{ENTER}")
            Next

EndFunc

splitnow()

I've come up with this from borrowing others code on this forums and tweaking it. this code works stand-alone, but i'm not sure how to incorporate it.

#Include <Array.au3>

Func formatdata()
    Global $file, $array, $final, $i, $split, $start, $final
    
    
While 1
    $line = FileReadLine('test.csv');, FileGetSize('test.csv'))
    If Not @error Then
    $clean = StringReplace($line, '""', '||')
    $array = StringSplit($clean, '"')
    If Not @error Then
        $line = ''
    ; See if 1st element is quoted
        If StringLeft($array[1], 1) = '"' Then
            $start = 1
        Else
            $start = 2
        EndIf
    ; Replace the quotes in the elements with pipes
        For $i = $start To $array[0] Step 2
            $array[$i] = StringReplace($array[$i], ',', '|')
        Next
    ; Return the array elements back into a single variable
        For $i = 1 To $array[0]
            $line = $line & $array[$i]
        Next
    ;; Split the variable into an array with the pipes.
        $split = StringSplit($line, ',')
        If Not @error Then
            For $i = 1 To $split[0]
                $split[$i] = StringReplace($split[$i], '|', ',')
            Next
        EndIf
    EndIf
EndIf

; The array $plit holds your final elements.

;splitnow($split)
WEnd
EndFunc

formatdata()

the problem with this script here, is that it puts all the data into a 1 column array instead of lines that can be processed easier.

So I'm thinking something like this might be on the right track of incorporating the two ideas into one by just having my original script do the replacing of the delimiters on the fly:

Func splitnow()

If _FileReadToArray("TEST.CSV", $NewOrderFile) == 0 Then
    MsgBox(0,"error", "Error reading in file.")
    Exit
EndIf


For $curLine = 1 To $NewOrderfile[0]

    ;clean and split the current line
    $line = StringSplit(StringStripWS($NewOrderFile[$curLine],7), ",")


        $clean = StringReplace($line, '""', '||')
        $array = StringSplit($clean, '"')


    If $onum <> $array[2] Then
        ;the order number is different
        $onum = $array[2]

        ;send the array for the last order number on for processing
        ;added isArray check to prevent sending on the first loop
        If IsArray($arrTemp) Then
            processOrders($arrTemp)
        EndIf

        ;start a new temp array for the new order number
        Dim $arrTemp[1] = [$NewOrderFile[$curLine]]
    Else
        ;the order number is the same, add the line to the temp array for the current order number
        _ArrayAdd($arrTemp,$NewOrderFile[$curLine])
    EndIf

Next

;send along the last order number after the FOR loop hits the last line in the file
If UBound($arrTemp) Then
    processOrders($arrTemp)
EndIf
EndFunc

however when i run this section by itself if gives the error: Array variable has incorrect number of... meaning its not passing the array correctly or its getting messed up somewhere? I think i'm not understanding how to incorporate them both...

Basically the idea of this section 

For $curLine = 1 To $NewOrderfile[0]

    ;clean and split the current line
    $line = StringSplit(StringStripWS($NewOrderFile[$curLine],7), ",")


        $clean = StringReplace($line, '""', '||') ; <---------- added for this forum post
        $array = StringSplit($clean, '"')  ; <---------------- added for this forum post


    If $onum <> $array[2] Then
        ;the order number is different
        $onum = $array[2]
is to have the file get split into lines, clear extra white spaces if any, replace "" with ||, and then split the file into lines at the end of each line marked by ".

I have attached a sample data file.

test.zip

Edited by Alupis
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

You might review some basic rules of CSV formatting. There are standard ways to handle delimiting the text within fields.

:unsure:

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
Alupis Seeker

Alupis

Posted
  • Author
Posted (edited)

hmm... thanks PsaltyDS for the assistance...

after reviewing the link you provided i definetely understand CSV's better... however i dont think i'll be able to make use of any of this info...

THe problem is we have customers who type their mailing addresses in about 40 different ways! some put extra commas, some quote stuff, etc... so basically i can't just use one delimeter, comma or quotes, to have autoit split the file up and load it into an array...

for example addresses can be: "Bob","Frank","Industries, Inc.","123 Test St., Unit 5","Oakland","CA", etc...

"Jimmy "Jim"","Smith","Company Inc.","456 Test St. Unit 6","Oakland","CA", etc..

in these examples the first line should be read by autoit as such: Bob,Frank,Industries Inc.,123 Test St. Unit 5, Oakland, CA, etc...

and the second line should be as such: Jimmy Jim, Smith, Company Inc., 456 Test St. Unit 6, Oakland, CA, etc...

Because of this variation autoit reads the lines incorrectly if using just commas as a delimiter, and the same if you use quotes.

This got me to thinking that if autoit can read through the file, replace the ","'s with a || changing the data to: "Bob||Frank||Industries, Inc.||123 Test St., Unit 5||Oakland||CA||etc...

Then have it go through the new altered data and look for single ", and replace with | changing the data to: |Bob||Frank||Industries, Inc.||123 Test St., Unit 5||Oakland||CA||etc...

we now have | as delimiter and can work with the data a lot more consistently, ignoring all ,'s... so basically work with the data in between the |'s

i have this worked out in one of the scripts in my original post, but i just can't seem to work the two into eachother... i guess i dont understand how to get my original script to perform this function before continuing...

Edited by Alupis
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...