1

I would like to add a new column to the end of a csv file and populating the column with values. I have used the following codes and it ends up showing new blank lines in between each record. Please let me know how to avoid these newly added blank lines.

awk -F "," 'NR == 1 {$5="MonthYear"}{ if (NR>1){split($2,a,"[/ ]");$5=a[1]"/"a[3]}}1' RS='\r' OFS="," Test.csv > Test1.csv

tried with both RS='\n' and RS='\r\n' and still get the same result

Input file - Test.csv

Id  Day UserId  ItemId              
1   12/1/17 0:03    2323    tv              
2   12/14/17 7:10   4546    frr             
3   1/22/18 14:11   2421    fdf             
4   2/16/18 13:36   4545    dfdf                
5   3/5/18 10:47    1232    dfsdf   

Actual output file - Test1.csv

Id  Day UserId  ItemId  MonthYear

1   12/1/17 0:03    2323    tv  12/17

2   12/14/17 7:10   4546    frr 12/17

3   1/22/18 14:11   2421    fdf 1/18

4   2/16/18 13:36   4545    dfdf    2/18

5   3/5/18 10:47    1232    dfsdf   3/18

Expected Output - csv

Id  Day UserId  ItemId  MonthYear       
1   12/1/17 0:03    2323    tv  12/17       
2   12/14/17 7:10   4546    frr 12/17       
3   1/22/18 14:11   2421    fdf 1/18        
4   2/16/18 13:36   4545    dfdf    2/18        
5   3/5/18 10:47    1232    dfsdf   3/18

Without RS, the output is jumbled up like this:

,MonthYearrId,ItemId
,12/17/17 0:03,2323,tv
,12/174/17 7:10,4546,frr
,1/182/18 14:11,2421,fdf
,2/186/18 13:36,4545,dfdf
5,3/5/18 10:47,1232,dfsdf,3/18
  • 1
    Hi @thinkingsavvy. Would you please double check your command. The output is different than the output "Actual output file - Test1.csv" that you had provided in the question? – user88036 Sep 17 '18 at 19:17
  • Hi @Goro. You are correct! I copied the output from excel. The output using cat is. Id,Day,UserId,ItemId,MonthYear 1,12/1/17 0:03,2323,tv,12/17 2,12/14/17 7:10,4546,frr,12/17 3,1/22/18 14:11,2421,fdf,1/18 4,2/16/18 13:36,4545,dfdf,2/18 5,3/5/18 10:47,1232,dfsdf,3/18 – thinkingsavvy Sep 17 '18 at 19:21
  • 1
    You are using is RSwhich is the record seperator. What you want is FS which is the field separator. – Nasir Riley Sep 17 '18 at 19:21
  • @thinkingsavvy, thanks! please try the commands below and let us know! – user88036 Sep 17 '18 at 19:29
  • Your comment claims that the field separator is a semicolon (;) but the file contents posted in the question use a tab(?) or spaces(?) as field separator. Which is the correct one?. – Isaac Sep 17 '18 at 19:41
0

The only way in which I can reproduce your problem is if the file actualy contains semicolons as delimiters and each line ends in a \r\n pair. When that happens, using the \r as the RS causes the \n to be on the next line.

Source file used:

Id,Day,UserId,ItemId
1,12/1/17 0:03,2323,tv
2,12/14/17 7:10,4546,frr
3,1/22/18 14:11,2421,fdf
4,2/16/18 13:36,4545,dfdf
5,3/5/18 10:47,1232,dfsdf

The simplest solution is to remove the \r in the file (maybe using dos2unix).
Easier to do so inside awk:

$ awk '{gsub(/\r/,"")}; NR==1 {print $0,"MonthYear"}; NR>1 {print $0,$2"/"$4}' FS="[,/ ]" OFS=, Test.csv
Id,Day,UserId,ItemId,MonthYear
1,12/1/17 0:03,2323,tv,12/17
2,12/14/17 7:10,4546,frr,12/17
3,1/22/18 14:11,2421,fdf,1/18
4,2/16/18 13:36,4545,dfdf,2/18
5,3/5/18 10:47,1232,dfsdf,3/18
0

I was able to solve this by removing the blank records from the outputfile using sed '/^ *$/d' Test1.csv > Test2.csv

0

Another awk approach

$ awk '{print $0,NR==1?" MonthYear":substr($2,0,index($2,"/"))substr($2,length($2)-1)}' Test.csv
Id  Day UserId  ItemId  MonthYear
1   12/1/17 0:03    2323    tv 12/17
2   12/14/17 7:10   4546    frr 12/17
3   1/22/18 14:11   2421    fdf 1/18
4   2/16/18 13:36   4545    dfdf 2/18
5   3/5/18 10:47    1232    dfsdf 3/18
$

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.