top speed date storage format

[mshula]

Hi all,

I am extracting data from .tps files to mysql. I used TopSpeed scanner to
export files to CSV and then to mysql. The date fields appear to be
numbers or in a format I don't recognise eg 72,473.

Does any one know how to convert that to a format I can recognise?
Is there an easier way to move data from topspeed to mysql as I have a lot
of tables to work with?

Thanx

[Paul Attryde]

Post by mshula
Hi all,
I am extracting data from .tps files to mysql. I used TopSpeed scanner to
export files to CSV and then to mysql. The date fields appear to be
numbers or in a format I don't recognise eg 72,473.
Does any one know how to convert that to a format I can recognise?
Is there an easier way to move data from topspeed to mysql as I have a lot
of tables to work with?

A Clarion standard date is the number of days since 28th December 1800.
The range of dates is from 1 Jan 1801 (value of 4) to 31 December 9999
(value of 2,994,626)

If you've got a TPS file then that also implies you have a copy of Clarion.
If you do, think about using the FORMAT() command using a date picture like
@D17

HTH,
--
Paul
www.attryde.com/clarion

[Jim in AZ]

Post by Paul Attryde

Post by mshula
Hi all,
I am extracting data from .tps files to mysql. I used TopSpeed scanner to
export files to CSV and then to mysql. The date fields appear to be
numbers or in a format I don't recognise eg 72,473.
Does any one know how to convert that to a format I can recognise?
Is there an easier way to move data from topspeed to mysql as I have a lot
of tables to work with?

A Clarion standard date is the number of days since 28th December 1800.
The range of dates is from 1 Jan 1801 (value of 4) to 31 December 9999
(value of 2,994,626)
If you've got a TPS file then that also implies you have a copy of Clarion.
If you do, think about using the FORMAT() command using a date picture like
@D17

iirc, it is referred to as the Julian Date.

Jim

[mshula]

thanks guys found an easy solution to my first problem.

If you have a CSV file or other data that contains an unformatted Clarion
date, and you wish to view the date in Excel, you'll need to convert the
Clarion standard date to an Excel date.

Fortunately, that's easy to do. Just create a formula in another column,
subtracting 36161 from the value and formatting it as a date. Clarion
standard dates and the Excel DATE() function both have an arbitrary "Day
1" - for Excel, this is 1/1/1900, while Clarion's Day 1 is 12/28/1800
(although the first usable date is Day 4, or 1/1/1801). The number 36161
is the difference in days between these two dates.

[mshula]

Thanx Paul I only got a copy of clarion today; I am still not familiar with
clarion. I am currently trying to have an application that was writing in
clarion 5.5 to work with my application.

The .tps files r of a running application. Do you know of any other way to
convert to a normal format other than clarion it self.

[Paul Attryde]

Post by mshula
Thanx Paul I only got a copy of clarion today; I am still not familiar with
clarion. I am currently trying to have an application that was writing in
clarion 5.5 to work with my application.
The .tps files r of a running application. Do you know of any other way to
convert to a normal format other than clarion it self.

You can edit TPS files directly with Topscan - it should be in your
\clarion55\bin folder
I wouldn't necessarily recommend editing the file, but it should allow you
to export the data to a text file

--
Paul