Dave Dave - 4 months ago 8
Perl Question

Having trouble using perl command line to do a search and replace

I'm using bash shell on Mac YOsemite. I have a file that contains lines like

560c4f458b97330d00b0832d 5237d5eee1399d2f11005571
560c4f458b97330d00b0832e 55c4a0001b9e2d0e00032d6f
560c4f458b97330d00b0832f 55ce2e78e1055a0d008cad3c
560c4f458b97330d00b08330 55ce2e78e1055a0d008cad44
560c4f458b97330d00b08331 55ce2e78e1055a0d008cad4a
560c4f458b97330d00b08332 55c4a0001b9e2d0e00032d78
560c4f458b97330d00b08333 55c4a0001b9e2d0e00032d7b
560c4f458b97330d00b08334 55ce2e78e1055a0d008cad51


I’m trying to write an expression that will turn the file into a series of SQL statements. So I tried

perl -pi -e “s/(.*?)\s(.*)/update my_sql_table set my_user_id = '$2' where my_user_id = '$1';/g" /tmp/myfile.csv


Hoping to get a file of lines line

update my_sql_table set my_user_id = '5237d5eee1399d2f11005571' where my_user_id = '560c4f458b97330d00b0832d';
update my_sql_table set my_user_id = '55c4a0001b9e2d0e00032d6f' where my_user_id = '560c4f458b97330d00b0832e';


But instead the output is one massive line of statements that look like

update my_sql_table set my_user_id = '' where my_user_id = '';update my_sql_table set my_user_id = '' where my_user_id = '';update my_sql_table set my_user_id = '' where my_user_id = '';update my_sql_table set my_user_id = ''


How can I correct my above statement to put the tokens in the right place?

Answer

This is simple using Perl's command-line autosplit option

perl -anE'print qq{update my_sql_table set my_user_id = \'$F[1]\' where my_user_id = \'$F[0]\';\n}' ids.txt

output

update my_sql_table set my_user_id = '5237d5eee1399d2f11005571' where my_user_id = '560c4f458b97330d00b0832d';
update my_sql_table set my_user_id = '55c4a0001b9e2d0e00032d6f' where my_user_id = '560c4f458b97330d00b0832e';
update my_sql_table set my_user_id = '55ce2e78e1055a0d008cad3c' where my_user_id = '560c4f458b97330d00b0832f';
update my_sql_table set my_user_id = '55ce2e78e1055a0d008cad44' where my_user_id = '560c4f458b97330d00b08330';
update my_sql_table set my_user_id = '55ce2e78e1055a0d008cad4a' where my_user_id = '560c4f458b97330d00b08331';
update my_sql_table set my_user_id = '55c4a0001b9e2d0e00032d78' where my_user_id = '560c4f458b97330d00b08332';
update my_sql_table set my_user_id = '55c4a0001b9e2d0e00032d7b' where my_user_id = '560c4f458b97330d00b08333';
update my_sql_table set my_user_id = '55ce2e78e1055a0d008cad51' where my_user_id = '560c4f458b97330d00b08334';