
Base Dataset:
"X1","Y1","Z1","Parmesan Cheese","Yummy","Delicious"
"X1","Y1","Z1","Grilled Salmon","Amazing","Tender"
"X1","Y1","Z1","Filet Mignon","Juicy","Exquisite"
"X2","Y2","Z2","Parmesan Cheese","Yummy","Delicious"
"X2","Y2","Z2","Grilled Salmon","Amazing","Tender"
"X2","Y2","Z2","Filet Mignon","Juicy","Exquisite"
Required Dataset:
"X1","Y1","Z1","Grilled Salmon","Amazing","Tender"
"X1","Y1","Z1","Filet Mignon","Juicy","Exquisite"
"X1","Y1","Z1","Parmesan Cheese","Yummy","Delicious"
"X2","Y2","Z2","Grilled Salmon","Amazing","Tender"
"X2","Y2","Z2","Filet Mignon","Juicy","Exquisite"
"X2","Y2","Z2","Parmesan Cheese","Yummy","Delicious"
lookup.txt
"X1","Y1","Z1","Parmesan Cheese","Yummy","Delicious"
"X2","Y2","Z2","Parmesan Cheese","Yummy","Delicious"
In summary, for each record being read in getEntireRecord from this lookup file, it will take the getUniqueRecIdentifier ("X1","Y1","Z1") and find the line number lineNumOfFiletMignon (3) of the "Filet Mignon" record having same identifier ("X1","Y1","Z1"). Now we know where to insert the "Parmesan Cheese" record getEntireRecord - the line number will be lineNumToInsertParmesanCheese which is the next line, so add one (3+1=4).
Full logic:
filename="file.txt"
grep 'Parmesan Cheese' $filename > lookup.txt
lkpfilename="lookup.txt"
while read -r line
do
readLine=$line
getUniqueRecIdentifier="$(cut -c1-8 <<<"$readLine")"
getEntireRecord="$(cut -c1-100 <<<"$readLine")"
generateSameIdFiletMignon=$getUniqueRecIdentifier""',"Filet Mignon"'""
lineNumOfFiletMignon="$(grep -n "$generateSameIdFiletMignon" $filename | head -n 1 | cut -d: -f1)"
lineNumToInsertParmesanCheese=$((lineNumOfFiletMignon + 1))
sed -i ''"$lineNumToInsertParmesanCheese"'i '"$getEntireRecord"'' file.txt
lineNumToBeDeleted="$(grep -n "$getEntireRecord" $filename | head -n 1 | cut -d: -f1)"
sed -i ''"$lineNumToBeDeleted"'d' file.txt
done < "$lkpfilename"
rm $lkpfilename
Detailed Explanation: To identify lineNumOfFiletMignon we are using grep -n as seen below, with head -n 1 to get the first record for the specific combination, even though we know it will give only one record in our case. Then we have cut -d: -f1 to get the first column as the Unix line number.
lineNumOfFiletMignon="$(grep -n "$generateSameIdFiletMignon" $filename | head -n 1 | cut -d: -f1)"
lineNumToInsertParmesanCheese=$((lineNumOfFiletMignon + 1))
sed -i ''"$lineNumToInsertParmesanCheese"'i '"$getEntireRecord"'' file.txt
After we do the above, we are going to have a duplicate original record of "Parmesan Cheese" that has to be deleted, this is calculated in lineNumToBeDeleted by using the entire record string getEntireRecord which was retrieved from lookup.txt.
lineNumToBeDeleted="$(grep -n "$getEntireRecord" $filename | head -n 1 | cut -d: -f1)"
The duplicate original line will be removed by the below sed -i command where we are providing the line number lineNumToBeDeleted to be deleted with d at the end for deletion, followed by the file name.
sed -i ''"$lineNumToBeDeleted"'d' file.txt
Then at the end we can safely delete our lookup file, which was happily storing all the "Parmesan Cheese" for us until now!
The above activity can also be done in Excel macro, but considering the amount of maintenance and scalability factors, we are clear which option to choose now.
Excellent !
ReplyDeleteRahul Bhattacharya !!!
Do you mind if I quote a couple of your posts as long as I
ReplyDeleteprovide credit and sources back to your blog? My blog site is in the very same niche
as yours and my visitors would definitely benefit from some of the information you present
here. Please let me know if this ok with you. Thanks
a lot!
I could not refrain from commenting. Exceptionally well written!
ReplyDeleteKeep on writing, great job!
ReplyDeletePretty! This has been an incredibly wonderful article. Many thanks for providing this information.
ReplyDelete