Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to change date format in a csv file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to change date format in a csv file using awk
# 1  
Old 12-10-2014
Need to change date format in a csv file using awk
Example:
Input csv file
Code:
00245DLS,Sitel Ocala,12/31/2014,18:45,1.00,7.00,0.00,0.00
00245DLS,Sitel Ocala,12/31/2014,19:00,-1.00,-1.00,-1.00,-1.00
00245HB,Charlotte,01/01/2015,00:00,-1.00,-1.00,-1.00,0.00

Output csv file
Code:
00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00
00245DLS,Sitel Ocala,2014/12/31,19:00,-1.00,-1.00,-1.00,-1.00
00245HB,Charlotte,2015/01/01,00:00,-1.00,-1.00,-1.00,0.00

i was able to do it as different field using this
Code:
cut -d',' -f3 test_tgt.csv | awk -F"/" '{ print $3"/"$1"/"$2 }'

Result:
Code:
2014/12/31
2014/12/31
2015/01/01

Please help..!!!
Last edited by Scrutinizer; 12-10-2014 at 02:16 PM.. Reason: code tags
# 2  
Old 12-10-2014
Try
Code:
awk -F, '{ $3 = substr($3,7,4)"/"substr($3,1,2)"/"substr($3,4,2); print }' OFS=, input.csv

This User Gave Thanks to junior-helper For This Post:
adit
# 3  
Old 12-10-2014
try

Code:
awk -F, '{split($3,a,"/"); $3=a[3]"/"a[1]"/"a[2]}1' OFS=,  input.csv

This User Gave Thanks to senhia83 For This Post:
adit
# 4  
Old 12-10-2014
Alternatively (if non-date fields have no slashes):
Code:
sed 's|\([^,]*\)/\([^,]*\)|\2/\1|g' file

This User Gave Thanks to Scrutinizer For This Post:
adit
# 5  
Old 12-10-2014
If you have gawk, this might help you

Code:
 awk '{print gensub(/(..)\/(..)\/(....)/,"\\3/\\1/\\2","",$0)}' infile

Code:
awk 'NF{$3=gensub(/(..)\/(..)\/(....)/,"\\3/\\1/\\2","",$3)}1' FS=, OFS=, infile

This User Gave Thanks to Akshay Hegde For This Post:
adit
# 6  
Old 12-11-2014
Thanks.. Everyone.. All worked..!!

---------- Post updated at 08:48 PM ---------- Previous update was at 06:37 PM ----------

i was trying to sort this data but 2015 goes in the top as sort considers it as a string

ex:
Code:
00245HB,Charlotte,01/01/2015,00:30,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,01/01/2015,00:45,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/01/2014,00:00,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:15,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:30,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/02/2014,00:45,-1.00,-1.00,-1.00,0.00
00245HB,Charlotte,12/03/2014,00:00,-1.00,-1.00,-1.00,0.00

can any help me with this.
used number of sort options but could not get it right.

---------- Post updated at 08:49 PM ---------- Previous update was at 08:48 PM ----------

Code:
sort -u -o test.csv new_test.csv

was working till 2015 data started coming in.

---------- Post updated at 08:52 PM ---------- Previous update was at 08:49 PM ----------

so thought of changing date format to yyyy/mm/dd.
Any better idea?
Last edited by Scrutinizer; 12-11-2014 at 01:40 PM.. Reason: code tags
# 7  
Old 12-11-2014
Try:
Code:
sort -t, -k1,2 -k3.7,3.10 -k3.1,3.6 -k4 file

This User Gave Thanks to Scrutinizer For This Post:
adit
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Change date format in a file.

Hi all, I have a file as below, i would like the change the format of the time from "11/7/2019 20:12" to "2019-07-11 20:12:00" in the last coloumn. any awk solution on this. Input: 2,0,695016,1961612,497212,5800804,0,0,161,33,7605,12226,23,10,66,0,0,34,11/7/2019 20:10... (4 Replies)
Discussion started by: Raghuram717
4 Replies

2. UNIX for Beginners Questions & Answers

Date format conversion how to change this from using nawk to awk

