Aidan Aidan - 26 days ago 5
Bash Question

Fill Column in CSV with previous value using shell

I have the following File that can be generated like the below by pulling information from a webpage, however as you can see it is missing Dates on Rows 4,5,7 & 8:

1,Nov 09 2016,Pakistan,Karachi Stock Exchange,Iqbal Day
2,Nov 11 2016,Poland,Warsaw Stock Exchange,Independence Day
3,Nov 14 2016,Colombia,Colombia Stock Exchange,Independence of Cartagena
4,,India,India National Stock Exchange,Guru Nanak Jayanti
5,,Sri Lanka,Colombo Stock Exchange,Ill Full Moon Poya Day
6,Nov 15 2016,Brazil,Sao Paulo Stock Exchange,Republic Day
7,,Palestinian Territory,Ramallah Stock Exchange,Independence Day
8,,Sri Lanka,Colombo Stock Exchange,Ill Full Moon Poya Day


What I would need to achieve is to Fill the Date from row3 into 4 and 5 and the Date from row6 into 7 & 8, this file could have more than the 8 lines above and will require all blank lines to be filled with what is in the previous cell. I've tried all different types of answers on Stackoverflow but none are able to do what I require.

Answer

Assuming that the first row is complete. (with Date value in column 2). Give this awk one-liner a try:

awk -F, -v OFS="," '{$2=$2?$2:d;d=$2}7' file

With your input, it outputs:

1,Nov 09 2016,Pakistan,Karachi Stock Exchange,Iqbal Day
2,Nov 11 2016,Poland,Warsaw Stock Exchange,Independence Day
3,Nov 14 2016,Colombia,Colombia Stock Exchange,Independence of Cartagena
4,Nov 14 2016,India,India National Stock Exchange,Guru Nanak Jayanti
5,Nov 14 2016,Sri Lanka,Colombo Stock Exchange,Ill Full Moon Poya Day
6,Nov 15 2016,Brazil,Sao Paulo Stock Exchange,Republic Day
7,Nov 15 2016,Palestinian Territory,Ramallah Stock Exchange,Independence Day
8,Nov 15 2016,Sri Lanka,Colombo Stock Exchange,Ill Full Moon Poya Day
Comments