1

below is my csv file,I want to remove all occurrences of - from retailer_id field and create new csv.

>IPAY_USER_ID,RETAILER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_MIDDLE_NAME,ACTIVATION_ACTOR_ID,DATE_OF_BIRTH,GENDER,EMAIL_ID,MOBILE_NO,CUSTOMER_CATEGORY,CUST_STATUS,WALLET_TYPE,MOBILE_CIRCLE,MPIN_EXPRY_DATE,R_MOD_ID,R_MOD_TIME,R_CRE_ID,CREATION_DATE,CREATION_TIME,RETAILER_UPGRADE_REG_DATE,RETAILER_UPGRADE_REG_TIME,DEDUP2_DATE,DEDUP2_TIME,DATA_ENRICHMENT_DATE,DATA_ENRICHMENT_TIME,BLACKLIST_DATE,BLACKLIST_TIME,DEDUP3_DATE,DEDUP3_TIME,KYCN_P_Registration_Mode,CHANNEL,TD_PD_STATUS,DEFAULT_MPIN_CHANGED_OR_NOT,UPGRADE_CHANNEL,UPGRADE_STATUS,LAST_TXN_DATE,KYCF_CONVERSION_DATE,KYCF_CONVERSION_TIME,NOMINEE_NAME,RELATION_CODE,BALANCE,SEEDING AUTHORISATION ID
22909943,--,RAL,WAL,,0,08/jan/1997,,RjhL@GMAIL.COM,9923,,ACTIVE,NOKYC,RJ,2025-08-27 21:19:30,22909943,2015-11-05 17:21:17,22909943,2015-08-27,21:19:30,,,,,,,2015-11-05,17:21:17,,,SELF,WEB,,-,,PENDING,2015-08-27 21:19:30,,,,,0,
  • What do you want to replace the -- with? – EightBitTony Mar 10 '16 at 12:35
4
awk -F , -v OFS=, '{gsub(/-/, "", $2); print}' < in.csv > out.csv
  • Could you please let me know why did you use -v option in awk for this requirement ? – siddhartha jain Mar 10 '16 at 15:19
  • @siddharthajain, -v is to pass a variable definition, here of the Output Field Separator special variable. -F , is short for -v FS=,. Check the awk man page for details. – Stéphane Chazelas Mar 10 '16 at 15:22
  • to add to SC's reply, the specific reason why it is necessary to set the OFS is that if you don't set it to , then any lines that were changed by the gsub() function will have the OFS changed to awk's default (a single space) so it will no longer be a CSV file. Unchanged lines will retain their original field separator (a comma) – cas Mar 11 '16 at 2:48
  • BTW, this is not specific to gsub() - it will happen to any output line modified by awk. – cas Mar 11 '16 at 2:52
  • @StéphaneChazelas Thanks,But what if i want to remove character '-' from multiple and specific fields. – siddhartha jain Mar 11 '16 at 5:58
0
sed -i 's/--//g'  in.cvs > out.cvs
  • 2
    Why -i and redirect? – Jeff Schaller Mar 10 '16 at 12:46
  • Although the original question isn't clear if -- appears anywhere else, that solution risks replacing every occurrence of -- regardless of where it resides in the file. It would be a little safer to replace ,--, with ,, but even that is risky if it can appear in another field and still be required. – EightBitTony Mar 10 '16 at 12:47
0

I would use sed for that.

$ sed -r -i 's/^([0-9]+,)--,/\1,/g' file.csv

However, I liked the Stéphane's answer. If the RETAILER_ID field was the tenth field, for example, regular expressions to sed would be uglier.

0

I'd probably do it in perl, because it allows you to be selective about named fields:

#!/usr/bin/env perl
use strict;
use warnings;

#read header row from "STDIN" (or file on command line); 
chomp ( my @header = split /,/, <> );
#print it
print join ",", @header, "\n";
#iterate STDIN or file on command line - line by line
while ( <> ) {
    #declare a row
    my %this_row;
    #strip trailing linefeed (optional, given you need to reinsert it)
    chomp;
    #select fields in this row, into named fields based on the header row. 
    @this_row{@header} = split /,/;
    #apply regex to just RETAILER_ID
    $this_row{'RETAILER_ID'} =~ s/--//;
    #print row. map is unnecessary if you've always got a full set of fields.  
    #I've included it because your sample data didn't. 
    print join ( "," , map { $_ // '' } @this_row{@header} ),"\n";
}

It's a bit longer than a sed one liner - but you could one-line-ify this if you wanted.

Because this script uses <> - that's the magic file handle, that reads either STDIN or a file specified on command line, just like grep, sed etc. would. That means though, you can do perl -i and inplace edit if that's your goal. Or just redirect the output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.