Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Mapping two files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Mapping two files
# 1  
Old 11-15-2012
Mapping two files
I have a large comma delimited file from which i have to extract a specific column, compare it against a static file and replace the column value with the matching column from the static file.

The following are the files:

Main file:
mainfile.csv
Code:
test1234,,real,,,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,TRY,10000,15/07...
test1234,,real,,,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,FDG,10000,15/07...
test1234,,real,,,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,HGC,10000,15/07...
test1234,,real,,,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,MDL,10000,15/07...

Static file.
reffile
Code:
CODE,PRODUCT
UGE72453,TRY
NGU76290,FDG
HRY82662,HGC
LQY35689,MDL

I now want to replace the bolded 26th column in the main file with the code from the static file
The main file has about 1000 lines but only has the aforementioned [products.
I am using the following code but only a

Code:
        OLDIFS=$IFS
        IFS=","
        while read pr1 pr2
        do
                for i in `cat mainfile.csv | awk 'BEGIN { FS = ","; } { print $26}'`
                do
                        if [[ "$i" == *"$pr2"* ]]; then
                                 echo $i $pr1
          #                     sed 's/$i/$pr1/' $i;
                        fi
                done
        done < reffile
        IFS=$OLDIFS

When i execute the above code, only some of the lines get their value replaced.

Can you please advise check for pr2 in the for loop and replace by the corresponding pr1 in from the while? I would appreciate if someone can even advise me on a better way of doing this
# 2  
Old 11-15-2012
Try this:

Code:
awk -F, 'FNR==NR{C[$2]=$1;next}{$26=C[$26]}1' OFS=, reffile mainfile.csv

This User Gave Thanks to Chubler_XL For This Post:
goddevil
# 3  
Old 11-15-2012
Using shell script:-
Code:
IFS=","
while read pr1 pr2
do
    sed "s/$pr2/$pr1/g" mainfile.csv > tmp; mv tmp mainfile.csv;
done < reffile

# 4  
Old 11-15-2012
Quote:
Originally Posted by bipinajith
Using shell script:-
Code:
IFS=","
while read pr1 pr2
do
    sed "s/$pr2/$pr1/g" mainfile.csv > tmp; mv tmp mainfile.csv;
done < reffile

Cheers bipi.

I am looking for a way to map the code to the specific column as there may be other columns with the same characters and i would not like them touched.

For eg:
Code:
test1234,,real,,TRY97837290,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,TRY     ,10000,15/07...
test1234,,real,,FDG09789238,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,FDG           ,10000,15/07...
test1234,,real,,HGC02749267,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,HGC          ,10000,15/07...
test1234,,real,,MDL92773671,,open,,,,EU,,,,23/03/2012 12:03:00,,,,GB,,,,1187,0,B,MDL           ,10000,15/07...

I suppose the fault is mine. I wasnt clear with my initial requirement as to why i am choosing this avenue of replacing a column. Apologies
# 5  
Old 11-15-2012
@bipinajith, yuck what happens if "TRY" appears in other fields on the line?
# 6  
Old 11-15-2012
Chubler_XL It will mess up the data Smilie
I posted a solution which will work based on file content initially posted. I'm sorry if that is not what the requester want Smilie
# 7  
Old 11-15-2012
Quote:
Originally Posted by Chubler_XL
Try this:

Code:
awk -F, 'FNR==NR{C[$2]=$1;next}{$26=C[$26]}1' OFS=, reffile mainfile.csv

I am getting the following error when i execute the command

Code:
awk: syntax error near line 1
awk: bailing out near line 1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Many to many -- mapping

INPUT 13333--TEXT1 14444--TEXT2 13333--TEXT3 12233--TEXT5 14444--TEXT5 12233--TEXT1 12222--TEXT5 13333--TEXT09 what I'm looking for is something using awk arrays with below given output. 14444--TEXT2,TEXT5 13333--TEXT1,TEXT3,TEXT09 12233--TEXT5,TEXT1 12222--TEXT5 (6 Replies)
Discussion started by: busyboy
6 Replies

2. UNIX for Advanced & Expert Users

Mapping Oracle SGA memory into physical files in Solaris.

Is there a way by which we could map Oracle SGA memory into physical files in solaris. We could find these physical files in /dev/shm/ folder in linux environment but they are missing in solaris. (1 Reply)
Discussion started by: arjun_chauhan
1 Replies

3. UNIX for Dummies Questions & Answers

Loop Through Files in Order and mapping

Hi there, How can I loop through files with order. I tried using ls .html | sort -v However the filename is with spaces:1233.61.47.0 - 121.61.123.112 nexpose.html Here is the original code that is working well, but I need to sort through the filename.echo "<center>" for file in *.html... (2 Replies)
Discussion started by: alvinoo
2 Replies

4. Shell Programming and Scripting

Mapping Servers

Hi All, I need an urgent assistance please . My case below: I have a list of 500 IP addresses. All These ip addresses are mapped/connected to different machine kinds : NT, Linux, Switch, Router ,FW, and so on. My Requirement is to filter from all this ip address only the Linux machines.... (2 Replies)
Discussion started by: James Stone
2 Replies

5. UNIX for Advanced & Expert Users

Help With Mapping machine

Hi All, I need an urgent assistance please . My case below: I have a list of 500 IP addresses. All These ip addresses are mapped/connected to different machine kinds : NT, Linux, Switch, Router ,FW, and so on. My Requirement is to filter from all this ip address only the Linux... (0 Replies)
Discussion started by: James Stone
0 Replies

6. Shell Programming and Scripting

Creating unique mapping from multiple mapping

Hello, I do not know if this is the right title to use. I have a large dictionary database which has the following structure: where a b c d e are in English and p q r s t are in a target language., the two separated by the delimiter =. What I am looking for is a perl script which will take... (5 Replies)
Discussion started by: gimley
5 Replies

7. Shell Programming and Scripting

Need Help Mapping Arrays

I have the following arrays with different lengths that I want to map them with the same key. # Week numbers, 8 columns @headers = ("2011-34", "2011-35", "2011-36", "2011-37", "2011-38", "2011-39", "2011-40", "2011-41"); %data = ("2011-34", BCE, "2011-35", YZA, "2011-36",... (5 Replies)
Discussion started by: tqlam
5 Replies

8. UNIX for Dummies Questions & Answers

Re-Mapping Printers.

Hi we have a situation where some printers are on a server that sometimes has to be rebooted. If this happens the Unix boxes we have that are referencing the printers in the vfstab file fail to work even when the print server is brought back up. Does anyone know if it would be possible to put... (0 Replies)
Discussion started by: Hadleyshope
0 Replies

9. Shell Programming and Scripting

Mapping and replacing numbers in two files

hi i have two files one of the form 1 2 2 45 3 56 4 98 5 6598 6 98 7 10 8 0 9 15 10 56 This file's significance is that it maps the number in first column to that of the number in second column The other file is of the form 1 2 (1 Reply)
Discussion started by: vaibhavkorde
1 Replies

10. Shell Programming and Scripting

Join 3 files using key column in a mapping file

I'm new of UNIX shell scripting. I'm recently generating a excel report in UNIX(file with delimiter is fine). How should I make a script to do it? 1 file to join comes from output of one UNIX command, the second from another UNIX command, and third from a database query. The key columes of all... (7 Replies)
Discussion started by: bigsmile
7 Replies
Login or Register to Ask a Question