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
8
  • 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, 2018 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 Sep 17, 2018 at 19:21
  • 1
    You are using is RSwhich is the record seperator. What you want is FS which is the field separator. Sep 17, 2018 at 19:21
  • @thinkingsavvy, thanks! please try the commands below and let us know!
    – user88036
    Sep 17, 2018 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?.
    – user232326
    Sep 17, 2018 at 19:41

3 Answers 3

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
1
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
$

You must log in to answer this question.

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