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
Improve this question
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? Commented 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 Commented 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. Commented Sep 17, 2018 at 19:21
  • @thinkingsavvy, thanks! please try the commands below and let us know! Commented 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?. Commented Sep 17, 2018 at 19:41

3 Answers 3

Reset to default
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
Improve this answer
1
0

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

Improve this answer
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
$
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.