0

I have to move the unique key which is in 3rd column of each record to first column. Now based on this key each record has different total columns in it (means # of fields)

File content is

10,,FH,1834,1010 (newline) 
11,10,BH,9899,1010 (newline)
 21,11,TH,1010,345 (newline)
 22,11,DA,34.65 (newline)
 23,11,DA,76.89 (newline)
 24,11,CC,1010 (newline)
 25,11,CC,1011 (newline) 
13,10,FT,200.68 (newline)

Note: add (newline) as somehow when i was pasting the file all the records were coming in same line.

I wrote below awk logic

awk -F',' -v OFS=, '{printf "%s" ,$3 OFS; for(i=1;i<=NF;i++) if(i!=3) printf "%s",$i OFS;printf ORS}' test1.csv

getting this output

,H,10,,1834,1010                                                                                                   
,H,11,10,9899,1010                                                                                                 
,H,21,11,1010,345KW                                                                                                
,U,22,11,34.65                                                                                                     
,U,23,11,76.89                                                                                                     
,H,24,11,1010                                                                                                      
,H,25,11,1011                                                                                                      
,T,13,10,200.68 

Desired output is this

FH,10,,1834,1010 (newline)
BH,11,10,9899,1010 (newline)
TH,21,11,1010,345  (newline)
..... ...

Issue is with my command, one character of 3rd field is getting replaced with comma.

1
  • The comma that's overwriting the first character is likely because your ORS is just a CR(CarriageReturn). Depending on your OS newline is CRLF or LF(LineFeed). Check the csv file and confirm whether it has CRLF or LF.
    – al76
    Commented May 14, 2019 at 2:23

2 Answers 2

1
awk -F',' '{print $3 "," $1 "," $2 "," $4 "," $5}' test1.csv

output is

FH,10,,1834,1010
BH,11,10,9899,1010
TH, 21,11,1010,345 
DA, 22,11,34.65 ,
DA, 23,11,76.89 ,
CC, 24,11,1010 ,
CC, 25,11,1011  ,
FT,13,10,200.68 ,

This variation on your script might shed some light on your end-of-line issue

awk -F',' -v OFS=,  '{printf "%s" ,$3 OFS; for(i=1;i<=NF;i++) if(i!=3) printf "%s",$i OFS;print ""}' test1.csv

output is

FH,10,,1834,1010,
BH,11,10,9899,1010,
TH, 21,11,1010,345 ,
DA, 22,11,34.65 ,
DA, 23,11,76.89 ,
CC, 24,11,1010 ,
CC, 25,11,1011  ,
FT,13,10,200.68 ,
0

This is simpler, and it will handle lines that have arbitrary numbers of fields without introducing extra commas at the end of the line. It'll also handle lines that have fewer than 3 fields.

    awk -F, -v OFS=, 'NF > 2 { k=$3; $3=$2; $2=$1; $1=k } { print }' test.csv

Rearranging the fields of the input makes generating the output easy, it's just print.

If there were more values that needed to be shifted (say, the key was the 10th field) then using a for loop to do that would be worthwhile, but for shifting only two fields it seems like overkill.

1
  • Thanks. This actually resolves the issue and i am getting the desired result. I executed my script in different servers and due to the extra comma at end, i was having issue. Your script resolves this.
    – PI2004
    Commented May 14, 2019 at 4:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .