Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

"CSV Import Date-Time formatting problem"

Hi,

I have a question about how to format time when I import a csv consisting of intraday forex data. I have duplicated the neuralmarket trends tutorial 10 using forex data exported from metatrader. Everything works for daily data when I import using yyyy-MM-dd and deselect time. Now I am trying to import four hour data with date,time,input,input,input,input as the header but when I format time as hh:mm:ss I get an error. Also at this point is there a way to combine date and time into one column and assign an id to that combination? I have looked at the documentation with regards to formatting date and time in java but I can't seem to get this to work.

Many thanks,

Alex

Find more posts tagged with

AI Studio

CSV

Accepted answers

All comments

haddock

Hi there Alex,

If you check out the Date to Numerical operator you'll see that it

Transforms date values into numerical ones capturing the milliseconds since 01/01/1970 00:00:00 GMT.

So... you could try getting the millseconds for the Date ( nasty calculation ) and then adding the appropriate for your time portion, finally you can convert back to the Date format to unify things. At least in some parallel universe perhaps.

Timestamps are a pain, and one of the reasons I use SQL servers.

Hope that helps

hughesfleming

Thanks Haddock,

I am surprised that this is not straight forward. I may try exporting the csv in a different way. I am running an indicator that exports training data and then out of sample data to two files. The out of sample file gets updated more or less in realtime and then the two files are imported into rapidminer. I could probably do the same thing with mysql if that would make life simpler but that adds another layer that I am hoping to avoid.

regards,

Alex

hughesfleming

I am still running into trouble here with the csv import wizard. yyyy-MM-dd is turning into Mon Jan 05 00:00:00 CET 2009, My time column is set to time in the format of 08:00 and I still get the error unparseable date(08:00). If I add hh:mm or HH:mm I get unparseable date (2009-01-02). If I switch off the time column and use daily data, everything works so something about the time column is causing problems.

Can I just ignore this and drop date and time all together and just bring in the numeric data?

Alex

hughesfleming

Ok. I have converted the csv to excel. Imported the date as date, time as polynomial and added a Nominal to date to fix the time as hh:mm:ss. Is there a way now to join date and time and create an id? It seems that setting the date with yyyy-MM-dd while importing a csv produces different results than importing the same format with excel.

Alex

haddock

Hi again,

For future reference you may find this page useful, it lays out the Java datetime format strings http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html.

To try and emulate your problem I set up a text file with the contents of an SQL timestamp, like this..

datetime
2011-01-03 01:06:00.000;

and was able to import it as a datetime, like this..

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.1.004">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.1.004" expanded="true" name="Process">
    <process expanded="true" height="400" width="878">
      <operator activated="true" class="read_csv" compatibility="5.1.004" expanded="true" height="60" name="Read CSV" width="90" x="45" y="30">
        <parameter key="csv_file" value="C:\FIMData\date.csv"/>
        <parameter key="date_format" value="yyyy-MM-dd HH:mm:ss.SSS"/>
        <parameter key="first_row_as_names" value="false"/>
        <list key="annotations">
          <parameter key="0" value="Name"/>
        </list>
        <list key="data_set_meta_data_information">
          <parameter key="0" value="datetime.true.date_time.attribute"/>
        </list>
      </operator>
      <connect from_op="Read CSV" from_port="output" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

Note the '.SSS' for milliseconds in the format string.

If you want to join date and time stamps I think you need to sum their milliseconds and convert back to a datetime, as I explained before.

hughesfleming

Thanks so much!

I modified the exporter to follow your example and everything is working.

Kind regards,

Alex

haddock

Molto Bene!