nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Siemens Community Catalyst Program

The Siemens Community Catalyst program was co-created with our community to acknowledge technology leaders who consistently contribute to the Siemens Community. Nominations are accepted on a rolling basis.

Nominate Now

"CSV Import Date-Time formatting problem"

hughesfleming

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!

Quick Links

Upcoming Events

Advancing power electronics design with Simcenter PSIM and High-Performance Computing
Join us for a community conversation on how High-Performance Computing (HPC) and Simcenter PSIM work together to meet the increasing demands of modern power electronics simulation. During this one-hour technical session, you'll discover how leveraging HPC can speed up simulations, improve scalability, and empower engineers…
Getting Started with Faster Motor Building in Simcenter Simlab
Join us for a community conversation on accelerating your motor simulation workflow with Simcenter Simlab. In this beginner-friendly session, we'll introduce the platform's automated model-building capabilities and show how easy it is to set up your first electric motor analysis. You'll see the basic steps of geometry…
Practical hybrid solver workflows in Simcenter Feko
Join us for a Community Conversation where we'll discuss practical approaches to analyzing complex electromagnetic models using hybrid solver techniques. This session will provide a general overview of how different computational methods can be combined within a single simulation to address models containing varied…

View all community events