Hi, I have a file where I need to change the date format on the nth field from DD-MM-YYYY to YYYY-MM-DD so I can accurately sort the record by dates From regex - Use sed or awk to fix date format - Stack Overflow, I found an example using nawk. Test run as below: $: cat xyz.txt A ... (2 Replies)
Discussion started by: newbie_01
2 Replies

3. Shell Programming and Scripting

Change date format in am/pm in csv files using UNIX

Hi All, I'm new to forum good to hear all. I stuck in converting date format in csv file using unix csv file contains as below ,750,0000000000000000GCJR, ,06/22/2016 14:48:44 I want to convert into as below ,750,0000000000000000GCJR, ,06/22/2016 02:48:44 PM Please reply asap..... (22 Replies)
Discussion started by: Raghureds
22 Replies

4. Shell Programming and Scripting

Date format change in a csv file

Hi, We have csv file where date is coming in MM/DD/YYYY HH:MM:SS (06/23/2015 20:59:12) in multiple places But we need to change the date format to DD/Mon/YYYY HH:MM:SS (23/Jul/2015 20:59:12) using shell script. Please let us know how can we achieve the same. (16 Replies)
Discussion started by: dholea
16 Replies

5. Shell Programming and Scripting

Datestamp format 2nd change in csv file (awk or sed)

I have a csv file formatted like this: 2014-08-21 18:06:26,A,B,12345,123,C,1232,26/08/14 18:07and I'm trying to change it to MM/DD/YYYY HH:MM for both occurances. I have got this: awk -F, 'NR <=1 {print;next}{"date +%d/%m/%Y\" \"%H:%m -d\""$1 "\""| getline dte;$1=dte}1' OFS="," test.csvThis... (6 Replies)
Discussion started by: say170
6 Replies

6. Shell Programming and Scripting

awk - change date format

I have below date format in a CSV file. (dd/mm/yyyy) Ex Input: 9/8/2013 Need to convert it into below format (yyyymmdd ) and redirect to new file. Ex Output: 20130809 How do I use awk here to change the format and if leading 0 (zero) is not then add it. Please help. Thanks. (8 Replies)
Discussion started by: vegasluxor
8 Replies

7. Shell Programming and Scripting

Changing date format in CSV file

I have a CSV file with a date format like this; 11/19/2012 17:37:00,1.372,121.6 11/19/2012 17:38:00,0.743,121.6 Want to change the time stamp to seconds after 1970 so I can get the data in rrdtool. For anyone interested, this is data from a TED5000 unit and is Kwatts and volts. Needs to... (3 Replies)
Discussion started by: ottsm
3 Replies

8. Shell Programming and Scripting

File date format how to change

Hi All, Below are the unix files taken by the help of ls -lrt -rw-r--r-- 1 kbehera Domain Users 293 Jul 27 13:33 sand.txt -rw-r--r-- 1 kbehera Domain Users 4 Jul 27 13:37 sand1.txt -rw-r--r-- 1 kbehera Domain Users 293 Jul 27 15:30 new_sand.txt -rw-r--r-- 1 kbehera Domain Users 0 Jul 27... (2 Replies)
Discussion started by: krupasindhu18
2 Replies

9. Shell Programming and Scripting

How to change date format in file

Hello! I have a textfile that look like this: "83d1:46:2b";"20091008190000";"Rögle BK - Skellefteå";"Swedish" "d4c:46:21";"20091008190000";"Södertälje - Brynäs";"Swedish" "d4b:46:2";"20091008190000";"HV 71 - Färjestad";"Swedish" "838:46:b";"20091010160000";"Skellefteå - HV 71";"Swedish"... (2 Replies)
Discussion started by: condmaster
2 Replies

10. Shell Programming and Scripting

Format a date in a csv file

So I have a csv file where the 3rd field is a date string in the format yyyy-mm-dd. I need to change it to mm/dd/yyyy. So each line in the csv file looks like: StringData,StringData,2009-02-17,12.345,StringData StringData,StringData,2009-02-16,65.789,StringData Any idea how I can keep... (5 Replies)
Discussion started by: rpiller
5 Replies
Login or Register to Ask a Question