Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Combining rows into columns

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Combining rows into columns
# 1  
Old 11-22-2013
Combining rows into columns
hi experts,

I have a flat file with below contents

Code:
Database1 Table1 column1
Database1 Table1 column2
Database1 Table1 column3
Database1 Table1 column4
Database1 Table2 Column1
Database1 Table2 Column2
Database2 Table1 Column1
Database2 Table1 Column2
Database2 Table1 Column3
Database2 Table2 Column1


Output file as below:

Code:
Database1..Table1..column1,column2,column3,column4
Database1..Table2..column1,column2
Database2..Table1..column1,column2,column3
Database2..Table2..column

Please help me.
Moderator's Comments:
Mod Comment Please use CODE tags for code, input, and output samples.
Last edited by Don Cragun; 11-22-2013 at 10:37 PM.. Reason: Add CODE tags.
# 2  
Old 11-22-2013
Is this a homework assignment?

What have you tried so far?
# 3  
Old 11-22-2013
I have tied the below,i knew there is some logic is missing, unable to capture it.

Code:
olddb=""
oldtb=""
while `read line`
do
DB=`echo $line | awk -F' ' '{print $1}'`
TB=`echo $line | awk -F' ' '{print $2}'`
CL=`echo $line | awk -F' ' '{print $3}'`
if [ $DB != $olddb ] && [ $TB != $oldtb ]; then
echo ${DB}..${TB}..${CL} >> newfile
else
col=$temp","$CL
fi
temp=${DB}..${TB}..${CL}
olddb=$DB
oldtb=$TB
echo $col >> newfile
done < column.txt

while displaying thefile,

i get output as below

Code:
Database1..Table1..col1,col2
Database1..Table1..col2,col3
Database1..Table1..col3,col4
Database1..Table2..col4,col1
Database1..Table2..col1,col2

# 4  
Old 11-22-2013
You might try something like:
Code:
awk '
function pe() { if(NR > 1) print last columns }
$1 ".." $2 ".." != last {
        pe()
        last = $1 ".." $2 ".."
        columns = $3
        next
}
{       columns = columns "," $3 }
END {   pe() }' column.txt > newfile

which, with your sample input, stores:
Code:
Database1..Table1..column1,column2,column3,column4
Database1..Table2..Column1,Column2
Database2..Table1..Column1,Column2,Column3
Database2..Table2..Column1

in the file named new file.

Invoking awk once per input file instead of three times for each line in your input file should be considerably more efficient. Note, however, that this output does not translate uppercase letters in the 3rd column of your input to lowercase letters and does not chop off the last character from the 3rd column of an input line when only one line matches in the 1st two columns as was shown in the output you said you wanted in the 1st message in this thread. I assumed that those were typos in what you said you wanted in the output, but the code could be modified to make those changes if that really is what you wanted.

If you want to try this on a Solaris/SunOS system, change awk to nawk, /usr/xpg4/bin/awk, or /usr/xpg6/bin/awk.
# 5  
Old 11-23-2013
Thanks for your prompt response, but its not giving the output which is expected, instaed its giving as below

Code:
Database1..Table1..col1
Database1..Table1..col2
Database1..Table1..col2
..
...

I am using Korn shell (OS: Solaris)

As you said, i have used nawk instead of awk, but it didn't give the output which i required.
# 6  
Old 11-23-2013
Don's solution is working for given input with a assumption that file is sorted

You may try if output order is doesn't matter

Code:
$ cat file
Database1 Table1 column1
Database1 Table1 column2
Database1 Table1 column3
Database1 Table1 column4
Database1 Table2 Column1
Database1 Table2 Column2
Database2 Table1 Column1
Database2 Table1 Column2
Database2 Table1 Column3
Database2 Table2 Column1

Code:
$  awk '{A[$1 FS $2] = A[$1 FS $2] ? A[$1 FS $2] OFS $3 : $0}END{for(i in A)print A[i]}' OFS=\, file
Database2 Table1 Column1,Column2,Column3
Database2 Table2 Column1
Database1 Table1 column1,column2,column3,column4
Database1 Table2 Column1,Column2

