[SOLVED] Analyzing CSV files

arbex5 · 07-04-2012, 11:21 PM

From Tread: http://www.linuxquestions.org/questi...0/#post4509101

I see this is a pretty nice solution.
I do need something very similar to this.

I have two cvs files (geodata files)

One have 3 columns (lets call file_1) and the other 5 columns (lets call file_2)

file_1 is like this:

startIpNum,endIpNum,locId
"16777216","16777471","17"
"16777472","16777727","24328"
"16777728","16778239","49"

file_2 is like this:

geo_id,country,region,city,postalCode
32,BR,,,
19304,BR,SP,São Paulo,
24328,BR,RJ,Rio De Janeiro,
22975,BR,SP,Salto,

What I need is to create a new file (lets call file_3) that match the field locId of the file_1 with the field geo_id of the file_2
In other words, if a field locId (file_1) and a field gep_id (file_2) are equal, generate a file_3 with the row of file_1 that matched the criteria.

Example.

Using the tables above and running the script the resulting file_3 would be

startIpNum,endIpNum,locId
"16777472","16777727","24328"

Which is the locId "24328" (file_1) matched the geo_id "24328" (file_2) and generate the resulting file_3

Is this hard to make?
Thanks.

grail · 07-05-2012, 12:56 AM

Please do not resurrect old questions. If you feel this one is relevant to your solution then add a link and comment back to this one within your own question.

crabboy · 07-05-2012, 08:53 AM

This sounds much easier than the other problem, sounds like you want to pluck items from file1 which contain a key that is in the second file. This can be done with a simple shell script.

Loop over each line in file1 and grab the third field and grep for that in file2:

Code:

#!/bin/sh

for LocID in `cat file1 | cut -d ',' -f3 | tr -d \"`; do
  grep ^$LocID file2 >> file3
done

This is not bullet proof, but should work.

arbex5 · 07-06-2012, 09:59 AM

Hi tried your code but I didn't get it to work, I don't know why.
But here is the thing.
As a suggestion I opened a new thread about this problem, and Daniel B. Martin answered a solution that worked pretty nice with my files.
Here it is.

cut -d, -f1 $InFile2 \
|sed 's/^/"/g' \
|sed 's/$/"$/g' \
|grep -f - $InFile1 \
> $OutFile

And here is the link.
http://www.linuxquestions.org/questi...21#post4720321

Anyway thank you so much and have a nice day.

grail · 07-06-2012, 11:13 AM

Please close this thread then

arbex5 · 07-06-2012, 01:19 PM

Done.
Thank you Grail.