Jump to content
Sign in to follow this  
dar100111

Array Dates from CSV as numbers. Trying to convert.

Recommended Posts

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

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

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

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

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...