atnextc Posted March 1, 2010 Share Posted March 1, 2010 (edited) Hey guys, I've got a text file with phone numbers like 770222555 what I need to do is I have to cross check an excel sheet with these numbers "quickly", the problem is on the excel sheet they are listed as 770-222-55555. So very simply I need a script that can either read the contents of a closed file and and the 7702225555 to 770-222-5555 and resave it, or I need it to be able to just go down the list as its open and do it. IS this possible? ====================================================================================================== I FIXED THE PROBLEM ABOVE ALso is it possible to have a script check to the two for matches and highlight the cells in which the phone numbers that match are located? I don't have a script written yet as I like to plan things out but more importantly I just need to know if it can be done. I'm not a scripter but I can do basic stuff with Autoit. Thank You Atnextc Edited March 1, 2010 by atnextc Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 1, 2010 Share Posted March 1, 2010 Have you looked at the Excel.au3 UDF functions in the help file? The easiest way would probably be to read the whole worksheet into an array once with _ExcelSheetReadToArray() and then search that for each of your list items. 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...
Fulano Posted March 1, 2010 Share Posted March 1, 2010 (edited) Excel has some nice tricks for finding duplicates. You might consider this:Write a script to:1 Sort the data by the phone number column (assuming that there is data matched to it)2 Add a column, right before the phone number column and give it a heading like: Imported Numbers3 Copy the data from the text file into the new column4 Sort just this new columnEither in the script or manually - I haven't worked with conditional formating through VBA, so you might have to do this on by hand5 Add conditional formating to highlight duplicates (It should be one of the presets)Do this part manuallyVisually inspect for false positives. If you delete something with COM it is gone for go, no undo, so do yourself a favor and don't trust a simple script to make complicated decisions unless you are willing to spend the time to makeit significantly smarterIf all is clear Excel has a wonderful 'Remove Duplicates' FunctionA (contrived) example to get you started:;Excel manipulation example: opens, sorts by column, adds a column, fills rows 1 to 100 with the number '1', saves, and closes #include <Excel.au3> Local $COLUMN = 9 Local $file = FileOpenDialog ("File to clobber:", "", "Excel (*.xls)|Excel (*.xlsx)") If @error Then Exit Local $oExcel = _ExcelBookOpen ($file, 0) $oExcel.ActiveSheet.Cells.Sort ($oExcel.Columns($COLUMN), $xlDescending, Default, Default, Default, Default, Default, $xlYes) $oExcel.Columns($COLUMN).Insert (-4161) ; Column $COLUMN is now the blank column -4161 is an Excel Constant For $i = 1 to 100 $oExcel.Cells($i, $COLUMN).Value = 1 Next _ExcelBookSave ($oExcel) _ExcelBookClose ($oExcel)Good Luck Edited March 1, 2010 by Fulano #fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja! Link to comment Share on other sites More sharing options...
dani Posted March 1, 2010 Share Posted March 1, 2010 Just a small Regular Expression to convert 770222555 to 770-222-55555$num = 77022255555 $new = StringRegExpReplace($num, "(\d{3})(\d{3})(\d{5})", "\1-\2-\3") Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 1, 2010 Share Posted March 1, 2010 (edited) Y'all might want to agree on how many digits there are in a phone number first. @atnextc: "Hey guys, I've got a text file with phone numbers like 770222555 what I need to do is I have to cross check an excel sheet with these numbers "quickly", the problem is on the excel sheet they are listed as 770-222-55555."@4ni: "$new = StringRegExpReplace($num, "(\d{3})(\d{3})(\d{5})", "\1-\2-\3")" P.S. If there are any country code in the mix, the fun really begins! Edited March 1, 2010 by PsaltyDS 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...
dani Posted March 1, 2010 Share Posted March 1, 2010 (edited) I read this:the problem is on the excel sheet they are listed as 770-222-55555.So assumed there apparently were five 5s I didn't even see those suddenly turned into three or four 5s in other parts of his first post... Confusing, so atnextc, how many does it have anyway (I don't live in the US ) Edited March 1, 2010 by d4ni Link to comment Share on other sites More sharing options...
Fulano Posted March 3, 2010 Share Posted March 3, 2010 Hey guys, I've got a text file with phone numbers like 770222555 what I need to do is I have to cross check an excel sheet with these numbers "quickly", the problem is on the excel sheet they are listed as 770-222-55555.So very simply I need a script that can either read the contents of a closed file and and the 7702225555 to 770-222-5555 and resave it, or I need it to be able to just go down the list as its open and do it.IS this possible?======================================================================================================I FIXED THE PROBLEM ABOVEGuys: missing the point, he got that part already. It's the finding duplicates that's the current question. #fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja! Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 3, 2010 Share Posted March 3, 2010 Just read the column to an array with _ExcelReadArray(), loop through the array to remove unwanted formatting with something like StringReplace(), then get unique entries with _ArrayUnique(). 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...
Fulano Posted March 3, 2010 Share Posted March 3, 2010 Excel is designed for working with strings, so unless you have a short number of records (less than 1000), Excel is going to be monumentally faster.The main reason has to do with the way the Excel stores strings internally, it stores them in what is called a "Pascal Style String", basically it's got the string size stored in the first byte, which eliminates the seeking you have to do with the conventional c-style strings (which terminate the string with a '\0' character and results in a boatload of seeking). Long story short: it makes it wicked fast.You can read more about it here if you want to know more. #fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja! 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