Jump to content
Sign in to follow this  
dar100111

Array Dates from CSV as numbers. Trying to convert.

Recommended Posts

dar100111

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!

Share this post


Link to post
Share on other sites
Malkey

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)

Share this post


Link to post
Share on other sites
dar100111

Thanks for the reply Malkey.

So do I need to loop through every instance in my column in the array to do this?

Share this post


Link to post
Share on other sites
Malkey

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

Share this post


Link to post
Share on other sites
dar100111

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.

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×