Link to home
Start Free TrialLog in
Avatar of richsark
richsarkFlag for United States of America

asked on

Search and replace command for a csv file

Hi There folks,

I have a csv file that I need to replace the 15 field contents under the header "domain_name_servers"

The csv looks like this:

header-network,address*,netmask*,always_update_dns,boot_file,boot_server,broadcast_address,comment,ddns_domainname,ddns_ttl,deny_bootp,dhcp_members,disabled,domain_name,domain_name_servers,enable_ddns,enable_option81,enable_pxe_lease_time,enable_threshold_email_warnings,enable_threshold_snmp_warnings,enable_thresholds,generate_hostname,ignore_client_requested_options,is_authoritative,lease_time,network_view,next_server,pxe_lease_time,range_high_water_mark,range_high_water_mark_reset,range_low_water_mark,range_low_water_mark_reset,recycle_leases,routers,threshold_email_addresses,update_dns_on_lease_renewal,update_static_leases,zone_associations,OPTION-150,OPTION-44
network,10.1.92.0,255.255.255.0,,,,,SOT38-92-v392,,,,"acibgmc.odjfs.state.oh.us,soccibgm.odjfs.state.oh.us",False,,"10.1.5.17,10.14.242.69,10.14.4.69",,,,False,False,,,,,,default,,,95,85,0,10,,10.1.92.1,,,,,,
network,10.1.163.0,255.255.255.0,,,,,SOT30-163-VoIP-v463,,,,"acibgmc.odjfs.state.oh.us,soccibgm.odjfs.state.oh.us",False,,,,,,False,False,,,,,,default,,,95,85,0,10,,10.1.163.1,,,,,10.1.178.64 10.9.148.64,
network,10.1.165.0,255.255.255.0,,,,,SOT31-165-VoIP-v465,,,,"acibgmc.odjfs.state.oh.us,soccibgm.odjfs.state.oh.us",False,,,,,,False,False,,,,,,default,,,95,85,0,10,,10.1.165.1,,,,,10.1.178.64 10.9.148.64,

Now... if there is no IP already in that section leave it as is. So.. in another words, if there is a IP or IP's there, replace it with these:

10.0.1.136,10.0.1.152

Thanks !!
Avatar of arober11
arober11
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you considered: csvfix

else you could use awk e.g.

awk -F, '$15 ~ /[0-9.]*/ { OFS= ";"; $15 = "10.0.1.136"; }'
Avatar of richsark

ASKER

Humm, never heard of csvfix, I will check it out.

In reference to your awk, If I want to add two address seperated by a comma, would the awk command look like:

awk -F, '$15 ~ /[0-9.]*/ { OFS= ";"; $15 = "10.0.1.136";,"10.0.1.152" }'
As F15 and F16 or both IP's comma seperated in F15?

e.g.

Replace F15 and F16
awk  -F, -v OFS=, '{if ($15 ~ /[0-9.]+/) {$15="10.0.1.136";$16="10.0.1.152"}; print}' tst1.txt

Or:
Both in F15:

awk  -F, -v OFS=, '{if ($15 ~ /[0-9.]+/) {$15="\"10.0.1.136,10.0.1.152\""}; print}' tst1.txt
Yes. The ips are separated by a comma and inclosed in double quotes.  For the domain_name_servers
ASKER CERTIFIED SOLUTION
Avatar of arober11
arober11
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
interesting question...

would you like a DOS batch file program to do this?
Thanks.  But this should be fine.  I will test and close if no issues.

Thanks again
Nice work
Hey arober11... slight issue... sorry,

Looks like its changing the next field as well. I need it only to fix the column "domain_name_servers" Looks like its also doing stuff to the next column "enable_ddns"
Should have gone with the batch file I offered earlier on!
Alter the $15  to $14
Hi. You have two $15, which one should I change ?
Both
Ok... Call me stupid... if I counted the fields correctly, 15 is the one I want changed.  if I change to 14, that changes the field called "domain_name"

Sorry to sound dumb
An issue: We've specified the field separator as a comma, and awk dosen't care if it's in quotes or not, it will treat it as a field separator.  So for the lines with a comma embedded a text field, before $15 it will fail / use a previous column. Anyway as a quick frig, probably better to use csvfix, is to apply a bit of pre-processing to convert any embedded commas into '~', before the awk command e.g.

