awk print columns and variable

Hi,

Can anyone help with the below please?

I have written some code which takes an input file, and and prints the contents out to a new file - it then loops round and prints the same columns, but increments the ID column by 1 each time.

Input file;

NAME,1,15-Dec-15,
NAME,1,21-Dec-15,
NAME,1,31-Jan-16,

Expected output;

NAME,1,15-Dec-15,
NAME,1,21-Dec-15,
NAME,1,31-Jan-16,
NAME,2,15-Dec-15,
NAME,2,21-Dec-15,
NAME,2,31-Jan-16,
NAME,3,15-Dec-15,
NAME,3,21-Dec-15,
NAME,3,31-Jan-16,

VAR_ID=1
touch newfile.csv
while [ $VAR_ID -le 3 ]
do
        while read line
        do
        echo $line | awk -v var="$VAR_ID" -F "," 'BEGIN {print $1 var $3}' >> newfile.csv
        done < inputfile.csv

VAR_ID=`expr $VAR_ID + 1`
done

When i run currently, it just prints out the incremental numbers i.e.

1
1
1
2
2
2
3
3
3

Can anyone help please?

Hello Ads89,

Welcome to forums, thank you for using code tags around some of codes you have shown in your post, request you to please use code tags for all commands/codes/Inputs shown in your posts as per forum rules. Following may help you in same.

awk '{n=NR;A[++i]=$0} END{for(i=1;i<=n;i++){for(j=1;j<=n;j++){num=split(A[j], array,",");for(u=1;u<=num;u++){array[2]=i;W=W?W OFS array:array};print W ;W=""}}}' OFS=,   Input_file

Output will be as follows.

NAME,1,15-Dec-15,
NAME,1,21-Dec-15,
NAME,1,31-Jan-16,
NAME,2,15-Dec-15,
NAME,2,21-Dec-15,
NAME,2,31-Jan-16,
NAME,3,15-Dec-15,
NAME,3,21-Dec-15,
NAME,3,31-Jan-16,
 

Thanks,
R. Singh

That is great thank you!

In your example when I run, It returned ID values up to #10 - If I wanted to change that to go up to 1000 which bit within that code would need to change?

Why that cumbersome shell script, in which you're using awk anyhow, if all can be done in awk alone? Try

awk -F, '{for (i=1; i<4; i++) print $1, i, $3, _}' OFS=, file
NAME,1,15-Dec-15,
NAME,2,15-Dec-15,
NAME,3,15-Dec-15,
NAME,1,21-Dec-15,
NAME,2,21-Dec-15,
NAME,3,21-Dec-15,
NAME,1,31-Jan-16,
NAME,2,31-Jan-16,
NAME,3,31-Jan-16,

Pipe through sort if need be:

| sort -t, -k2,2

Hello Ads89,

Let me explain you above code it will take number of total lines in Input_file and then will run each line of Input_file from number 1 to till number of lines by changing the second field of each line with the number. So let's say your Input_file has 10 lines so each line will be printed by having it's 2nd field from 1 to 10 where 1 will be 2nd field for all lines then 2 will ne 2nd files for all lines and so on till 10(which is total number of lines.). If you have any other thing to perform, request you to let us know complete details with expected output and sample input.

Thanks,
R. Singh

Would this help?

awk -F, '{for (i=1; i<=CNT; i++) print $1, i, $3, _}' OFS=, CNT=10 file | sort -t, -k2,2n
NAME,1,15-Dec-15,
NAME,1,21-Dec-15,
NAME,1,31-Jan-16,
NAME,2,15-Dec-15,
NAME,2,21-Dec-15,
NAME,2,31-Jan-16,
NAME,3,15-Dec-15,
NAME,3,21-Dec-15,
NAME,3,31-Jan-16,
NAME,4,15-Dec-15,
NAME,4,21-Dec-15,
NAME,4,31-Jan-16,
NAME,5,15-Dec-15,
NAME,5,21-Dec-15,
NAME,5,31-Jan-16,
NAME,6,15-Dec-15,
NAME,6,21-Dec-15,
NAME,6,31-Jan-16,
NAME,7,15-Dec-15,
NAME,7,21-Dec-15,
NAME,7,31-Jan-16,
NAME,8,15-Dec-15,
NAME,8,21-Dec-15,
NAME,8,31-Jan-16,
NAME,9,15-Dec-15,
NAME,9,21-Dec-15,
NAME,9,31-Jan-16,
NAME,10,15-Dec-15,
NAME,10,21-Dec-15,
NAME,10,31-Jan-16,

RudiC,

That solution works perfect - I also have a file that need to same sort of treatment

NAME,1,15-Dec-15,"13,420.58",P,"110,264.04",USD
NAME,1,21-Dec-15,862.2760926,I,"110,264.04",USD
NAME,1,31-Jan-16,658.1671127,I,"96,764.06",USD

I tried to change the number of columns passed

awk -F, '{for (i=1; i<=CNT; i++) print $1, i, $3, $4, $5, $6, $7 _}' OFS=, CNT=3 TestCashflow.csv | sort -t, -k2,2n >> test.csv

I think it is treated the commas in the values as seperate columns - is there any way to resolve this?

There's a lot of threads in these forums dealing with exactly this problem. Try searching these forums, and come back with any problems that might arise.

If field two in your input file is always 1 , the following might be simpler:

awk '{for(i=1; i<=CNT; i++){print; sub("," i ",", "," i+1 ",")}}' CNT=3 TestCashflow.csv | sort -t, -k2,2n > test.csv

or:

awk  -F, '{for(i=1; i<=CNT; i++){$2=i; print}}' OFS=, CNT=3 TestCashflow.csv | sort -t, -k2,2n > test.csv

or, if maintaining the order of the rows in the input file is important (the example input happens to be sorted in the same sequence that the default sort utility will use for printing records with the same 2nd field; so it doesn't matter for the given example):

awk '{l[NR]=$0} END{for(i=1; i<=CNT; i++) for(j=1; j<= NR; j++){print l[j]; sub("," i ",", "," i+1 ",", l[j])}}' CNT=3 TestCashflow.csv > test.csv

Even if maintaining the input line order in the output doesn't matter, the last example above will be faster than the other two because it only needs one process to do the work instead of needing to invoke both awk and sort .

As always, if someone wants to try any of these examples on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .

Two more awks without sorting:

awk '{A[NR]=$0} END{for(i=1; i<=n; i++) for(j=1; j<=NR; j++) {$0=A[j]; $2=i; print}}' n=3 FS=, OFS=, file

And this should also work in most awks and RS= must be set equal to an arbitrary character that does not occur in the file..

awk '{for(i=1; i<=n; i++) {for(j=2; j<=NF; j+=3) $j=i; print}}' n=3 FS=, OFS=, RS=* ORS= file