In following attached 748phy.xls file, fifth column is ST_Date, which contains time and dates in this format
22-11-2012 7:54:54 PM in single column
I want it to split in this format either 1st column 22/11/2012 and in second column 7:54:54 PM
Or like this in separate column
1 2 3 4 5 6 7
Date Month Year Hour Min Sec AM/PM
22 11 2012 7 54 54 PM
Whether its possible ? in shell scripting
Thanks in advance.
XLS? Not in a shell script. Either convert it to something sensible like csv, or use perl.
There are command-line tools to convert XLS to CSV. Google for them.
actually I have csv file but forum will not allow you to upload csv file so I had converted into xls and uploaded.
Yes, this can be done in shell scripting. But since you have the data in excel, you can use Format Cells and Text to Columns option to separate these values using a delimiter.
Then post enough of the file (in [code] tags) to show the format.
CSV is just text, you can copy-paste some of it into your post to show it.
To answer your first question:-
Where There is a Shell There is a Way - ULF 
Try this:-
awk -F, 'NR>1 { split($5,ARR," "); $5=ARR[1]","ARR[2]" "ARR[3]; print; } ' OFS=, file.csv
[LEFT]I have one more file (.txt) in which date format is like this
2012-4-12
2012-3-12
I want to change the format to DD-Month-Year
How can I do this..
[/LEFT]
If your date command support -d option, then you can try:-
DT="2012-4-12"
date -d"$DT" +"%d-%b-%Y"
12-Apr-2012
date -d"$DT" +"%d-%B-%Y"
12-April-2012
awk -F- '
BEGIN { months = "JanFebMarAprMayJunJulAugSepOctNovDec" }
{
year = $1; month = $2; day = $3
printf "%02d-%s-%d\n", day, substr( months, (month - 1) * 3 + 1, 3 ), year
} ' "$file"
this works but it has to read file and has to write to another new file with new date format, anyways, thanks
bipinajith
Then redirect the output to a new file.