if .. is needed then try
Code:
$ awk '{A[$1 FS $2] = A[$1 FS $2] ? A[$1 FS $2] OFS $3 : $0}END{for(i in A){gsub(/[[:space:]]/,"..",A[i]);print A[i]}}' OFS=\, file
Database2..Table1..Column1,Column2,Column3
Database2..Table2..Column1
Database1..Table1..column1,column2,column3,column4
Database1..Table2..Column1,Column2

for order sort like this
Code:
$ awk '{A[$1 FS $2] = A[$1 FS $2] ? A[$1 FS $2] OFS $3 : $0}END{for(i in A){gsub(/[[:space:]]/,"..",A[i]);print A[i]}}' OFS=\, file | sort -nk1
Database1..Table1..column1,column2,column3,column4
Database1..Table2..Column1,Column2
Database2..Table1..Column1,Column2,Column3
Database2..Table2..Column1

Note : use nawk on solaris
Last edited by Akshay Hegde; 11-23-2013 at 01:42 AM..
# 7  
Old 11-23-2013
Quote:
Originally Posted by Selva_2507
Thanks for your prompt response, but its not giving the output which is expected, instaed its giving as below

Code:
Database1..Table1..col1
Database1..Table1..col2
Database1..Table1..col2
..
...

I am using Korn shell (OS: Solaris)

As you said, i have used nawk instead of awk, but it didn't give the output which i required.
With your given sample, using nawk on Solaris 10 and with Don's suggestion I get:

Code:
Database1..Table1..column1,column2,column3,column4
Database1..Table2..Column1,Column2
Database2..Table1..Column1,Column2,Column3
Database2..Table2..Column1

Can you confirm that it works with your own sample? If not, what is your Solaris version?

If so, could you post a new sample that better reflects you real world data? And show us where the suggestion fails with that input?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

2. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

3. Linux

[Solved] Combining columns from different files

Hey Guys & Gals, I am stuck with the following ; I have 2 text files, each containing 2 columns. My goal is to have a column from the 2nd file placed inbetween the columns in the first file. Basically the idea is, each address has a different name (but 1 name per address) but 1 address... (6 Replies)
Discussion started by: TAPE
6 Replies

4. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

5. Shell Programming and Scripting

Combining multiple rows in single row based on certain condition using awk or sed

Hi, I'm using AIX(ksh shell). > cat temp.txt "a","b",0 "c",bc",0 "a1","b1",0 "cc","cb",1 "cc","b2",1 "bb","bc",2 I want the output as: "a","b","c","bc","a1","b1" "cc","cb","cc","b2" "bb","bc" I want to combine multiple lines into single line where third column is same. Is... (1 Reply)
Discussion started by: samuelray
1 Replies

6. Shell Programming and Scripting

Combining rows in a text file with a character limit

I have a file that contains several thousands rows. Here is an example. ^411912$ ^487267$ ^643776$ ^682249$ ^687737$ ^692328$ ^693767$ ^695483$ ^697289$ ^757411$ ^776688$ ^778953$ ^806123$ ^872262$ ^877877$ ^839837$ ^76666$ ^72018$ ^23330$ (3 Replies)
Discussion started by: justinb_155
3 Replies

7. Shell Programming and Scripting

combining cat output and cutting rows

I have a file that contain the following. -D HTTPD_ROOT="/usr/local/apache" -D SERVER_CONFIG_FILE="conf/httpd.conf" I want a shell script, so that after cat filename and apply the shell script I should get the output as follows. /usr/local/apache/conf/httpd.conf ie cat filename |... (7 Replies)
Discussion started by: anilcliff
7 Replies

8. UNIX for Dummies Questions & Answers

Combining two text files as columns?

I have one space delimited file with multiple columns and one tab delimited file with multiple columns (They have the same number of rows). I want to basically combine these two text files into a new text file by column. How would I go about doing that? (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

Combining columns from different files

I have two files I need to combine. The problem I'm having is I need to only combine data from the second file in the empty spaces of the first. For example: file1 Data Field Data Field Data Field Data Field file2 a - Insert Data b - Insert Data c - Insert Data d - Insert Data... (10 Replies)
Discussion started by: handband2
10 Replies

10. Shell Programming and Scripting

combining columns from different files

Hi all, I would be very grateful for some advice on the following. I have several text files. The files are experiment results with columns of data separated by white space. The files begin with several lines of header which are all preceeded by a comment character '#'. Each file has a... (10 Replies)
Discussion started by: iomaire
10 Replies
Login or Register to Ask a Question