Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Change date format in am/pm in csv files using UNIX
# 15  
Old 07-01-2016
Awesome its working fine Aia thanks a lot.

Could you please post same how can I replace derived value in csv file

Thanks again for your time Smilie
# 16  
Old 07-01-2016
I went back and read your first post and the example you posted could be a problem if it is not an exact representation of your csv file.

Code:
,750,0000000000000000GCJR, ,06/22/2016 14:48:44

Could you confirm that lines in your file contains ONLY lines EXACTLY like this?
If this is not the case, could you post a complete portion of your file, showing real complete lines.
# 17  
Old 07-01-2016
Awesome its working thank a lot Aia.

Could please post how to replace derive output in csv file in shell script

Thanks for your time
# 18  
Old 07-01-2016
Quote:
Originally Posted by Raghureds
Awesome its working thank a lot Aia.

Could please post how to replace derive output in csv file in shell script

Thanks for your time
Code:
perl -pe 's/\r//; s|\d+/\d+/\d{4}\s(\d{2}):\d{2}:\d{2}|$1>12?"$& PM":"$& AM"|e' raghureds.csv

# 19  
Old 07-03-2016
Hi Aia,

It's working but its giving output like 06/22/2016 14:48:44 PM , in need output like 06/22/2016 02:48:44 PM

Thanks in Advance
# 20  
Old 07-03-2016
Hi Raghureds,
I'm not nearly as fluent in perl as Aia, but the following awk code seems to do what you want (and it won't report that times between 12:00:00 and 12:59:59, inclusive, are AM instead of PM).
Code:
awk '
BEGIN {	FS = OFS = ","
}
{	sub(/\r/, "")
	split($NF, d, /[ :]/)
	ampm = (d[2] > 11) ? "PM" : "AM"
	if(d[2] > 12)
		d[2] -= 12
	if(d[2] == "00")
		d[2] = 12
	$NF = sprintf("%s %02d:%s:%s %s", d[1], d[2], d[3], d[4], ampm)
}
1' file.csv

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.

If file.csv contains:
Code:
,750,0000000000000000GCJR, ,06/22/2016 14:48:44
,750,0000000000000000GCJR, ,06/22/2016 12:48:44
,750,0000000000000000GCJR, ,06/22/2016 02:48:44
,750,0000000000000000GCJR, ,06/22/2016 00:48:44

it produces the output:
Code:
,750,0000000000000000GCJR, ,06/22/2016 02:48:44 PM
,750,0000000000000000GCJR, ,06/22/2016 12:48:44 PM
,750,0000000000000000GCJR, ,06/22/2016 02:48:44 AM
,750,0000000000000000GCJR, ,06/22/2016 12:48:44 AM

which, I think, is what you wanted.

Hope this helps...
# 21  
Old 07-03-2016
Quote:
Originally Posted by Raghureds
Hi Aia,

It's working but its giving output like 06/22/2016 14:48:44 PM , in need output like 06/22/2016 02:48:44 PM

Thanks in Advance
Code:
perl -pe 's/\r//; s|(\d{2})((:?:\d{2}){2})|$1==0?"12$2 AM":$1==12?"$& PM":$1>12?sprintf "%02d%s",${1}-12,"$2 PM":"$& AM"|e' raghureds.csv

Last edited by Aia; 07-04-2016 at 03:03 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. Shell Programming and Scripting

Need to change date format in a csv file using awk

Example: Input csv file 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 00245DLS,Sitel Ocala,2014/12/31,18:45,1.00,7.00,0.00,0.00 00245DLS,Sitel... (8 Replies)
Discussion started by: adit
8 Replies

3. 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

4. Shell Programming and Scripting

Date format change in UNIX .dat file

Hi, I need help to convert the date format in .DAT file in unix. I want to convert 10@@|SWIFT MT568 Extract@@|Apr 14 2014 5:47:52:563PM@@|Apr 14 2014 4:33:47:663PM@@||##| into 10@@|SWIFT MT568 Extract@@|04/14/2014/ 5:47:52:563PM@@|04/14/2014 4:33:47:663PM@@||##| Appreciate... (18 Replies)
Discussion started by: karthikengox
18 Replies

5. Shell Programming and Scripting

Change the date and time format in UNIX script.

Hi, I am extracting a date string from the source file like this : 06/05/2014 16:04:00 I want to change it to 05-JUN-14 04.05.00.000000000 PM I basically store the date in a variable. I got solutions to change date in dd-mmm-yyyy format using tr but I guess it works only with the "date"... (8 Replies)
Discussion started by: Varshha
8 Replies

6. UNIX for Dummies Questions & Answers

Rename all Files in a UNIX Directory from one date format to another date format

Hi Unix Gurus, I would like to rename several files in a Unix Directory . The filenames can have more than 1 underscore ( _ ) and the last underscore is always followed by a date in the format mmddyyyy. The Extension of the files can be .txt or .pdf or .xls etc and is case insensitive ie... (1 Reply)
Discussion started by: pchegoor
1 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

Retaining the Unix CSV format in Excel format while exporting

Hi All, I have created a Unix Shell script whch creates a *.csv file and export it to Excel. The problem i am facing is that Users wants one of the AMOUNT field in comma separted values. Example : if the Amount has the value as 3000000 User wants to be in 3,000,000 format. This Amount format... (2 Replies)
Discussion started by: rawat_me01
2 Replies

9. 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

10. Shell Programming and Scripting

How to Change the Format of a Date

Hi All, this is my second post, last post reply was very helpful. I have a data that has date in DD/MM/YYYY (07/11/2008) format i want to replace the backslash by a dot(.) so that my awk script can read it inside the C shell script that i have written. i want to change 07/11/2008 to... (3 Replies)
Discussion started by: asirohi
3 Replies
Login or Register to Ask a Question