How to create a csv file with a time format (UTC time) ?

[stephenjgray]

Hi everyone. I am new to thinbasic (which I'm very impressed with), though not new to programming.

With help from the samples I've written myself a program to parse a large .csv file, do a little number crunching and then write out some new files.

One of the new output files has to be in a specific .csv format in order to be loaded correctly into another piece of software.

The csv format required starts with date,time,whatevever,whatever.

I am having trouble with the time format. When I open the .csv (ie in MS Excel) that my thinbasic program creates it shows my time column as type=General, and it shows my times as, for example: 14:30. When I open the .csv that the other piece of software can save itself as an example of the format that it wants to load, then its time column shows as type=Custom, and whilst it shows in the column as, for example: 14:30, in the field at the top of the Excel screen it shows as "2:30:00 PM". So in other words Excel is storing it as some kind of special time format, and this is how it needs to be for the other software to successfully load it.

So my question is: is there a function or something in thinbasic that will allow me to create this second data item in my .csv in this special time format so that the other software can load it?

cheers,
Stephen

[SteveOW]

I suggest you avoid using Excel if you possibly can.

Examine the CSV files in something like TextPad (www.textpad.com) to see what is really inside them.

[phrenetic]

Since you are really talking about formatting rather than UTC times, you should load the DT module and use some of the inbuilt functions to output your time.

Uses "DT"

Dim theCurrentTime = DT_GetTime()
Dim outputTime = DT_TimeFormat(theCurrentTime, "HH:mm")

If you are in the UK and want to adjust the current time back by one hour (to account for BST) before you output it, then you can use:

theCurrentTime = DT_TimeSubSeconds(theCurrentTime, 3600)

Alistair

[ErosOlmi]

First of all: welcome to all of you posted for the first time.

@stephenjgray
Can you please attach an example of the produced .csv files?
The one produced by your thinBasic script and the one produced by the other software?
Just few lines are enough
Thanks

[ErosOlmi]

Another important info: what version of Excel a re you using?

In the meantime I tested a simple .csv using Excel 2010
I manually created the following file:

11:36;aaa;31/12/2013
21:36;bbb;30/12/2013


Opened with Excel 2010 it shows the following:
8351

To me it seems ok.
Anyway I will wait other info.

Cizao
Eros