sed -e 's/\(,"[^,]*\),\([^,]*\),\([^,]*"\)/\1~\2~\3/g' -e 's/\(,"[^,]*\),\([^,]*"\)/\1~\2/g' tst1.txt | awk  -F, -v OFS=, '{if ($15 ~ /[0-9., "]+/) {$15="\"10.0.1.136,10.0.1.152\""}; print}' 

Open in new window


Note: will only convert 1 or 2 embedded commas, but simple to expand for more.

You can check the column name via:

sed -e 's/\(,"[^,]*\),\([^,]*\),\([^,]*"\)/\1~\2~\3/g' -e 's/\(,"[^,]*\),\([^,]*"\)/\1~\2/g' tst1.txt | head -1 | awk  -F, '{print $15}'

Note: to change the column increase / decrease the value in $15.
Hi richsark,

I've written this Perl one-liner for you.  You might find it simpler and more flexible (i.e. it should handle any number of embedded commas):
    perl -MText::CSV -ne 'BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]="10.0.1.136,10.0.1.152" if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string."\n"' infile >outfile

Or, if you want to update the original file, use this:
    perl -MText::CSV -i -ne 'BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]="10.0.1.136,10.0.1.152" if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string."\n"' infile

Notes: Please forgive my laziness, but I've "cheated" by using Perl's Text::CSV module, so if you don't already have that, you can get it from cpan.org.

If my code doesn't work, please explain how and provide the sample output you require.  Seems to work for me.
Hi folks, I have attached the master file.

For "tel2" I ran your line,  I get the result below. I do have CSV installed
C:\test>perl -MText::CSV -ne 'BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]="10.0.1.136,10.0.1.152" if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string."\n"'dh
cp-networks-Loaded.csv > outfile.csv

Open in new window


I get an error:
Can't find string terminator "'" anywhere before EOF at -e line 1.


For "arober11" I also ran your code, with an error.

