Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert rows into columns and create table with awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert rows into columns and create table with awk
# 1  
Old 09-30-2018
Convert rows into columns and create table with awk
Hello
I've four fields . They are First Name, Last Name, Age, Country. So when I run a Unix command, I get below output with these fields comes every time in different order as you can see. Some times first name is the first row and other time last name is first row in the output and etc etc.. Now I am looking for to have a table with these 4 columns. Let's say first column is First Name, 2nd column is Last Name, 3rd is Age and 4th Column is Country. How can I able to achieve this with awk command or any other unix command? Need your help please. Thank you in advance

Current Output:
Code:
First Name: John
Last Name: Slater
Age: 31
Country: USA

Last Name: Watt
Age: 35
Country: UK
First Name: Mark

Country: Germany
First Name: Jim
Age: 50
Last Name: Peterson

Final Output should look like below table:

Code:
First Name   Last Name                          Age     Country
John                Slater                                  31         USA
Mark                Watt                                    35          UK
Jim                  Peterson                              50         Germany

Last edited by Don Cragun; 10-01-2018 at 12:24 AM.. Reason: Add missing CODE tags.
# 2  
Old 09-30-2018
Hi rprpr,

unix.com is not a place where the members do your work for you, regardless of if it is homework, personal or professional work.

We are here to help you do your work, not do your work for you.

Why would you post a "problem" which looks like a typical school question which requires an "urgent reply" saying "it must be awk or any other unix command" without any details of the computer technology available to you and what code you have written or tried so far? Urgent why? Because your teacher told you to have the homework done in two days? Urgent because why? Urgent because you took a computer admin job and have no idea how to create a basic computer program?

Have you ever written a single line of software?

We are not here to beg you to provide basic technology details and we are not here to do you work for you.

So, if you want help, you need to be very technically specific.

Thanks.
This User Gave Thanks to Neo For This Post:
shamrock
# 3  
Old 10-01-2018
Quote:
Originally Posted by Neo
Hi rprpr,

unix.com is not a place where the members do your work for you, regardless of if it is homework, personal or professional work.

We are here to help you do your work, not do your work for you.

Why would you post a "problem" which looks like a typical school question which requires an "urgent reply" saying "it must be awk or any other unix command" without any details of the computer technology available to you and what code you have written or tried so far? Urgent why? Because your teacher told you to have the homework done in two days? Urgent because why? Urgent because you took a computer admin job and have no idea how to create a basic
computer program?

Have you ever written a single line of software?

We are not here to beg you to provide basic technology details and we are not here to do you work for you.

So, if you want help, you need to be very technically specific.

Thanks.
------ Post updated at 03:06 AM ------

I apologize. the "urgent" word came out there by mistake.. i used awk command to create the table and print the values but as you can see that in original output, the fields are arrangee in different orders, hence the columns comes with different values (i.e. columns not matching with its actual fields values). It doesn't need to be awk specially, it can be any command..
# 4  
Old 10-01-2018
In addition to what Neo has already said, it isn't at all clear what you are really trying to do. Since features vary from system to system and shell to shell, you should always tell us what operating system and shell you're using when starting a thread in the Shell Programming and Scripting forum. You haven't done that.

You haven't shown us what your original input looks like. If you're trying to rewrite your awk script to produce the output you want instead of the current output you showed us in post #1 in this thread, we need to know details about your input file(s) format(s).

You haven't shown us the awk script that produces your current output. If you want us to help you fix your code, we have to be able to see your code.

If you want to keep your current awk script and write another shell, sed, awk, or some other utility or combination of utilities; we could do that, but it would be grossly inefficient and would seem to be fighting against your goal of directly producing the final output you want. Whether or not you have tried to write a script to reformat your current output and want to rewrite your script to format your output correctly to start with, we need to see what you have tried to achieve that goal so we have a better understanding of what you do understand and where you're stuck.

So, please help us help you by:
  1. telling us what operating system you're using,
  2. telling us what shell you're using,
  3. showing us the input data (in CODE tags) that was used by your awk script to produce the current output you have shown us and explain to us in English the format of that input data,
  4. showing us your awk script (in CODE tags) that processed the input data described above and produced the current output you have shown us,
  5. showing us code that you have tried (in CODE tags) to get the final output you are hoping to produce, and
  6. explaining to us where you are stuck.
This User Gave Thanks to Don Cragun For This Post:
Neo
# 5  
Old 10-01-2018
Code:
awk -v cols="First Name:Last Name:Age:Country" '
BEGIN {
  col_count=split(cols, col_arr, ":");
  for (i=1; i<=col_count; i++) printf col_arr[i] ((i==col_count) ? "\n" : "\t");
}
{
  for (i=1; i<=NF; i++) {
     sub("[ \t]*:[ \t]", ":", $i);
     split($i, value_pair, " *: *");
     slabel[value_pair[1]]=value_pair[1];
     data[value_pair[1]]=value_pair[2];
  }
  for (i=1; i<=col_count; i++) printf data[slabel[col_arr[i]]] ((i==col_count) ? "\n" : "\t");
} ' FS="\n" RS="\n\n" infile

