Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk print columns and variable

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk print columns and variable
# 1  
Old 01-29-2016
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;

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

Expected output;

Code:
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,

Code:
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.

Code:
1
1
1
2
2
2
3
3
3

Can anyone help please?
Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments; not just code segments.
Last edited by Don Cragun; 01-29-2016 at 06:37 AM.. Reason: Add CODE tags for input and output.
# 2  
Old 01-29-2016
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.
Code:
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[u]:array[u]};print W ;W=""}}}' OFS=,   Input_file

Output will be as follows.
Code:
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
This User Gave Thanks to RavinderSingh13 For This Post:
Ads89
# 3  
Old 01-29-2016
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?
# 4  
Old 01-29-2016
Why that cumbersome shell script, in which you're using awk anyhow, if all can be done in awk alone? Try
Code:
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:
Code:
| sort -t, -k2,2

# 5  
Old 01-29-2016
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
# 6  
Old 01-29-2016
Would this help?
Code:
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,

# 7  
Old 01-29-2016
RudiC,

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

Code:
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

Code:
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?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to use "awk" to print columns from different files in separate columns?

Hi, I'm trying to copy and paste the sixth column from a bunch of files into a single file having each column pasted in separate columns (and not one after each other in just one column.) I tried this code but works only partially because it copied and pasted 50 rows of each column... (6 Replies)
Discussion started by: Frastra
6 Replies

2. Shell Programming and Scripting

awk print columns which are not null

I am working on a file with several columns as below MO_NAME,FAULT_TYPE,CLASS,CODE1,CODE2,CODE3 RXOCF-101,BTS INTERNAL,FAULT CODES CLASS 2A,53,58 RXOCF-101,BTS INTERNAL,FAULT CODES CLASS 2B,24 RXOCF-101,BTS INTERNAL,FAULT CODES CLASS 2A,33 RXOCF-101,BTS INTERNAL,FAULT CODES CLASS 2D,57 ... (12 Replies)
Discussion started by: Rizwan Rasul
12 Replies

3. Shell Programming and Scripting

Awk: is it possible to print into multiple columns?

Hi guys, I have hundreds file like this, here I only show two of them: file 1 feco4_s_BB95.log ZE_1=-1717.5206260 feco4_t_BB95.log ZE_1=-1717.5169250 feco5_s_BB95.log ZE_1=-1830.9322060... (11 Replies)
Discussion started by: liuzhencc
11 Replies

4. UNIX for Dummies Questions & Answers

awk question - print columns with names

I found this command and would like to know what it means: gawk 'NR==1{for(i=1;i<=NF;i++)if($i~/FE/)f=i}{for(i=0;i<n;i++)printf"%s%s",i?" ":"",$f;print""}' It seems to mean if the row =1 assign i to that row, and then if FE is in the top row /column then increment and print the row. I am... (2 Replies)
Discussion started by: newbie2010
2 Replies

5. Shell Programming and Scripting

Awk print all columns in delimited file

text file example 1,222222222222,333,444444444444444 111111111,22,33333333,444 desired output 1 222222222222 333 444444444444444 111111111 22 33333333 444I have a delimeted file which I want to print in a table like format. The... (10 Replies)
Discussion started by: Calypso
10 Replies

6. Shell Programming and Scripting

Removing columns from awk '{ print $0 }'

I have a one-line command, lsusb | awk '{ $1=""; $2=""; $3=""; $4=""; $5=""; $6=""; print $0 }' It works, and gives the results I expect, I was just wondering if I am missing some easier way to nullify the first 6 column variables? Something like, lsusb | awk '{ $(1-6)=""; print $0 }' But... (10 Replies)
Discussion started by: AlphaLexman
10 Replies

7. Shell Programming and Scripting

using awk to print some columns of a file

Hi, i have a file with content 00:01:20.613 'integer32' 459254 00:01:34.158 459556 00:01:36.626 'integer32' 459255 and i want to print only output as below 00:01:20.613 459254 00:01:34.158 459556 00:01:36.626 459255 i dont want the word 'integer32' which is the second column. i... (2 Replies)
Discussion started by: dealerso
2 Replies

8. Shell Programming and Scripting

cannot print the columns i want with awk.

hi friends! i have a script where a execute a veritas command, available_media wich retrieves me a list of tapes .lst then i execute cat /tmp/listtapes.lst | grep -v VL |sed '/^$/d'|awk -F, '{print $1, $3, $4, $9} ' > /tmp/media1.lst but it prints all the columns instead of the four... (3 Replies)
Discussion started by: pabloli150
3 Replies

9. Shell Programming and Scripting

AWK script to print all the columns excpet the one specified

I have several columns by the name A B C D E...... and I want to print all the column other than column C and D. Could you please help me with the awk script? Thanks!! (3 Replies)
Discussion started by: kn.naresh
3 Replies

10. UNIX for Dummies Questions & Answers

Print last 4 columns (variable column #)

I have rows of data with variable number of columns. Is there an easy way to print out the last 4 columns or rather the 4th and 3rd last column? data looks like this: 24 20:51 N 9 10.00 Overcast OVC110 27 11 30.04 1017.7 24 19:51 N 7 10.00 Mostly Cloudy BKN110 28 15... (19 Replies)
Discussion started by: Da_Duck
19 Replies
Login or Register to Ask a Question