prefix every header column with string using awk

Viewed 178

I have a bunch of big csv I want to prefix every header column with fixed string. There is more than 500 columns in every file.

suppose my header is:

number;date;customer;key;amount

I tried this awk line:

awk -F';' 'NR==1{gsub(/[^a-z_]/,"input_file.")} { print }'

but I get (note fist column is missing prefix and separator is removed):

numberinput_file.dateinput_file.customerinput_file.keyinput_file.amount

expected output:

input_file.number;input_file.date;input_file.customer;input_file.key;input_file.amount
4 Answers

In any awk that'd be:

$ awk 'NR==1{gsub(/^|;/,"&input_file.")} 1' file
input_file.number;input_file.date;input_file.customer;input_file.key;input_file.amount

but sed exists to do simple substitutions like that, e.g. using a sed that has -E to enable EREs (e.g. GNU and BSD sed):

$ sed -E '1s/^|;/&input_file./g' file
input_file.number;input_file.date;input_file.customer;input_file.key;input_file.amount

If you're using GNU tools then you could use either of the above to change all of your CSV files at once with either of these:

awk -i inplace 'NR==1{gsub(/^|;/,"&input_file.")} 1' *.csv
sed -i -E '1s/^|;/&input_file./g' *.csv

Your gsub would brutally replace any nonalphabetic character anywhere in the input with the prefix - including your column separators.

The print can be abbreviated to the common idiom 1 at the very end of your script; this simply means "this condition is true; perform the default action for every line (i.e. print it all)" though this is just a stylistic change.

awk -F';' 'NR==1{
    sub(/^/, "input_file."); gsub(/;/, ";input_file."); }
  1' filename

If you want to perform this on multiple files, probably put a shell loop around it. If you only want to concatenate everything to standard output, you can give all the files to Awk in one go (in which case you probably don't want to print the header line for any file after the first; maybe change the 1 to NR==1 || FNR != 1).

I would use GNU AWK following way, let file.txt content be

number;date;customer;key;amount
1;2;3;4;5
6;7;8;9;10

then

awk 'BEGIN{FS=";";OFS=";input_file."}NR==1{$1="input_file." $1}{print}' file.txt

output

input_file.number;input_file.date;input_file.customer;input_file.key;input_file.amount
1;2;3;4;5
6;7;8;9;10

Explanation: I set OFS to ; followed by prefix. Then in first line I add prefix to first column, which trigger string rebuilding. No modification is done in any other line, thus they are printed as is.

(tested in GNU Awk 5.0.1)

Also with awk using for loop and printf:

awk 'BEGIN{FS=OFS=";"} NR==1{for (i=1; i<=NF; i++) printf "%s%s", "input_file." $i, (i<NF ? OFS : ORS)}' file
input_file.number;input_file.date;input_file.customer;input_file.key;input_file.amount
Related