# 6  
Old 10-02-2018
Thank you so much rdrtx1 for your help. I still see one issue when the total no. of fields are not equal (i.e.4 fields) for every section.. This is the output results I'm getting after I run command..Here you can see Last name is repeating for all 3 rows and Age 50 is also repeating in this below results
Code:
First Name   Last Name   Age   Country
John F         Slater           31    USA
Mark           Slater           35     United Kingdom
Jim             Slater           50     Germany
Mike           Crown           50     Canada

This is my original input file:
Code:
First Name: John F
Last Name: Slater
Age: 31
Country: USA

Last Name: Watt
Age: 35
Country: United Kingdom
First Name: Mark

Country: Germany
First Name: Jim
Age: 50

Country: Canada
Last Name:  Crown
First Name: Mike

Can you guys please suggest how to make this happen? Thank you

------ Post updated at 02:21 AM ------

I was running this below awk command initially in the first place...But this is not working either..

Code:
cat infile.txt | awk '
                        BEGIN {printf("FIRSTNAME LASTNAME AGE COUNTRY\n")
                                        }
                        /First Name:/ {F=$3$4}
                        /Last Name:/ {L=$3}
	        /Age:/ {A=$2}
                        /Country/ {print F, L, A, $2$3}' | column -t


=======================

The output results I get by running above awk command. How can I fix this? Please help. Thanks

Code:
FirstName LastName Age Country
JohnF Slater      USA
JohnF Slater   United Kingdom
Mark Slater  Germany
Jim Slater Canada

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments (as required by forum rules).
Last edited by Don Cragun; 10-03-2018 at 02:57 AM.. Reason: Add missing CODE tags again.
# 7  
Old 10-03-2018
Quote:
Originally Posted by rprpr
Thank you so much rdrtx1 for your help. I still see one issue when the total no. of fields are not equal (i.e.4 fields) for every section.. This is the output results I'm getting after I run command..Here you can see Last name is repeating for all 3 rows and Age 50 is also repeating in this below results
Code:
First Name   Last Name   Age   Country
John F         Slater           31    USA
Mark           Slater           35     United Kingdom
Jim             Slater           50     Germany
Mike           Crown           50     Canada

This is my original input file:
Code:
First Name: John F
Last Name: Slater
Age: 31
Country: USA

Last Name: Watt
Age: 35
Country: United Kingdom
First Name: Mark

Country: Germany
First Name: Jim
Age: 50

Country: Canada
Last Name:  Crown
First Name: Mike

Can you guys please suggest how to make this happen? Thank you

------ Post updated at 02:21 AM ------

I was running this below awk command initially in the first place...But this is not working either..

Code:
cat infile.txt | awk '
                        BEGIN {printf("FIRSTNAME LASTNAME AGE COUNTRY\n")
                                        }
                        /First Name:/ {F=$3$4}
                        /Last Name:/ {L=$3}
	        /Age:/ {A=$2}
                        /Country/ {print F, L, A, $2$3}' | column -t


=======================

The output results I get by running above awk command. How can I fix this? Please help. Thanks

