Please help with autoit script needing human intervention

[seandisanti]

Sorry...here it is

ok, patterns changed a little bit, country is not in new data, and there are a couple extra spaces in new data. changed patterns here, works with new test data

expandcollapse popup

#include<file.au3>;so that we can use _FileReadToArray
#include<array.au3>;so that we can use _ArrayToString

dim $FullTexta,$fulltexts,$date,$name,$country,$street,$city,$state,$zip,$tel,$email,$quantity,$corr,$comments;declaring variables is always a good idea
_FileReadToArray("c:\cribtentexport.txt",$FullTexta);creates an array
$fulltexts=_ArrayToString($FullTexta,"\");turns array into a backslash delimited blob
ClipPut($fulltexts)
;the regular expressions below this point create arrays of each of the pieces of data
;that you're looking for based on the backslash delimiters (which was a horrible choice of delimiter on my part but i'm not redoing it)
$date = StringRegExp($fulltexts,'(?:Sent:)(.*?\\)',3)
$name = StringRegExp($fulltexts,'(?:Name \\\|.*?\\\|)(.*?\\)',3)
;$country = StringRegExp($fulltexts,'(?:Country\\\|\\\|)(.*?\\)',3);commented out country because it was not on new sample data
$street = StringRegExp($fulltexts,'(?:Street Address \\\| \\\|)(.*?\\)',3)
$city = StringRegExp($fulltexts,'(?:City \\\| \\\|)(.*?\\)',3)
$state = StringRegExp($fulltexts,'(?:State \\\| \\\|)(.*?\\)',3)
$zip = StringRegExp($fulltexts,'(?:Zip Code \\\| \\\|)(.*?\\)',3)
$tel = StringRegExp($fulltexts,'(?:Telephone \\\| \\\|)(.*?\\)',3)
$email = StringRegExp($fulltexts,'(?:Email \\\| \\\|)(.*?\\)',3)
$quantity = StringRegExp($fulltexts,'(?:Quantity \\\| \\\|)(.*?\\)',3)
$corr = StringRegExp($fulltexts,'(?:Consumer or Retailer \\\| \\\|)(.*?\\)',3)
$comments = StringRegExp($fulltexts,'(?:Comments \\\| \\\|)(.*?\\)',3)

$oEx = ObjGet("","excel.Application");If you have excel open, this grabs ahold of the application to make it do our bidding
$oWb = $oEx.Workbooks.Add;creates a new workbook just incase the one you were working on wasn't intended to be for this project
If IsObj($oWb) = 0 Then;if you didn't have excel open it will error out (you could also have it create an instance but it's easy i'll let you
    MsgBox(0,"Error","Failed to create workbook, exiting.  open excel and try again")
    Exit
EndIf
$oWS = $oWb.ActiveSheet;grabs the active sheet in the new workbook
;then all the headers are added to the first row
$oWS.Range("a1").Formula = "Date"
$oWS.Range("b1").Formula = "Name"
$oWS.Range("c1").Formula = "Country"
$oWS.Range("d1").Formula = "Street Address"
$oWS.Range("e1").Formula = "City"
$oWS.Range("f1").Formula = "State"
$oWS.Range("g1").Formula = "Zipcode"
$oWS.Range("h1").Formula = "Telephone"
$oWS.Range("i1").Formula = "Email"
$oWS.Range("j1").Formula = "Quantity"
$oWS.Range("k1").Formula = "Consumer or Retailer"
$oWS.Range("l1").Formula = "Comments"

;this loop adds the data from the various arrays into their coloumns
For $x = 2 to UBound($date);starting with an iterator of 2 because we do not want to overwrite headers
    $oWS.Range("a" & $x).Formula = StringTrimRight($date[$x-2],1);string trim gets rid of the trailing backslash i was too lazy to
    $oWS.Range("b" & $x).Formula = StringTrimRight($name[$x-2],1);continue fighting with
;commenting out country because it's not in new data
    ;$oWS.Range("c" & $x).Formula = StringTrimRight($country[$x-2],1);and we are subtracting 2 from $x because even though the spreadsheet
    $oWS.Range("d" & $x).Formula = StringTrimRight($street[$x-2],1);needs to be populated at the second row, the first element of the array
    $oWS.Range("e" & $x).Formula = StringTrimRight($city[$x-2],1);is 0
    $oWS.Range("f" & $x).Formula = StringTrimRight($state[$x-2],1)
    $oWS.Range("g" & $x).Formula = StringTrimRight($zip[$x-2],1)
    $oWS.Range("h" & $x).Formula = StringTrimRight($tel[$x-2],1)
    $oWS.Range("i" & $x).Formula = StringTrimRight($email[$x-2],1)
    $oWS.Range("j" & $x).Formula = StringTrimRight($quantity[$x-2],1)
    $oWS.Range("k" & $x).Formula = StringTrimRight($corr[$x-2],1)
    $oWS.Range("l" & $x).Formula = StringTrimRight($comments[$x-2],1)
Next
$oWb.SaveAs("c:\cribtentfinal");this is to save your workbook with the name that you want

***edit***

apparently "autotit" doesn't close the tag

Edited August 20, 2010 by cameronsdad

[seandisanti]

Now one thing that jumps out at me with the new sample data, is that it is lacking the country field. was that one that you'd put into the original text in error, or are some fields only there occasionally? because if you've got an export with 6 fields on one and 5 on the rest, the approach i've used will not work as even if you code around it (using @error at regex time to avoid writing from non existent arrays at the bottom) the additional fields will end up associated with the top rows first... the easy fix is to make sure your data is standardized and headers won't vary, otherwise there's going to be more of a chore of chopping the export into it's individual emails and then each email into fields... and i don't want to do that

[rcareno]

Ok, so i realized initially in the first 20 emails, there was no country field to enter, but every one after that has a country code, so i re-added the country code to the script. After running script on all the e-mails with the country code included, it worked flawlessly, and very fast!! You are awesome!!! I will be dedicating more of my time to learning autoit.

I actually use it quite a bit at work to automate a lot of tasks that are tedious, but there is still so much to learn

Thank you so much for your time and effort on all of this.

Talk to you soon

[seandisanti]

Ok, so i realized initially in the first 20 emails, there was no country field to enter, but every one after that has a country code, so i re-added the country code to the script. After running script on all the e-mails with the country code included, it worked flawlessly, and very fast!! You are awesome!!! I will be dedicating more of my time to learning autoit.

I actually use it quite a bit at work to automate a lot of tasks that are tedious, but there is still so much to learn

Thank you so much for your time and effort on all of this.

Talk to you soon

no problem, i'm glad to help