dar100111 Posted August 22, 2013 Share Posted August 22, 2013 Hey All, I noticed my dates from an excel CSV go into my array as 20130818000000. Is there a way I can convert this number to a date to paste into date format that excel can reckognize? Thank you in advance! Link to comment Share on other sites More sharing options...
Malkey Posted August 22, 2013 Share Posted August 22, 2013 By re-arranging the back-references order in the "replace" parameter of the StringRegExpReplace function, the date format can be manipulated as desired. Local $iDate = 20130818000000 Local $sDate = StringRegExpReplace(String($iDate), "(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})", "\1/\2/\3 \4:\5:\6") ; format "yyy/MM/dd HH:mm:ss" ConsoleWrite($sDate & @LF) $sDate1 = StringRegExpReplace(String($iDate), "(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})", "\3/\2/\1 \4:\5:\6") ; format "MM/dd/yyy HH:mm:ss" ConsoleWrite($sDate1 & @LF) Link to comment Share on other sites More sharing options...
dar100111 Posted August 22, 2013 Author Share Posted August 22, 2013 Thanks for the reply Malkey. So do I need to loop through every instance in my column in the array to do this? Link to comment Share on other sites More sharing options...
Malkey Posted August 23, 2013 Share Posted August 23, 2013 Thanks for the reply Malkey. So do I need to loop through every instance in my column in the array to do this? Yes, a loop is needed to access the data stored in an array. An example:- ;#include <Array.au3> Local $sDate Local $aCsvArr[4][3] = [[20130818143000, 1, 2],[20130819000000, 3, 4],[20130820000000, 5, 6],[20130821000000, 7, 8]] ;_ArrayDisplay($aCsvArr) For $i = 0 To UBound($aCsvArr) - 1 $sDate = StringRegExpReplace(String($aCsvArr[$i][0]), "(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})", "\1/\2/\3 \4:\5:\6") ; format "yyy/MM/dd HH:mm:ss" ;$sDate = StringRegExpReplace(String($aCsvArr[$i][0]), "(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})", "\3/\2/\1 \4:\5:\6") ; format "MM/dd/yyy HH:mm:ss" ConsoleWrite($sDate & @LF) Next Link to comment Share on other sites More sharing options...
dar100111 Posted August 23, 2013 Author Share Posted August 23, 2013 Hey Malkey! Thanks for this. Since I have multiple columns in my array that's read from the sheet how can I set certain columns to do this on with your variable $aCsvArr ? I have a 2d array already. Thanks again. Link to comment Share on other sites More sharing options...
kylomas Posted August 23, 2013 Share Posted August 23, 2013 (edited) dar100111, Look at this notation $aCsvArr[$i][0] That means reference whatever is at row $i and column 1. $i iterates from 0 to the limit of the array. If you want to reference the data at column 2 of each row then it would look like $aCsvArr[$i][1] * - arrays are 0 offset kylomas Edited August 23, 2013 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now