swap characters using sed or awk in the first and second column of CSV

kuruvi 0
Viewed 51

I have the following CSV file (it is comma separated file)

01/04/2022,02/04/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
02/04/2022,02/04/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
03/04/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
05/04/2022,05/04/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
06/04/2022,06/04/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08

I have to swap the days and months in the first and second column. I can use sed command. Using comma , instead of / to abstain escaping

$ sed -E 's,^([0-9]+)/([0-9]+),\2/\1,'

$ echo  01/04/2022 | cut -d, -f2 | sed -E 's,^([0-9]+)/([0-9]+),\2/\1,'
04/01/2022

However, I couldn't incorporate sed with CSV file, how can I do this? The resultant data must look like this

04/01/2022,04/02/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/02/2022,04/02/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/03/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/05/2022,04/05/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/06/2022,04/06/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
awk sed csv
4 Answers

With your shown samples please try following awk code.

awk '
BEGIN{ FS=OFS="," }
{
  split($1,arr1,"/")
  split($2,arr2,"/")
  $1=sprintf("%01d/%01d/%02d",arr1[2],arr1[1],arr1[3])
  $2=sprintf("%01d/%01d/%d",arr2[2],arr2[1],arr2[3])
}
1
'  Input_file

Explanation: Simple explanation would be; setting FS and OFS to , in BEGIN section of this program. Using split function to split 1st and 2nd fields by / delimiter and placing all values into arrays named arr1 and arr2. Then re-assigning values to $1 and $2 respectively with use of sprintf where I am re-arranging the values as per requirement.

RavinderSingh130 
$ awk '
    BEGIN { FS=OFS="," }
    {
        for ( i=1; i<=2; i++ ) {
            split($i,d,"/")
            $i = d[2] "/" d[1] "/" d[3]
        }
        print
    }
' file
04/01/2022,04/02/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/02/2022,04/02/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/03/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/05/2022,04/05/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/06/2022,04/06/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
Ed Morton0 
sed -E 's=^([0-9]+)/([0-9]+)=\2/\1=;s=,([0-9]+)/([0-9]+)=,\2/\1=' file.csv

It works for CSV files, i.e. the values must be separated by commas, not spaces as in your example. Switching to spaces should be easy, though.

Tested on:

01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
01/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
02/04/2022,2/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
03/04/2022,4/4/22,UPI/3432/Upi,Transaction,0,400,114362.08
04/04/2022,4/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
05/04/2022,5/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
06/04/2022,6/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
08/04/2022,8/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
09/04/2022,11/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08

Output:

04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/01/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/02/2022,4/2/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/03/2022,4/4/22,UPI/3432/Upi,Transaction,0,400,114362.08
04/04/2022,4/4/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/05/2022,4/5/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/06/2022,4/6/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/08/2022,4/8/22,UPI/3432/Upi,Transaction,5998,0,114362.08
04/09/2022,4/11/22,UPI/3432/Upi,Transaction,5998,0,114362.08
choroba0

I would exploit GNU AWK's gensub function following way, let file.csv content be

01/04/2022,02/04/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
01/04/2022,02/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
02/04/2022,02/04/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
03/04/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
05/04/2022,05/04/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
06/04/2022,06/04/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08

then

awk 'BEGIN{FS=OFS=","}{$1=gensub(/(..)\/(..)/, "\\2/\\1",1,$1);$2=gensub(/(..)\/(..)/, "\\2/\\1",1,$2);print}' file.csv

gives output

04/01/2022,04/02/2022,UPI/45/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/45435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/01/2022,04/02/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/02/2022,04/02/2022,UPI/4545/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/03/2022,04/04/2022,UPI/234234325435/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/04/2022,04/04/2022,UPI/67657/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/05/2022,04/05/2022,UPI/787/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08
04/06/2022,04/06/2022,UPI/7878/Upi Transaction/dsf.payu@hdfcban/HDFC BANK LTD/HDF1EEB5FACE82A4,5998.00,0.0,114362.08

Explanation: I inform GNU AWK that , is both field separator (FS) and output field separator (OFS) then for 1st and 2nd field I use gensub with capturing groups to swap 1st and 2nd characters with 4th and 5nd charcters.

(tested in GNU Awk 5.0.1)

Daweo0
Related