Issue
While appending 2 csv files with unequal number of rows , we need 'NULL' value in place of missing lines in csv.
in the below mentioned images
First file is having 5 rows while second is having 4 , so we need NULL in the last line of second CSV for all the columns. 3rd image is the expected final file.
we need command to add NULL in the missing cells
Solution
paste
should help here along with awk
to complete the file merge.
Firstly, let's determine the number of fields. Then, using paste
, we merge both files line by line separated by ','. This output is then piped to awk
. If it sees a line less than the expected field count, adds string "NULL" for the appropriate count. Otherwise, it is printed as is. Assuming files are file1.csv and file2.csv, here is the full code:
NUM_FIELDS=$(paste -d"," file1.csv file2.csv | head -1 | awk -F, '{print NF}')
paste -d"," file1.csv file2.csv | awk -F, -v cnt="$NUM_FIELDS" 'NF < cnt {
printf "%s",$0;
for (i=NF;i<cnt;i++)
printf "NULL,";
print "NULL";
next}
{print}'
Here is the output:
ProductCc,SITE,BatchID,PROCESS_CATEGORY,DO actual,Runtime,Tank Volume,Open pipe
MK3,Biberach,15300289,BiologicsUpstream,60.62,14.396,12460.1,0
MK3,Biberach,15300289,BiologicsUpstream,59.33,14.403,12462.7,0
MK3,Biberach,15300289,BiologicsUpstream,60.68,14,41,12457.3,0
MK3,Biberach,15300289,BiologicsUpstream,59.99,14.417,12453.3,0
MK3,Biberach,15300289,BiologicsUpstream,NULL,NULL,NULL,NULL
p/s: In future, please include any sample data lines as text instead of images to simplify testing.
Answered By - Heelara Answer Checked By - Mildred Charles (WPSolving Admin)