Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Dynamically merging 2 files on header values

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Dynamically merging 2 files on header values
# 1  
Old 06-27-2013
Dynamically merging 2 files on header values
Hi All,

I have 2 files which i need to merge together based on the column names provided in the file. The first line in both files are header records.
The first file has fixed columns but second file can have subset of the columns from file 1

File 1:
Code:
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098

File 2:

Code:
column2,column3,column6
656,wzw,898

Desired output:

Code:
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098
,656,wzw,,,898


Please help on how to do this.
Last edited by radoulov; 06-28-2013 at 05:53 AM..
# 2  
Old 06-27-2013
What have you tried so far? we can build on that may be..
# 3  
Old 06-27-2013
Try

Code:
awk -F, 'NR==1{n=split($0,A,",")}
    NR==FNR{print; next}
    FNR==1{m=split($0,B,",")}
    FNR>1{for(i=1;i<=m;i++){AT[B[i]]=$i}
    {for(i=1;i<=n;i++){S=S?S","AT[A[i]]:AT[A[i]]}
    {print S;S=""}}}' file_1 file_2

# 4  
Old 06-27-2013
Quote:
Originally Posted by pamu
Try

Code:
awk -F, 'NR==1{n=split($0,A,",")}
    NR==FNR{print; next}
    FNR==1{m=split($0,B,",")}
    FNR>1{for(i=1;i<=m;i++){AT[B[i]]=$i}
    {for(i=1;i<=n;i++){S=S?S","AT[A[i]]:AT[A[i]]}
    {print S;S=""}}}' file_1 file_2

Hi
I tried this code but the out put for this is as follows

Code:
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098
wzw,898

Last edited by radoulov; 06-28-2013 at 05:54 AM..
# 5  
Old 06-27-2013
Please use code tags as required by forum rules!
Try this
Code:
awk     'NR==1          {CNT=split($0, HD)}
         NR==FNR        {print; next}
         FNR==1         {SCN=split ($0, SB); for (j=1; j<=CNT; j++)
                                                  for (i=1; i<=SCN; i++)
                                                        if (SB[i] == HD[j]) COL[j]=i
                         next}
                        {for (j=1; j<=CNT; j++) {T=COL[j]; printf "%s%s", T?$T:"", (j==CNT)?"\n":OFS}}
        ' FS="," OFS="," file1 file2
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098
,656,wzw,,,898

# 6  
Old 06-28-2013
Quote:
Originally Posted by RudiC
Please use code tags as required by forum rules!
Try this
Code:
awk     'NR==1          {CNT=split($0, HD)}
         NR==FNR        {print; next}
         FNR==1         {SCN=split ($0, SB); for (j=1; j<=CNT; j++)
                                                  for (i=1; i<=SCN; i++)
                                                        if (SB[i] == HD[j]) COL[j]=i
                         next}
                        {for (j=1; j<=CNT; j++) {T=COL[j]; printf "%s%s", T?$T:"", (j==CNT)?"\n":OFS}}
        ' FS="," OFS="," file1 file2
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098
,656,wzw,,,898

Thanks for your help.
But on running the code the output is as follows

Code:
column1,cloumn2,coulmn3,column4,column5,column6
abc,def,12,34,56,tyu
,,,,,123,asz
,,,,,poi,uyt
,,,,,qwe,poiyt
,,,,,qqq,123

File 1:

Code:
column1,cloumn2,coulmn3,column4,column5,column6
abc,def,12,34,56,tyu


file 2:

Code:
column3,column5,column6
qwe,123,asz
rew,poi,uyt
rty,qwe,poiyt
txz,qqq,123


While merging it is leaving out values for column 3 and populating it as null.

---------- Post updated at 04:54 AM ---------- Previous update was at 03:49 AM ----------

Quote:
Originally Posted by RudiC
Please use code tags as required by forum rules!
Try this
Code:
awk     'NR==1          {CNT=split($0, HD)}
         NR==FNR        {print; next}
         FNR==1         {SCN=split ($0, SB); for (j=1; j<=CNT; j++)
                                                  for (i=1; i<=SCN; i++)
                                                        if (SB[i] == HD[j]) COL[j]=i
                         next}
                        {for (j=1; j<=CNT; j++) {T=COL[j]; printf "%s%s", T?$T:"", (j==CNT)?"\n":OFS}}
        ' FS="," OFS="," file1 file2
