Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have 2 CSV files for each date (csv_2014_4_15 and csv_2014_4_16) , with basic structure and couple of unique columns as below.

id,name,created_at,updated_at,other columns

12, joe, 2013-1-1 18:30, 2014-2-1 12:00
56, bob, datetime, datetime

I want to merge the 2 csv files based on these these conditions. My code so far is as below.

if (csv_date_x.id == csv_date_x+1.id)
{
   if(csv_date_x.updated_at < csv_date_x.updated_at)
         add csv_date_x+1 row into out.csv
}
else {
   if(csv_date_x+1.created_at == TODAY (yyyy-mm-dd)
         add csv_date_x+1 row into out.csv
}
share|improve this question
    
If both condition is false, the line in csv_date_x will print? –  cuonglm Jul 11 at 13:25
    
No, nothing will be printed –  user3696932 Jul 11 at 13:29
    
Lots of ambiguity in this question. You don't say for example if there is at most a single instance of 'id' in each file, or if they are sorted. I assume that by csv_date_x you mean csv_2014_4_15 and csv_date_x+1 means csv_2014_4_16. But according to your pseudo-code you never use any lines from csv_date_x, so that's more a filter than a merge and you wind up with a subset of csv_2014_4_16. –  BobDoolittle Jul 11 at 14:44
    
I am sorry for the ambiguities, but it seems you understood. –  user3696932 Jul 11 at 15:41

2 Answers 2

up vote 2 down vote accepted

Try this:

$ awk -F',' -v t=$(date +"%Y-%-m-%-d) '
    FNR == NR {
        u[$1] = $4;
        next;
    }
    $4 > u[$1] {
        print;
        next;
    }
    t ~ $3
' file[x] file[x+1]

Explanation

  • We get today date, save it in variable t
  • While reading file[x] FNR == NR, we save each updated time of each id in associative array u, key is id, value is updated time.
  • While reading file[x+1]:

    • If update date of id, $4 greater than correspond id updated time save in array u ($4 > u[$1]), we print the line and skip to next line.
    • If above condition false, we check if created date of current line is today, t ~ $3, i.e "2014-7-11" ~ "2014-7-11 12:00", if true print the line.
share|improve this answer
1  
Can you please explain how this works ? –  user3696932 Jul 11 at 14:59
    
FNR==NR compares the ids ? and in the first scope you create 2 arrays with the dates ? c=csv_x 3rd column ? and u = csv_x+1 4th column ? and in the second scope you compare the dates? –  user3696932 Jul 11 at 15:07
    
@user3696932: I updated my answer, remove unnecessary things. –  cuonglm Jul 11 at 15:14
    
Thank you sir for the update –  user3696932 Jul 11 at 15:39

I assume that by csv_date_x you mean csv_2014_4_15 and csv_date_x+1 means csv_2014_4_16. But according to your pseudo-code you never use any lines from csv_date_x, so that's more a filter than a merge and you wind up with a subset of csv_2014_4_16.

This is a non-trivial programming task, but I can point you in a profitable direction.

This sort of thing can get hairy if you have to deal with escaped commas in the first two fields. I'll assume that you do not.

I'll also assume that you can compare these two files row-by-row, since you didn't specify.

You'll need to first normalize your csv files so that months and dates are always two digits (leading zeros). Then they can be compared textually, and this is pretty easy with awk. Something like:

sed 's/-\([1-9]\)/-0\1/'

(this assumes you have no hyphens in your names, ids, or "other columns", you'll need a different approach if so, probably matching more carefully on date patterns and possibly restricting to the 3rd and 4th fields).

Use the date program to generate today's date in the output format that matches your fields, and pass that in as a variable for awk to use:

awk -v Today=$(date +"%Y-%m-%d %H:%M") -v File1=csv_2014_4_15 -v File2=csv_2014_4_16 '
{ getline line1 < File1;
  getline line2 < File2;
  split(line1, f1, ",");
  split(line2, f2, ",");
}
f1[1] == f2[1] { ...; next } # compare IDs this is your first 'if' clause
{ ... } # this is your else clause since the previous ended with "next"

You're in pretty good shape at that point. f1 contains the fields of the line from File1, and f2 contains the fields of the line from File2. You can compare ids (e.g. f1[1] and f2[1]) and dates (e.g. f1[2] for create f1[3] for update). and print the lines you want. You can compare against Today for today's date.

Note that you could get fancier and do your date-format normalization within awk by splitting the 3rd and 4th fields on - but I'd find it easier with a sed pass first.

Also note that Today contains both date and time. If you just want to compare the date, you can drop that from the date command specification, but you'll also need to split the csv field to strip the time before comparing.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.