Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Need script to merge two spreadsheets

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Need script to merge two spreadsheets
# 8  
Old 05-03-2012
Quote:
Originally Posted by tarun_agrawal
try this

Code:
 
 paste -d "|" file1 file2 | sed 's/|/\n/g' | tail -n +2

Crap that works nicely. Thanks so much!

Just wondering, can this be applied to merge into more than two lines? For example, say one file stays the same with only one unique name per row, but the second file has one name appearing multiple times, perhaps with a unique identifier :

PHP Code:
file 2
gene ID    W  X  Y  Z
I   2345         a
I   3545            b
I   2363      c
II  7524            e
II  7342   f
III 6884        f 
Last edited by torchij; 05-03-2012 at 10:42 AM..
# 9  
Old 05-03-2012
Looking at the column headers of the two files, I'd say the columns need to be moved about as well, don't they?

Code:
$ head -1 file[12].txt
==> file1.txt <==
"ID"	torch1	torch2	torch3	torch4	torch5	torch6	torch7	torch8	torch9	torch10	torch11	torch12	torch13	torch14	torch15	torch16	torch17	torch18	torch19	torch20	torch21	torch22	torch23	torch24	torch25	torch26

==> file2.txt <==
"ID"	torch15	torch8	torch10	torch17	torch16	torch3	torch4	torch2	torch9	torch7	torch5	torch11	torch12	torch14	torch6	torch1	torch13	torch23	torch26	torch24	torch22	torch18	torch25	torch19	torch20	torch21

# 10  
Old 05-03-2012
It seems to keep the order of the second file, which is arbitrary for me, maybe not in other cases.
# 11  
Old 05-03-2012
There is no order, for example column "torch1" from with file1 will get merged with column "torch15" of file2, likewise with "torch2" and "torch8", etc.. So that is no problem?
# 12  
Old 05-03-2012
Quote:
Originally Posted by Scrutinizer
There is no order, for example column "torch1" from with file1 will get merged with column "torch15" of file2, likewise with "torch2" and "torch8", etc.. So that is no problem?
Oh yes you are right...both files have to in the same order for it to work. Thanks I didnt even notice.
# 13  
Old 05-03-2012
Try:
Code:
awk -F'\t' '
{ 
  print
}
NR==1{
  for (i=2;i<=NF;i++) Label[$i]=i
  getline < secondfile
  for (i=2;i<=NF;i++) Order[i]=Label[$i]
  next
} 
{
  getline < secondfile
  $1=""
  split($0,Field)
  for(i=2;i<=NF;i++) $Order[i]=Field[i]
  print
}
' OFS='\t' secondfile=file2.txt file1.txt

--
Remember, the files are in DOS format, they need to be converted to Unix format first...
Last edited by Scrutinizer; 05-03-2012 at 01:23 PM..
# 14  
Old 05-03-2012
Quote:
Originally Posted by Scrutinizer
Try:
Code:
awk -F'\t' '
{ 
  print
}
NR==1{
  for (i=2;i<=NF;i++) Label[$i]=i
  getline < secondfile
  for (i=2;i<=NF;i++) Order[i]=Label[$i]
  next
} 
{
  getline < secondfile
  $1=""
  split($0,Field)
  for(i=2;i<=NF;i++) $Order[i]=Field[i]
  print
}
' OFS='\t' secondfile=file2.txt file1.txt

--
Remember, the files are in DOS format, they need to be converted to Unix format first...
It doesn't seem to print the second line. It creates a new line for each row, but the 2nd data point isnt being printed. I've attached the output file.
test.txt (148.8 KB) 
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to merge and delete lines

POLY_STATS { EqName 103_tri Id 123 act_polyCount 1 act_polyValue 0 } POLY_STATS { EqName 103_tri Id 123 pass_polyCount 2 pass_polyValue 0 } POLY_STATS { EqName 103 Id 123 mes_polyCount 2 mes_polyValue 0 (5 Replies)
Discussion started by: Jag02
5 Replies

2. Shell Programming and Scripting

How to merge Expect script inside shell script?

Hi I have two scripts one is Expect and other is shell. I want to merge Expect code in to Shell script so that i can run it using only one script. Can somebody help me out ? Order to execute: Run Expect_install.sh first and then when installation completes run runTests.sh shell script. ... (1 Reply)
Discussion started by: ashish_neekhra
1 Replies

3. Shell Programming and Scripting

script to merge two files on an index

I have a need to merge two files on the value of an index column. input file 1 id filePath MDL_NUMBER 1 MFCD00008104.mol MFCD00008104 2 MFCD00012849.mol MFCD00012849 3 MFCD00037597.mol MFCD00037597 4 MFCD00064558.mol MFCD00064558 5 MFCD00064559.mol MFCD00064559 input file 2 ... (9 Replies)
Discussion started by: LMHmedchem
9 Replies

4. Shell Programming and Scripting

Help with merge Shell Script

I have a file test.log The content of the file is : a:R_yz:1 a:R_cd:2 a:F_bc:0 a:F_xx:3 b:R_dg:5 b:R_gf:1 b:F_fd:4 I want the output is : :a R_yz 1 3 3 : R_cd 2 : F_bc 0 : F_xx 3 :b R_dg 5 6 4 : R_gf 1 : F_fd 4 (8 Replies)
Discussion started by: mnmonu
8 Replies

5. Shell Programming and Scripting

script to merge multiple line

Dear all, I am new to this community. I need a script that will merge two lines of a text file based on some character. for example- Input asfdas fas sdfhksd sdf ss 45 gf# gjsdh fhjsd yiu oiuio ioioiuii sdgjdshsdg sdhfjk sdfhsd sdf sdf sdf wer wer we# qqwewq qwe qwe wer# fsdf sdf... (8 Replies)
Discussion started by: wildhorse
8 Replies

6. Shell Programming and Scripting

merge two files via looping script

Hi all, I hope you can help me. I got a file a and a file b File a contains a b c d e f g h File b contains 1 2 3 (8 Replies)
Discussion started by: stinkefisch
8 Replies

7. Shell Programming and Scripting

script to merge xml files with options

Hi, I have a very basic knowledge of shell scripting & would like some help with a little problem I have. I sometimes use a program calle phronix & sometimes like to compare its results which are *.xml files. Which is easy enough but a friend wants to avoid typing the path to the files.... (2 Replies)
Discussion started by: ptrbee
2 Replies

8. Shell Programming and Scripting

script needed to merge two files and report differences

Hello, I have two txt files that look like this: db.0.0.0.0: Total number of NS records = 1 db.127.0.0.0: Total number of NS records = 1 Total number of PTR records = 1 db.172.19.0.0: Total number of NS records = 1 Total number of PTR records = 3 db.172.19.59.0: Total... (8 Replies)
Discussion started by: richsark
8 Replies

9. Shell Programming and Scripting

Merge two files in windows using perl script

Hi I want to merge two or more files using perl in windows only(Just like Paste command in Unix script) . How can i do this.Is ther any single command to do this? Thanks Kunal (1 Reply)
Discussion started by: kunal_dixit
1 Replies

10. Shell Programming and Scripting

shell script to merge files

Can anybody help me out with this problem " a shell program that takes one or any number of file names as input; sorts the lines of each file in ascending order and displays the non blank lines of each sorted file and merge them as one combined sorted file. The program generates an error... (1 Reply)
Discussion started by: arya
1 Replies
Login or Register to Ask a Question