Sunday, October 9, 2022

[SOLVED] How to add a new column in CSV file at the end of all columns using gsub in Unix?

Issue

Original test file:

original test file

I want to add a new column (CSV filename) at the end of all columns in a CSV file using awk and gsub functions in Unix.

Input data (filename test.csv):

col1,col2,col3
ab,  cd,  ef
gh,  ij,  kl
mn,  op,qr

Output file should look like:

col1,col2,col3,test.csv
ab,cd,ef,test.csv
gh,ij,kl,test.csv
mn,op,qr,test.csv

I have tried with below code:

awk '{gsub(/ /,",",$0);print $0,",",FILENAME > "test.csv"}' test.csv

Using this, the file name is getting appended at second column instead of last column:

tested result file

It works fine with .txt files but doesn't work with .csv files; my requirement is to handle both type of file to append file name at the end.


Solution

Some demonstrated effort would have been nice, but:

awk -F, '{gsub(/ /,"");print $0","FILENAME}' test.csv 
col1,col2,col3,test.csv
ab,cd,ef,test.csv
gh,ij,kl,test.csv
mn,op,qr,test.csv

Redirect the output to a temporary file and then replace the original with it.

awk -F, '{gsub(/ /,"");print $0","FILENAME}' test.csv > tmp.csv && mv tmp.csv test.csv && cat test.csv
col1,col2,col3,test.csv
ab,cd,ef,test.csv
gh,ij,kl,test.csv
mn,op,qr,test.csv


Answered By - tink
Answer Checked By - Timothy Miller (WPSolving Admin)
at