Combine columns from many files but keep them aligned in columns-shorter left column issue

isildur1234 · #1 11-30-2012

Hello everyone,
I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble.
I have many files each having two columns and hundreds of rows.
first column is a string (can have many words) and the second column is a number.The files are tab separated.
Example of some rows of three files:

file1.txt

Code:

Column1 Column2
S1	31.37
S1	31.49
S1	31.74
S10	31.13
S10	31.10
S10	31.29
S11	29.49
S11	29.54
S11	29.25
S12	31.24
S12	31.05
S12	31.50
S13	32.48
S13	31.07
...

rdrtx1 · #2 11-30-2012

try:

Code:

awk '
{
(FNR>m)? m=FNR:0;
f[FILENAME]++ ? 0:fc++;
a[fc-1,FNR]=$0;
}
END {
  for (j=1;j<=m;j++) {
     for (i=0;i<fc;i++) {
       printf (a[i,j])? (a[i,j] "\t"):("\t\t");
     }
     print "";
  }
}
' file*.txt > out.xls

isildur1234 · #3 12-03-2012

Thank you so much. This is working and aligning the columns correctly!
If you have time, can youvery very briefly explain how it works, why your for statements manage to align the columns, especially what happens at the printf?
for (j=1;j<=m;j++) {
for (i=0;i<fc;i++) {
printf (a[i,j])? (a[i,j] "\t"):("\t\t");

awk seems to be a very powerful language for file manipulation. How did you learn it? Textbook or website?
Again, thank you so much.

rdrtx1 · #4 12-03-2012

Code:

awk '{
(FNR>m)? m=FNR:0;                                # use file record number as max record counter
f[FILENAME]++ ? 0:fc++;                          # set new file counter (++ fail indicates new file)
a[fc-1,FNR]=$0;                                  # store data in two column,row array (file, record)}
END {  
  for (j=1;j<=m;j++) {                           # record counter loop     
    for (i=0;i<fc;i++) {                         # file counter loop       
       printf (a[i,j])? (a[i,j] "\t"):("\t\t");  # print tab separated data or just tabs if empty     
    }
    print "";                                    # print new line character  
  }
}' file*.txt > out.xls

nex_asp · #5 12-03-2012

Code:

awk: cmd. line:15: fatal: cannot open file `file*.txt' for reading (No such file or directory)

if you try 

a.txt b.txt > out.xls

isildur1234 · #6 01-23-2013

Right now my input to this code file*.txt > out.xls
outputs the files pasted together but the files are pasted randomly. I want to read the files from my folder alphabetical lexicographical order and paste them together.
is there a way to sort my input files alphabetically before I run this code?
I tried sort file*.txt but it doesnt work, nor does ls-1|folder.

Thread Tools	Search this Thread
Show Printable Version Email this Page Subscribe to this Thread	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Combine columns from 100 files with same structure	genehunter	UNIX for Dummies Questions & Answers	6	09-04-2012 07:47 AM
Combine columns from multiple files	sdf	Shell Programming and Scripting	2	05-03-2012 02:16 PM
How to combine 2 files with 6 columns?	tgoldstone	UNIX for Dummies Questions & Answers	2	03-14-2012 02:13 AM
How to combine 2 files into 1 file with 2 columns	pinpe	Shell Programming and Scripting	11	06-20-2011 11:46 AM
Output columns needs to be aligned	nkamalkishore	Shell Programming and Scripting	4	11-06-2009 10:24 AM

The Following User Says Thank You to rdrtx1 For This Useful Post:
isildur1234 (12-03-2012)