edit a CSV and "in-place" replacement

[slayedbylucifer]

hello Monks,

I need some regex help. I have a csv where in I want to edit the 35th field. In 35th field, I want to replace the "0" which is in the beginning of the string "and" is followed by 10 digits with "+91" and then write the change back. I do not want the output on the console but want to have it written back to the same file in the same field.

I thought this would have been an easier one with sed/awk but I could not yet make it work. Here is my thread with the sed/awk exploration:

http://stackoverflow.com/questions/11138434/sed-in-place-replacement

So, Now I am thinking of doing it with perl. Here is one sample input:

foo,foo,foo,,,,,,,,,,,,,,,,,,,,,,,* My Contacts ::: Phone Only,,,,,,,M
+obile,02234567899,,,,,,,,,,,,,,,,,

So in above line, the number happens to be the 35th field. I want to change it from "01234567899" to "[phone]".

Please provide your inputs.

-Thanks.

[Tux]

DBD::CSV. Assuming "foo.csv" and a header line where the first (key) field is c_foo and the header for column 35 is "tel" (untested, but you get the drift):

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    RaiseError => 1,
    PrintError => 1,
    f_dir      => "/test/foo",
    f_ext      => ".csv/r",
    f_encoding => "urf-8",
    });
my $stu = $dbh->prepare ("update foo set tel = ? where c_foo = ?");
my $sth = $dbh->prepare ("select c_foo, tel from foo");
$sth->execute;
$sth->bind_columns (\my ($c_foo, $tel));
while ($sth->fetch) {
    $tel =~ s/^0/+91/ and $stu->execute ($tel, $c_foo);
    }
$dbh->commit;

[slayedbylucifer]

Thanks Tux. Your reply got me thinking about using the CSV module and how to leverage it for more complex operation. thanks for your input.

[slayedbylucifer]

TYPO. there was typo in my origional post. It should read follow:

I want to change it from "02234567899" to "[phone]".

Thanks.

[bitingduck]

If you can be sure the regex won't match any other field, you can try:

perl -p -i -e 's/\b0(\d{10})\b/\+91$1/g;' filename

If there are other fields that it could match and munge, then it could all go horribly wrong and you're better off with something that accurately replaces only that field, like Tux already posted.

Update: I just took a look over at your thread at SO. You're trying a dangerous thing by counting commas in a CSV. Any element of a CSV can have commas inside of quotes to protect them, so you could have any number of commas before the 35th element in a row. CSV is a deceptive format-- it seems all simple and benign, but can be complicated in really annoying ways. If the quick and dirty regex won't work, use a module like Tux shows that is designed to deal with the unexpected but legal cases in the CSV.

[slayedbylucifer]

Wonderful. This is what I wanted. Your one liner is working perfectly. Thank you very much.

Yes, I understand that counting the commas is not the right way, but the file in question is my google contacts file exported to CSV and hence I was sure that there are not commas in between.

Thanks again.

[sundialsvc4]

Weighing in here, there is n-o .. w-a-y (... “in helll...”) that I would approach this problem in any way other than to first handle the record in a CSV-processing Perl package, then do the manipulation on the 35th item, then use the same package to reassemble the string.

The key issue here, of course, is maintainability.   (Got CPU-cycles to burn ... don’t care one whit about that.)   The solutions that you pick must not only work “right now,” but they also must be durable.   Fixed in such a way that it stays fixed, forever.   No matter what (within reason) you or somebody else does next.   Otherwise, you wind up creating fragile code, full of so many functional interdependencies that it just can’t stand-up any more on its own two feet.   Heh, it’s a hall-of-mirrors in which every single mirror is cracked.   (Been there, seen that.   All of us have, ’round these parts, and we’re still slurpin’ the Alka-Seltzer for it.)   Not pretty.)   No, when you do one thing to a complicated structure (and a CSV-file actually does qualify as “complicated,” as the authors of those CPAN modules can tell you), you want to be very sure that you’re taking the long view of things.   Even though we do like to fool around with things like “golf” around here, just for our own entertainment, cleverness actually does not win Brownie Points.