Hi all,
I have a comma delimited text file with rows like:
"txtLastName","txtFirstName","chrSSN","txtPhoneNumber","vchrAddress1","vchrtxtAddress2","chrCity","chrState","vchrHomeAddressZipCode"
"Allen","Arnold","000-00-0000","555-555-1234","123 Indian Rd.",,"Cityville","CA",12345
--
So far...
the expression i have wrote is: [0-9][0-9][0-9]\-[0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]
it finds phone numbers in a comma delimited .txt file like 123-456-7890
I think I also need to include the quotes on the phone number too, but i don't know how. I have looked for regex resources high and low to find out...
i want to seperate the areacode from the rest of the number and change the text file so if it said:
"Allen","Arnold","000-00-0000","555-555-1234","123 Indian Rd.",,"Cityville","CA",12345
I could use regex to find and replace the phone number so it would become:
"Allen","Arnold","000-00-0000","555", "555-1234","123 Indian Rd.",,"Cityville","CA",12345
--
Please help!! This text file is giving me a sever headache - it is 7000+ records long!!!
Sincerely,
-Josh Tiner
jtiner@tampabay.rr.com
Regular Expressions in a comma delimited .txt file
Moderators: AmigoJack, bbadmin, helios, Bob Hansen, MudGuard
-
Jens Hollmann
Re: Regular Expressions in a comma delimited .txt file
If the first telephon-number is always filled with zeros and should not be converted a possible solution would be:
search: 0000","\([0-9]+\)-\([0-9-]+\)"
replace: 0000","\1","\2"
Explanation: Search for the literal text 0000"," followed by some digits and mark these as a group. This first group should be the area code. then comes a minus sign followed by any digits and minus signs (!) until a " is reached. all these digits excluding the quotation are marked as a second group.
Regular expressions should be checked, of course. And this is the Not-Posix syntax! Check this in your settings. It is the default.
If the first telephone-number is not always filled with zeros, replace 0000 with \([0-9]+\) in the search string. The replace string should then be \1","\2","\3"
If the first telephone number should be converted too, just leave out the 0000", part in both strings.
HTH
search: 0000","\([0-9]+\)-\([0-9-]+\)"
replace: 0000","\1","\2"
Explanation: Search for the literal text 0000"," followed by some digits and mark these as a group. This first group should be the area code. then comes a minus sign followed by any digits and minus signs (!) until a " is reached. all these digits excluding the quotation are marked as a second group.
Regular expressions should be checked, of course. And this is the Not-Posix syntax! Check this in your settings. It is the default.
If the first telephone-number is not always filled with zeros, replace 0000 with \([0-9]+\) in the search string. The replace string should then be \1","\2","\3"
If the first telephone number should be converted too, just leave out the 0000", part in both strings.
HTH