kuruvi
Reputation: 653
swap characters using sed or awk in the first and second column of CSV
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
Upvotes: 0
Views: 63
Answers (4)
Daweo
Reputation: 36630
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)
Upvotes: 0
Ed Morton
Reputation: 204035
$ 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
Upvotes: 2
choroba
Reputation: 241998
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
Upvotes: 2
RavinderSingh13
Reputation: 133640
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.
Upvotes: 2
Related Questions
- Combining first x number of characters in column 1 of csv to all of column 2
- How to replace multiple characters with AWK in a specific column of a csv file?
- Switching columns of a csv-file with awk
- Code swap. How would I swap the value of one CSV file column to another?
- Replace character in one column of CSV file with awk gsub
- Replace characters in specific columns only (CSV)
- How do I swap the last column in a csv file to be the first column? + awk
- Replace string in multiple columns in a csv
- Replacing a column in CSV file with another in bash
- swapping characters in 3rd column using sed/awk