Alupis Posted April 14, 2011 Share Posted April 14, 2011 (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!) expandcollapse popup#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: expandcollapse popup#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. expandcollapse popup#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: expandcollapse popupFunc 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 April 14, 2011 by Alupis Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 15, 2011 Share Posted April 15, 2011 You might review some basic rules of CSV formatting. There are standard ways to handle delimiting the text within fields. 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 More sharing options...
Alupis Posted April 15, 2011 Author Share Posted April 15, 2011 (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 April 15, 2011 by Alupis Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now