Adding Dashes to lines in a text fle

[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.

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

[PsaltyDS]

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.

[Fulano]

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 Numbers

3 Copy the data from the text file into the new column

4 Sort just this new column

Either in the script or manually

- I haven't worked with conditional formating through VBA, so you might have to do this on by hand

5 Add conditional formating to highlight duplicates (It should be one of the presets)

Do this part manually

Visually 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 make

it significantly smarter

If all is clear Excel has a wonderful 'Remove Duplicates' Function

A (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

[dani]

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")

[PsaltyDS]

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

[dani]

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

[Fulano]

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

Guys: missing the point, he got that part already. It's the finding duplicates that's the current question.

[PsaltyDS]

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().

[Fulano]

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.