Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

edit a CSV and "in-place" replacement

by slayedbylucifer (Scribe)
on Jun 22, 2012 at 06:37 UTC ( #977784=perlquestion: print w/replies, xml ) Need Help??
slayedbylucifer has asked for the wisdom of the Perl Monks concerning the following question:

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:

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 "+912234567899".

Please provide your inputs.


Replies are listed 'Best First'.
Re: edit a CSV and "in-place" replacement
by Tux (Abbot) on Jun 22, 2012 at 06:47 UTC

    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;

    Enjoy, Have FUN! H.Merijn
      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.
Re: edit a CSV and "in-place" replacement
by slayedbylucifer (Scribe) on Jun 22, 2012 at 06:43 UTC

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

    I want to change it from "02234567899" to "+912234567899".


      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.

        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.

Re: edit a CSV and "in-place" replacement
by sundialsvc4 (Abbot) on Jun 22, 2012 at 19:00 UTC

    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://977784]
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2018-06-24 10:16 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.