Shell Programming and Scripting

I have a file that is exported from a database, I have no control on the raw output from the source system. The file is comma delimited with a header row.

The second field in the file is a time field, from the source system it exports in 12h time in the following format. "h:mm:ss AM" or "hh:mm:ss AM" (of course the AM can also be PM).

So, the problem. I need this file sorted by date and time, the date column is easy, the time column is a problem. The sort command seems to only group all the AM and PM times together, so my thinking was that using a 24 hour time format would do the trick.

I should warn you this is my first shell script, so I may be missing something obvious, but there seems to be a lack of date and time functions in script

Specifics:

This is on an appliance - Redhat ES 4 - most shell stuff is installed, GCC is not.

Shell is bash


The file format....

Code:

Date,Time,Data1,Data2,Data3
M/DD/YYYY,H:MM:SS AM,text string,text string,number
MM/DD/YYYY,HH:MM:SS PM,text string,text string,number

and so on.

I did find the following thread, which gets me close, but I need to put it together with reading and writing from a file.

https://www.unix.com/shell-programmin...onversion.html

Thanks in advance

awk -F, '{gsub(/\//," ",$1); gsub(/:/," ",$2)};{print $1, $2, $3, $4, $5}' file | sort -k1,3n -k7,7 -k4,6n | awk '{printf("%s/%s/%s,%s:%s:%s %s,%s,%s,%s,%s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11);}'

Work on this. You should get idea...only some formatting..

Thanks for the reply. I get what you are doing here and it almost works. It sorts by date and time correctly, however....

In the original post, where my header row indicates "data1, data2, data3," etc.
The third and fourth field in the original comma delimited format (data1 and data2) are variable length text fields with spaces, data3 is numeric. The awk script above puts commas in the spaces in the text fields.

In addition to that, the text string in field four (data2) is one of four values, so a specific date/time combination shows up four times in each file. This means I will also need a sort on field four to keep them grouped.

If I'm thinking clearly, I need to sort on data2, date and time.

awk -F, '{gsub(/\//,"|",$1); gsub(/:/,"|",$2); gsub(/ /,"|",$2);};{print $1,"|",$2,"|",$3,",",$4,",",$5}' file| sort -t"|" -k1,3n -k7,7 -k4,6n | awk -F"|" '{gsub(/\|/,"/",$1); printf("%s/%s/%s,%s:%s:%s %s,%s\n", $1,$2,$3,$4,$5,$6,$7,$8);}'