How to Merge 2 files with same values in similar columns in the files

[Nabendz]

Hi there, Forgive me for my little skills in awk. I believe that this could be obvious to some one with good skills in awk. I would like to create a text file C that merges 2 text files A and B with comma seperated values. The task is to look at a given column within the two files and return all the rows from the two files for which the values in that given column are the same. As an illustration, if i have A and B as

A.txt
11, one, two
12, three, four
13, five, six

B.txt
11, un, deux
13, cinq, six
19, quinze, sept
21, quartonze, cinquonze

I would like the file C to contain rows for which the values in the first column of the two files match, i.e C should be

11, one, two
11, un, deux
13, five, six
13, cinq, six

regards,
Nabendz

 

[Annihilannic]

Have you tried anything so far? If so, please post your code.

I'd suggest concatenating the two input files, sorting them numerically on the first field, and then using awk to output only the fields where the first column on the previous line matches the current line.

Annihilannic.

 

[Nabendz]

yea, i tried, first by extracting the files out of one of the geonames database called alternatenames.txt e.g if i wanted only english i used:

tr "\t" "," < alternatenames.txt | egrep ',en,' > onlyenglish.txt

i also did for onlyfrench.txt

I then imported the files to a database where i performed the SQL:

select * from onlyenglishtable, onlyfrenchtable
where onlyenglishtable.column1 = onlyfrenchtable.column1;

i then exported the results of this select to a txt file "englishfrench.txt from which i used awk:

awk -F ',' '{print $2}' englishfrench.txt > alignedenglishfile.txt

and

awk -F ',' '{print $4}' englishfrench.txt > alignedfrench.txt

to produce 2 files one containing english names and the other containing french names positioned at corresponding lines at corresponding lines i.e if if i have "peter" in line 2 in alignedenglish.txt then i will correspondingly have "Pierre" in line 2 in alignedfrench.txt.

I wanted to do away with the use of the database part. I will try what you have suggested.

Kind regards,
Nabendz