ASCII Dataset Date-Time string format?
Author: warren631
Creation Date: 10/14/2008 5:57 PM
profile picture

warren631

#1
What date-time format for importing an ASCII data file with a date-time string of "3/27/2008 12:08:00 PM"?
I have tried every combination including 'M/d/yyyy h:m:ss tt' but WLP5.1 doesn't like any so far (US english).

The data example (open, high,low,close,volume,date-time):

44.3400001525879,44.3699989318848,44.3400001525879,44.3600006103516,85675,"3/27/2008 12:08:00 PM"
44.3698997497559,44.3699989318848,44.3600006103516,44.3600006103516,47826,"3/27/2008 12:09:00 PM"
44.3699989318848,44.3699989318848,44.3499984741211,44.3499984741211,36243,"3/27/2008 12:10:00 PM"

Thanks in advance.
profile picture

Cone

#2
You should not add a "Time" field. Your Date and Time are in a single field, so you should delete the Time field and select M/d/yyyy hh:mm tt as the Date field, making sure that the field order is correct (Date should be moved to the bottom).

That said, your ASCII export is extremely inefficient. It's seems that you've exported single-precision numbers without paying attention that only 7 digits are significant. In other words, using a format string of '0.0#####' could cut the size of your ASCII files in half, which should have the effect of faster-loading data.
profile picture

Eugene

#3
Warren,

The forum currently does not support images, so let's drop a couple of external links. Here is how you need to set up the data importing dialog with the current ASCII provider version 1.2.2:

ASCII provider configuration

And this is how the parsed data should look like:

Result

The catch here is obviously this part of your data: "3/27/2008 12:08:00 PM". It contains an extra separator but the ASCII provider is able to handle them.

P.S. Note that in the upcoming version 1.2.3 and higher, you'll need to get rid of the Filler field.
profile picture

warren631

#4
Thanks Cone and Eugene, that worked fine for that file format.

But now I have a slightly different custom ASCII format file and I can't figure out the format string. It looks similar to the first one:

"9/25/2008 3:38:53 PM",41.6768815886247,41.6707526354499,41.6768815886247,2000,188,7948
"9/25/2008 3:38:58 PM",41.6830105417995,41.6768815886247,41.6768815886247,2000,244,7948
"9/25/2008 3:39:01 PM",41.6891394949743,41.6768815886247,41.6830105417995,2000.33333333333,280,4210
"9/25/2008 3:39:05 PM",41.7136553076734,41.7075263544987,41.7136553076734,2000.33333333333,462,4210

The fields are: Date(including time),High,Low,Close,tic,NYSE_TICK,MinuteTICK
profile picture

Eugene

#5
Here is how you need to set this up:

ASCII provider config #2

Notice the two Filler fields. tic,NYSE_TICK,MinuteTICK are custom fields.

P.S. Soon the ASCII provider is going to get a version upgrade, and handling those inconsistent separators will be simplified. When it happens, you'll need to delete the Filler fields altogether.