I have a bunch of text files that are named in YYYYMMDD.Txt format (so today would be 20160420.Txt).

Each file is basically a log that contains a timestamp and and a unique ID, each value is separated by tab delimiters.

So for example, 20160420.Txt has the following values:

DATE        TIME   ID
20160420   0135   123456
20160420   0240   234567
20160420   1252   345678

I need to extract all the Unique IDs present in those files, but only on those files from the last 6 months.

The catch is that I can't use the mtime because all the files were recreated again in the past week (i.e.: the mtime does not correspond with the filename).

Is there any way I can do this with grep/find/sort?

  • All files are in the same directory. I'm using Putty on Win 10 to ssh into Ubuntu Server LTS 14.04 I'm expecting, hopefully something like: uniqueids.txt with a single column listing only the unique ids contained within all the files from the last six months. – listerblack Apr 20 '16 at 18:03

It is not trivial to find the exact date 6 months ago, especially if the current date would be the 31st of some month. But if you know how to do this with find and -mtime, I would just touch the files depending on the date in their name:

for x in *.Txt; do
   touch -t "$dd"0000 "$x"

and then use the mtime

  • Thanks for the input, that looks like a sane approach. Is that bash or a different language? Tried to drop that into a script but it's not recognizing the commands. – listerblack Apr 21 '16 at 1:40
  • That is Bash, as I typed it on the commandline to try it out. Have you put a shebang at the top ( #!/bin/bash ) of your script? – Anthon Apr 21 '16 at 3:10
  • Figured it out! Copy pasting straight from chrome into nano was inserting some junk data that confused bash. Typing out everything by hand into a new file fixed it. This worked perfectly, thank you! A bit of awk and sort magic and I'm almost done. – listerblack Apr 21 '16 at 3:25

Use awk to do the work, and SQLite for the dates.

sqlite3 <<< "select date('2016-04-20', '-6 month');"

Dandy, ain't it? awk has string functions to insert/delete the hyphens SQLite needs and, yea, splits on tab delimiters.

awk 'NR == 1 {next}; { IDS[$3]++ }; END {for (K in IDS) {print K}}' ids

Guaranteed fast, too.

  • 1. Huh? Where does sqlite come into this? The input files are text files. Your answer doesn't even begin to address the most important part of the question, how to restrict the ID extraction to files with date-based filenames <= 6 months old. 2. GNU date can do date calculations anyway: date -d '2016-04-20 -6 months' +'%Y%m%d' or even date -d '6 months ago' +'%Y-%m-%d' – cas Apr 21 '16 at 10:25
  • That's a good answer, cas. I didn't know GNU date could do that. Either way, the answer is to generate the boundary filename, select those names greater than that one, and parse those files. Could be done with SQLite alone, actually, or with awk and anything that supports date arithmetic. Given that information, I'm sure the OP can write the script. – James K. Lowden Apr 21 '16 at 13:28

Following up on @cas's comment to another answer:

six_months=$(date -d "6 months ago" "+%Y%m%d")
for f in *.Txt; do
    [[ $file_date > $six_months ]] && echo "$f"
done |
  xargs awk 'FNR > 1 {print $3}' |
  sort -u > unique_ids_in_last_6_months

The for loop prints out the "eligible" filenames. xargs passes the filenames to awk a batch at a time (minimizing the number of calls to awk). awk discards the header and prints the last column. sort finds the unique IDs for you.

  • why no sqlite? :-) – cas Apr 22 '16 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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