Code:
FirstName LastName Age Country
JohnF Slater      USA
JohnF Slater   United Kingdom
Mark Slater  Germany
Jim Slater Canada

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments (as required by forum rules).
If you want our help, you would do well to provide the information we need to help you (such as was requested two days ago in post #4 in this thread. Since you have chosen to withhold much of the information requested, I will show you code that works with the ksh, awk, and column that are available on macOS High Sierra version 10.13.6. If there are any changes needed to work in your environment, it will be up to you to figure out what changes are needed.

Note that I added the "none given" as data to fill fields that contained no data in an input record because the version of column on my system doesn't seem to recognize two adjacent field separators as delimiters for an empty field.

I have no idea where the Last Name "Peterson" that you said should appear in the last line of your output came from. That name does not appear anywhere in any of the sample input files you have shown us. And, since there were four records in your sample input, I do not understand why your desired sample output only contained three records (not counting the header record).

On my system, when file contains the sample input you provided in post #6, the following code:
Code:
#!/bin/ksh
awk '
BEGIN {	FS = ": |\n"
	OFS = ":"
	RS = ""
	print FNn = "First Name", LNn = "Last Name", An = "Age", Cn = "Country"
}
{	A = C = F = L = "none given"
	for(i = 1; i <= NF; i += 2) {
		if($i == An)
			A = $(i + 1)
		else if($i == Cn)
			C = $(i + 1)
		else if($i == FNn)
			F = $(i + 1)
		else if($i == LNn)
			L = $(i + 1)
	}
	print F, L, A, C
}' file | column -s: -t

produces the output:
Code:
First Name  Last Name   Age         Country
John F      Slater      31          USA
Mark        Watt        35          United Kingdom
Jim         none given  50          Germany
Mike         Crown      none given  Canada

Note that the last name "Crown" in the last output record is misaligned because there is an extraneous <space> character before "Crown" in your sample input file.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert rows to columns

hi folks, I have a sample data like what is shown below: 1,ID=1000 1,Org=CedarparkHospital 1,cn=john 1,sn=doe 1,uid=User001 2,uid=User002 2,ID=2000 2,cn=steve 2,sn=jobs 2,Org=Providence I would like to convert it into the below format: 1,1000,CedarparkHospital,john,doe,User001... (11 Replies)
Discussion started by: vskr72
11 Replies

2. Shell Programming and Scripting

Convert rows to columns

I am looking to print the data in columns and after every 3 words it should be a new row. cat example.out | awk 'END { for (i = 0; ++i < m;) print _;print _ }{ _ = _ x ? _ OFS $1 : $1}' m=1| grep -i INNER I am looking to print in a new line after every 3 words. ... (2 Replies)
Discussion started by: lazydev
2 Replies

3. Shell Programming and Scripting

Convert Rows to Columns

Hi Everyone, Could someone shed some lights on how to convert the records in rows form into column basis. 172.29.59.12 IBM,8255-E8B 102691P 8 65536 MB 6100-04-11-1140 172.29.59.15 IBM,8255-E8B 102698P 4 45056 MB 6100-04-11-1140 IP SYS MODEL ... (6 Replies)
Discussion started by: ckwan
6 Replies

4. UNIX for Dummies Questions & Answers

Awk: convert rows to columns every n lines

Hi guys! I use AWK commands under GAMS to predispose the data files to be read by GAMS. I have a file which contains groups of data I need. Unfortunately I have the data spread in 3 rows for each subject. Here's an example (the file is really long) 1 0 2.0956 100.00 250.00 100.00 2.0956... (4 Replies)
Discussion started by: Pintug
4 Replies

5. Shell Programming and Scripting

How to Convert rows in to columns?

Hi Gurus, How to convert rows in to columns using linux shell scripting Input is like (sample.txt) ABC DEF GHI JKL MNO PQR STU VWX YZA BCD output should be (sampleoutput.csv) ABC,DEF,GHI,JKL,MNO PQR,STU,VWX,YZA,BCD (2 Replies)
Discussion started by: infasriniit
2 Replies

6. Shell Programming and Scripting

Convert rows into columns using awk or perl

hi friends, i am able to parse cvs diff file using bit of cut and grep commands to produce following output in text file '''cvs-diff.txt''' Package-Name = dev-freetype. Old-Version = 2.4.8 New-Version = 2.4.10 Patches-removed = freetype-2.4.8-cross-compile.patch... (2 Replies)
Discussion started by: alexzander18
2 Replies

7. UNIX for Dummies Questions & Answers

Convert rows to columns using AWK

Hi , I am struck while coding AWK script. Need your help to convert rows into columns. I should copy only those rows which are marked to Y in a file and ignore N rows. Please help me find a solution. input file 1|abc|Y 2|cdf|Y 3|efg|N 4|xyz|Y my output should be something like this... (2 Replies)
Discussion started by: rashmisb
2 Replies

8. Programming

Creating a table like format with rows and columns

I have few files which have two columns in each. like e2 1 1 2694 2 4 2485 3 2 2098 5 1 2079 6 5 2022 9 4 1734 11 5 1585 13 2 1461 18 1 1092 21 2 1019 24 1 915 25 3 907 27 1 891 28 3 890 34 1 748 39 1 700 (1 Reply)
Discussion started by: kamuju
1 Replies

9. Shell Programming and Scripting

convert columns into rows

hi, Apologies if this has been covered. I have requirement where i have to convert a single column into multiple column. My data will be like this - 2 3 4 5 6 Output required - 2 3 4 5 6 (1 Reply)
Discussion started by: Nishithinfy
1 Replies

10. Shell Programming and Scripting

convert rows into columns

Hi guys Could anyone advise me how to convert my rows into columns from a file My file would be similar to this: A11 A12 A13 A14 A15 ... A1n A21 A22 A23 A31 A41 A51 ... Am1 Am2 Am3 Am4 Am5 ... Amn The number of rows is not the same to the number of columns Thanks in advance (2 Replies)
Discussion started by: loperam
2 Replies
Login or Register to Ask a Question