Using awk to break out a timestamp and format it

Question

For filenames like this:

fileLoad.xml2017-12-21_10_55_53-153.txt
otherFile.xml2017-12-20-11_23_01-87899.txt
someFile.xml2017-11-30-21_00_59-1.txt

What I'm trying to accomplish with awk is to isolate the file name up through .xml and then isolate and format the timestamp for use in a csv/database.

I have the following:

NR==1 {
    fn=substr(FILENAME, 0, FILENAME-5);
    ts=fn;                                                                                                                                                                                                
    sub(/[0-9]{4}.*$/,"",fn);                                                                                      
    sub(/^\w+\.xml/,"",ts);
} {
     printf "fn\tts"
}

I can strip off the '-' from the end, but after that I can't figure out how in awk to convert the remaining timestamp to be formatted as 2017-11-30 21:00:59.

Answer 1

This works, though I wouldn't personally use awk for this, I'd use gawk, which has some significant features that make this exact type operation easy, gensub()..

echo 'fileLoad.xml2017-12-21_10_55_53-153.txt
otherFile.xml2017-12-20-11_23_01-87899.txt
someFile.xml2017-11-30-21_00_59-1.txt' | awk '{
  gsub(/^.*\.xml|-[0-9]+\.txt/,"",$0);
  date=$0; 
  time=$0; 
  sub(/[-_][0-9]{2}_[0-9]{2}_[0-9]{2}$/,"",date); 
  sub(/^[0-9]{4}-[0-9]{2}-[0-9]{2}[-_]/,"",time);
  gsub(/_/,":",time);
  print date " "  time 
}'
2017-12-21 10:55:53
2017-12-20 11:23:01
2017-11-30 21:00:59

Since you specified awk, this is one way to do it, though a bit basic.

Note that due to the absence of gensub, I transferred the $0 value to two holders, so I could strip off the beginning of one, and the end of the other.

Stripping out the initial starters/enders is easy as you can see, and that leaves you just with the date/time data to further process.

Using gawk and gensub it's easier.

echo 'fileLoad.xml2017-12-21_10_55_53-153.txt
otherFile.xml2017-12-20-11_23_01-87899.txt
someFile.xml2017-11-30-21_00_59-1.txt' | gawk '{
  gsub(/^.*\.xml|-[0-9]+\.txt/,"",$0);
  datetime = gensub(/^([0-9-]{10})[-_]([0-9_]{8})$/,"\\1 \\2",1,$0);
  gsub(/_/,":",datetime);
  print datetime 
}'
2017-12-21 10:55:53
2017-12-20 11:23:01
2017-11-30 21:00:59

Note that the start/end patterns do the same thing, one takes the actual pattern and reproduced it, the second just says, give me the first 10 matches to [0-9-] and the last 8 matches to [0-9_], it just depends which is easier to read.

The real question is if you wouldn't have been better off using Perl for this job however.