$ sed -e 's/\(,"[^,]*\),\([^,]*\),\([^,]*"\)/\1~\2~\3/g' -e 's/\(,"[^,]*\),\([^,]*"\)/\1~\2/g' tst1.txt | awk  -F, -v OFS=, '{if ($15 ~ /[0-9., "]+/) {$15="\"10.0.1.136,200.200.200.200\""}; print}'dhcp-networks-Loaded.csv
sed: can't read tst1.txt: No such file or directory
awk: cmd. line:1: {if ($15 ~ /[0-9., "]+/) {$15="\"10.0.1.136,200.200.200.200\""}; print}dhcp-networks-Loaded.csv

Open in new window


awk: cmd. line:1: ^ syntax error
dhcp-networks-Loaded.csv
Sorry, richsark, I didn't realise your OS was Windows.  I should have asked, but for future questions, please specify your OS, as Perl (your primary zone) is most commonly used on UNIX/Linux, and there can be differences, especially for quotes in one-liners.  This works for me:
perl -MText::CSV -ne "BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]='10.0.1.136,10.0.1.152' if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string.\"\n\"\" dhcp-networks-Loaded.csv >outfile.csv

Open in new window

Note that under Windows the "-i" switch in the 2nd solution of my previous post will not work as is, coz Windows doesn't allow Perl in-place edits.  You can do things like: "-i.bak" though, which would rename the original file to dhcp-networks-Loaded.csv.bak, then create a new dhcp-networks-Loaded.csv file.  Remember not to put "> ..." on the end if you use "-i..." though.

BTW, you can remove the:
    ({quote_space=>0})
part if you don't mind those pairs of IPs in the 2nd to last field ending up with "quotes" around them.  I expect either way would usually work with CSV reading software.
Re the tst1.txt error, replace with your OWN Filename.
Hi richsark,

In my last post I assumed you were running this in Windows, because of your command prompt when you ran my command:
    C:\test>perl -MText::CSV ...

But I see that when you run arober11's command, the prompt seems to be UNIX/Linux:
    $ sed -e ...

What's your OS, richsark?  Windows?  UNIX/Linux?  Both?
I am using Cygwin and windows.. However, I still can't get your new command above to work.
Please run it again, then copy and paste the command and errors/output below.
Thanks.
Hi There.... I tried it again this time on my Macbook Pro, When I run it I get a > Prompt

richsark-Mac:PLAY-CAN-DELETE $ perl -MText::CSV -ne "BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]='10.0.1.136,10.0.1.152' if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string.\"\n\"\" dhcp-networks-Loaded.csv >outfile.csv

>
Do you want to see a Windows batch file solution?
Sure.   Please
My latest solution was Written for Windows, not Mac.

1. Please run my latest solution on Windows then paste it and the results here.

2. And if you want to run one on Mac, try my 1st solution, but I have no experience with Perl on Mac.

3. My 1st solution might also run on Cygwin, since it is similar to UNIX.
Hi. I don't seem to see your solution
Scroll up.
- My "1st solution" is the one at the top of this post: 38581896
- My "latest solution" is the one in this post: 38582316
Hi Tel2,

I still cant get it to work.  I get the below output

perl -MText::CSV -ne 'BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]="10.0.1.136,10.0.1.152" if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string."\n"' dhcp-networks.csv > outfile.csv 

Open in new window


Can't find string terminator "'" anywhere before EOF at -e line 1.

I did attach a few posts ago the master csv file.
I will not be able to look at this until later tonight.
Hi richsark,

Please always specify which environment you're running it in, as you seem to have 3 environments (Windows command line, Windows Cygwin and Mac) and I can't tell which in this case, as I don't see a command prompt.

Thanks.
Understood
Good, so what's the answer in the case of your last attempt with my code (post 38588459)?

(Make sure you're running solutions which are appropriate for the environment, as detailed in my post 38585267.  I suggest you try options 1 & 3 in that post, and then post the results here.)
How'd you get on with this, richsark?
Nope.  I can't none of them to work on any os. Not sure if anyone has been able to test the actual spreadsheet I attached with windows os or Cygwin
Hi richsark,

In general, if you want people to fix your problems, simply telling them that their solutions don't work will not be helpful.  In my very first post I said:
  "If my code doesn't work, please explain how and provide the sample output you require."
Since then, you've basically said it doesn't work twice (without providing any details of how it didn't work - see your posts 38582579 & 38596564).
If you don't give us the details (errors/output you're getting), it's like searching for a needle in a hay stack, which is a waste of time for everyone (including you).  Please remember this for all your future EE posts, as it will help you to get solutions faster, and experts will be less likely to give up early.  We're volunteers trying to help you out, so please help us to help you.  I've already spent over 2 hours on this, but I'm not planning to spend much more time, if you're not going to make it easy for me to do so.  If we ask questions, then please make sure you answer all of them.
Have a look at my post 38589449 above.  Read it carefully again, please, and then respond accordingly.

The contents of the master file you attached to post 38582267 looks like expected output, rather than input, to me, as the 15th column (column O) is either blank or already contains "10.0.1.136,10.0.1.152", so using it as input data will give the same file as output.  Is that input or expected output?  If it's expected output, where's the associated input data?  Having expected output without the input data that came from is not helpful.

I seem to have an extra '\' near the end of in my Windows solution (sorry), so please use the following adjusted code for your Windows test:
perl -MText::CSV -ne "BEGIN{$c=Text::CSV->new({quote_space=>0})};$c->parse($_);@f=$c->fields;$f[14]='10.0.1.136,10.0.1.152' if $f[14]=~/^[\d.,]+$/;$c->combine(@f);print $c->string.\"\n\"" dhcp-networks-Loaded.csv >outfile.csv

Open in new window

For my solutions, could you please now give me:
1. The command you ran.
2. The operating system you ran it on.
3. The output/errors you got.
Do that for each system you ran it on (but don't bother with Mac at this stage).

Thanks.
Was away since I last commented, rebuilding my computer.

Will revisit this question tomorrow
Just finished testing the batch file version however, if all is well then there's not much point posting it here.

Apologies for the massive delay.
Hi guys,  thanks so much for all your efforts with this. I really appreciate it.  

I am in process to test one more time with the above and revert.

Again , thanks for all your time on this
Hi again richsark,

Apart from my solutions, which I'll be interested to see the results of, have you tried what arober11 asked you to do in post 38582499 yet?  What happened (i.e. as usual, show us the command, the environment, and the errors/output)?
If you confused as to where to change the file-name then the following ma help:

cat  dhcp-networks-Loaded.csv  | sed -e 's/\(,"[^,]*\),\([^,]*\),\([^,]*"\)/\1~\2~\3/g' -e 's/\(,"[^,]*\),\([^,]*"\)/\1~\2/g' | awk  -F, -v OFS=, '{if ($15 ~ /[0-9., "]+/) {$15="\"10.0.1.136,200.200.200.200\""}; print}'

Open in new window