Importing numbers as timestamp

[afujita]

Hi,

 

I am trying to import a csv file and one column, supposed to be a timestamp column, contain only numbers, without any spaces, forward slashes, dashes and colons.

 

Here is a sample:

20131025191900

 

I want to covert this number into something like 2013/13/25 19:19:00

 

Tried the GetAsTimestamp function, but it did not work.

 

Will appreciate a lot if somebody has a sollution for my issue.

 

Thanks in advance.

 

Alex

[LaRetta]

Hi Alex,

 

What is your timestamp format for your OS?  Unless it is YYYY/MM/DD, your field data cannot be this format.  Can we also assume that months and days with only a single digit are padded so the field ALWAYS contains 14 characters?  Once we pin this down, we can help you produce a true timestamp result.

 

And welcome to FMForums!!  :-)

[comment]

Here is a sample:

20131025191900

 

I want to covert this number into something like 2013/13/25 19:19:00

 

I think you mean 2013/10/25 19:19:00. Anyway, you need to parse the individual date and time elements out of the imported string, then put them back together =

Let ( [
y = Left ( string ; 4 ) ;
m = Middle ( string ; 5 ; 2 ) ;
d = Middle ( string ; 7 ; 2 ) ;
hh = Middle ( string ; 9 ; 2 ) ;
mm = Middle ( string ; 11 ; 2 ) ;
ss = Right ( string ; 2 )
] ;
Timestamp ( Date ( m ; d ; y ) ; Time ( hh ; mm ; ss ) )
)

[comment]

Can we also assume that months and days with only a single digit are padded so the field ALWAYS contains 14 characters? 

 

Well, I should hope so - otherwise it's pure GIGO.

[afujita]

Hi Laretta, thanks for your reply. My OS, and the original CSV source timestamp format is like yyyy/mm/dd hh:mm:ss, as we are in Japan.

And the I always receive this timestamp data with two digits month and day, like 20130305100500

 

Hi consultant, yes, I mistyped the numbers, should be 2013/10/25 19:19:00. Thank you.

Tried your formula, but it resulted in a sequence of other numbers..

Sorry being new to FM, created a new calculation field with the formula above, replaced the string expression with the field name with the data imported. Is that right?

[comment]

created a new calculation field with the formula above, replaced the string expression with the field name with the data imported. Is that right?

 

Yes, that is exactly right - but you also need to set the calculation's result type to Timestamp. Otherwise you may get a result of 63518325540 - the number of seconds elapsed between 0001/01/01 00:00:00 and 2013/10/25 19:19:00.

[afujita]

Bingo!!

I was not changing the result to Timestamp, leaving it as number.

 

Thank you very much!!