Column1,column2,column3,column4,column5,column6
Abc,123,zyz,456,asd,098
,656,wzw,,,898

The code works Smilie ... thanks a lot Smilie ... it was my mistake in column name spelling Smilie

have one more issue though... the columns can have comma in middle but they are enclosed in quotes
# 7  
Old 06-28-2013
You may want to have a look into https://www.unix.com/shell-programming-scripting/228791-how-delete-column-columns-csv-file-has-cell-values-string-enclosed.html.
Hopefully you can put together a solution yourself combining the two threads.
 
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 print multiple required columns dynamically in a file using the header name?

Hi All, i am trying to print required multiple columns dynamically from a fie. But i am able to print only one column at a time. i am new to shell script, please help me on this issue. i am using below script awk -v COLT=$1 ' NR==1 { for (i=1; i<=NF; i++) { ... (2 Replies)
Discussion started by: balu1234
2 Replies

2. Shell Programming and Scripting

Merging two files to form header

Dear experts required you support to achieve below i need the file 2 values show on top of file 1 for mentioned valuesENTER CREDENTIALS or beside this please support to achieve the expected result . if I get the result 2 that will be better I have two files File 1 ENTER CREDENTIALS BILLING... (4 Replies)
Discussion started by: mirwasim
4 Replies

3. UNIX for Beginners Questions & Answers

Concatenate column values when header is Matching from multiple files

there can be n number of columns but the number of columns and header name will remain same in all 3 files. Files are tab Delimited. a.txt Name 9/1 9/2 X 1 7 y 2 8 z 3 9 a 4 10 b 5 11 c 6 12 b.xt Name 9/1 9/2 X 13 19 y 14 20 z 15 21 a 16 22 b 17 23 c 18 24 c.txt Name 9/1 9/2... (14 Replies)
Discussion started by: Nina2910
14 Replies

4. Shell Programming and Scripting

Generate a DML dynamically based off of header record

I have the following scenario where I need to use a header record from a file and generate a DML based off of it... E.g.: The header can change periodically with an additional column in between or remove a col.... Sample header : (head -1 sample.txt)... (17 Replies)
Discussion started by: anduzzi
17 Replies

5. UNIX for Dummies Questions & Answers

Merging two text files by a column and filling in the missing values

Hi, I have to text files that I want to merge by the first column. The values in the first column pretty much match for the first part. However there are some values that are present in column 1 and not present in column 2 or vice versa. For such values I would like to substitute X for the... (9 Replies)
Discussion started by: evelibertine
9 Replies

6. Shell Programming and Scripting

Modifying the values of dynamically named arrays

Hi all, In ksh, I'm trying to loop through all of my arrays, named array1, array2, array3..., and update the indices. But I'm getting errors and I'm not sure how to fix them. The errors are ./parse.sh: 6+1: not found The code is: eval \${array$c}=$(eval \${array$c}+1 ) Any help... (12 Replies)
Discussion started by: nicksantos1
12 Replies

7. Programming

Retreving the dynamically allocated values from bdb using C

In one of the assignment which i am working on, i am am trying to insert keys and values into BDB by reading the input records from a input file as below. Here keys i am inserting as character buffer and for values i am dynamically allocating the memory using malloc and then inserting into bdb.... (1 Reply)
Discussion started by: AmbikaValagonda
1 Replies

8. UNIX for Advanced & Expert Users

Retreving the dynamically allocated values from bdb using C

In one of the assignment which i am working on, i am am trying to insert keys and values into BDB by reading the input records from a input file as below. Here keys i am inserting as character buffer and for values i am dynamically allocating the memory using malloc and then inserting into bdb.... (1 Reply)
Discussion started by: AmbikaValagonda
1 Replies

9. UNIX for Dummies Questions & Answers

Merge all csv files in one folder considering only 1 header row and ignoring header of all others

Friends, I need help with the following in UNIX. Merge all csv files in one folder considering only 1 header row and ignoring header of all other files. FYI - All files are in same format and contains same headers. Thank you (4 Replies)
Discussion started by: Shiny_Roy
4 Replies

10. Shell Programming and Scripting

dynamically adding values in c-shell

I am needing to create a variable(changing) and assign it a value(changing) ... I am using C-Shell.. Example: foreach account in ($Accountlist) set account_connect = "$account/$account_pass" end I want to make set account_connect to store various values ? $account_connect did not... (3 Replies)
Discussion started by: shafi2all
3 Replies
Login or Register to Ask a Question