regex - find and replace and convert to csv format

Question

I have a file that contains phone numbers and would like to create a csv file out of it.

The issue I face is that the format is not fixed and not easy to parse.

Each row contains one, two or three phone records.
A phone might start or not with (+xxx) and the second phone might be preceded of the '&' or not.

I was trying to built a regex that could split each row in 3 groups and then find/replace into the expected format but without success.

Anyone could come up with an regex that could identify each group per row?

Input

(+999) 11 762 52 61 (+999) 11 762 41 11
(+999) 44 695 01 76 & 44 695 01 89
(+999) 21 510 02 14 (+999) 21 511 97 98
(+999) 01 05 00 18 67
(+999) 21 552 42 12
(+999) 21 557 86 60 (+999) 21 557 86 72
(+999) 11 873 93 13 & 11 825 59 92
(+999) 15 307 57 15 & 15 307 57 16 & (+999) 11 974 19 57
(+999) 21 551 91 51 (+999) 21 551 91 68
(+999) 21 551 71 71 & 21 551 72 32
(+999) 21 527 30 00 (+999) 21 551 54 89
(+999) 11 621 15 00 (+999) 11 626 20 75
(+999) 21 555 21 60 (+999) 21 555 21 71 (+999) 12 804 76 30
(+999) 11 234 18 96 (+999) 11 234 54 48
(+999) 11 828 35 37 (+999) 11 828 63 76 (+999) 41 363 27 23
(+999) 11 690 03 00 (+999) 11 315 65 38
(+999) 08 32 60 34 65
(+999) 08 32 60 34 65 & (+999) 11 784 46 70 & (+999) 11 784 61 79

Expected Result:

(+999) 11 762 52 61, (+999) 11 762 41 11,
(+999) 44 695 01 76, 44 695 01 89,
(+999) 21 510 02 14, (+999) 21 511 97 98,
(+999) 01 05 00 18 67,,
(+999) 21 552 42 12,,
(+999) 21 557 86 60, (+999) 21 557 86 72,
(+999) 11 873 93 13, 11 825 59 92,
(+999) 15 307 57 15, 15 307 57 16, (+999) 11 974 19 57
(+999) 21 551 91 51, (+999) 21 551 91 68,
(+999) 21 551 71 71, 21 551 72 32,
(+999) 21 527 30 00, (+999) 21 551 54 89,
(+999) 11 621 15 00, (+999) 11 626 20 75,
(+999) 21 555 21 60, (+999) 21 555 21 71, (+999) 12 804 76 30
(+999) 11 234 18 96, (+999) 11 234 54 48,
(+999) 11 828 35 37, (+999) 11 828 63 76, (+999) 41 363 27 23
(+999) 11 690 03 00, (+999) 11 315 65 38,
(+999) 08 32 60 34 65,,
(+999) 08 32 60 34 65, (+999) 11 784 46 70, (+999) 11 784 61 79

If you're using python --> why don't you count the amount of numbers in a row? — Dieter
– Dieter, Commented Apr 9, 2017 at 21:22

Dieter · Accepted Answer · 2017-04-09 22:00:59Z

1

import math

for l in file: 

    nr_of_prefixes = l.count('(+')  # amount of prefixes (+xxx)
    prefixes = nr_of_prefixes * 3 # count the characters of a prefix
    numbers = sum(c.isdigit() for c in l) # amount of numbers in a string
    numbers -= prefixes # remove the prefixes
    telephone_numbers = math.floor(numbers / 8) # number of digits


    l = l.replace(' (+',', (+') # add a , to    (+
    l = l.replace(' &',',')     # replace a & by a comma
    l = l.replace(',,',',')     # replace double ,,  by a single  ,

    # if there where only 2 phone numbers, add an ending comma
    if telephone_numbers < 3:
        l += ","

    # if there was only 1 phone numbers, add an extra comma
    if telephone_numbers < 2:
        l += ","

    # print, or add to a list 
    print(l)

edited Apr 9, 2017 at 22:00

answered Apr 9, 2017 at 21:31

Dieter

2,6892 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alg_D Over a year ago

Yes, that rocks! Thanks

Stephane Janicaud · Accepted Answer · 2017-04-09 21:44:51Z

0

Use the following regex : ((\(\+999\)[\d ]+)|& ([\d ]+))

Here is a sample with your file content :

https://regex101.com/r/Q8grqd/1

And the python code generated by regex101 code generator

import re

regex = r"((\(\+999\)[\d ]+)|& ([\d ]+))"

test_str = ("(+999) 11 762 52 61 (+999) 11 762 41 11\n"
    "(+999) 44 695 01 76 & 44 695 01 89\n"
    "(+999) 21 510 02 14 (+999) 21 511 97 98\n"
    "(+999) 01 05 00 18 67\n"
    "(+999) 21 552 42 12\n"
    "(+999) 21 557 86 60 (+999) 21 557 86 72\n"
    "(+999) 11 873 93 13 & 11 825 59 92\n"
    "(+999) 15 307 57 15 & 15 307 57 16 & (+999) 11 974 19 57\n"
    "(+999) 21 551 91 51 (+999) 21 551 91 68\n"
    "(+999) 21 551 71 71 & 21 551 72 32\n"
    "(+999) 21 527 30 00 (+999) 21 551 54 89\n"
    "(+999) 11 621 15 00 (+999) 11 626 20 75\n"
    "(+999) 21 555 21 60 (+999) 21 555 21 71 (+999) 12 804 76 30\n"
    "(+999) 11 234 18 96 (+999) 11 234 54 48\n"
    "(+999) 11 828 35 37 (+999) 11 828 63 76 (+999) 41 363 27 23\n"
    "(+999) 11 690 03 00 (+999) 11 315 65 38\n"
    "(+999) 08 32 60 34 65\n"
    "(+999) 08 32 60 34 65 & (+999) 11 784 46 70 & (+999) 11 784 61 79")

matches = re.finditer(regex, test_str, re.MULTILINE)

for matchNum, match in enumerate(matches):
    matchNum = matchNum + 1

    print ("Match {matchNum} was found at {start}-{end}: {match}".format(matchNum = matchNum, start = match.start(), end = match.end(), match = match.group()))

    for groupNum in range(0, len(match.groups())):
        groupNum = groupNum + 1

        print ("Group {groupNum} found at {start}-{end}: {group}".format(groupNum = groupNum, start = match.start(groupNum), end = match.end(groupNum), group = match.group(groupNum)))

edited Apr 9, 2017 at 21:44

answered Apr 9, 2017 at 21:37

Stephane Janicaud

3,7051 gold badge16 silver badges21 bronze badges

3 Comments

Alg_D Over a year ago

This is already something but not really correct. It repeats the matches and is far from the desired output. but thanks anyway :)

Stephane Janicaud Over a year ago

I provided the regex to extract phone numbers on each row, isn't it what you wanted ?, Sorry I know regex, not python, code is generated from regex 101, I thought you knew how to use it ;)

Alg_D Over a year ago

The regex seems not to provide exactly what I was looking for, for example if you use an editor like notepad++, pycharm, etc and use it on the find/replace (as regex) and replace in 3 grpups (\1,\2,\3) dos not work for all the cases

Collectives™ on Stack Overflow

regex - find and replace and convert to csv format

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related