| How to compare data in two flat files and update them? [message #145471] |
Thu, 03 November 2005 06:26  |
rajus19
Messages: 18
Registered: September 2005
|
Junior Member |
|
|
Hi All,
I am giving an example similar to the problem I have.
I have two data files of 10 columns each in which fields are delimited by comma(,). I need to compare compare the two files using the uniq col(col3). If there are any records in file1 and are not in file2 then I have check the value for col4 of those records from a database table, if its success then I have to include those records in file2 and have to change the value of col6 to "updated".
Can you please give me an idea how to implement in a shell script.
your help will be greatly appreciated.
Thanks in Advance.
Raju
[Updated on: Thu, 03 November 2005 06:31] Report message to a moderator |
|
|
|
| Re: How to compare data in two flat files and update them? [message #145548 is a reply to message #145471] |
Thu, 03 November 2005 14:26  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
For me, this is easy to do in SQL, but not at the file level. Why don't you just load the file records into temp tables, do the "merge" and write it back out to file. you can use sqlloader or utl_file to load the data into the tables and use utl_file to write the result to the new file. As long as the format of the file is correctly written, it doesn't make any difference if you edit the file or created a new one.
|
|
|
|
| Re: How to compare data in two flat files and update them? [message #146079 is a reply to message #145471] |
Tue, 08 November 2005 09:10 |
rajus19
Messages: 18
Registered: September 2005
|
Junior Member |
|
|
Could you please suggest me how to replace two fields in a flat file which contains more than 40 fields.
If I use sed by searching for the strings there may be a chance that the same string occurs in other fields also.
If I use awk I explicitly metion the rest of the 38 fields.
Please suggest me how to deal with this.
Thanks in advance
Raju
|
|
|
|
|
|
| Re: How to compare data in two flat files and update them? [message #159488 is a reply to message #145471] |
Mon, 20 February 2006 00:47 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It is possible in Unix, but ONLY if the two files are already sorted on the join key.
eg.
We have file1 and file2 joined on file1.col3 = file2.col1.
File1 is sorted by col3 (ie. the join key)
File2 is sorted by col1 (ie. the join key)
Column 6 of file1 for rows in file1 NOT IN file2:
join -a1 -13 -21 -t, -o1.6 file1 file2
Now, this is REALY, REALLY, important:
If the files are NOT sorted in the right order, then you must sort them first (with sort) and output them to regular files. DO NOT attempt to do it all in memory with pipes - it won't work. The join command must be able to fseek both files - impossible if one or both is a named pipe.
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
