Loadwave -> custom datetime

Share icon

I have been able to import various date / time formats over time, but my newest strange time stamp format is:

Column 1,"HH:MM:SS DD.MM.YYYY,Column 3

For instance:

blank,"21:40:12 01.01.2017,blank,...
blank,"21:40:13 01.01.2017,blank,...
blank,"21:40:14 01.01.2017,blank,...

I have been trying, without success: (comma delimeted, ignoring the \ char.)

LoadWave/A/J/D/O/E=0/V={",","\"",0,0}/B="N='_skip_';F=8,N=TS2_a_temp;N='_skip_';N='_skip_';N='_skip_';N='_skip_';N='_skip_';"/R={English,2,2,2,2,"DayOfMonth.Month.Year",40} path

If I set F=6 instead, I can get the correct time of day from that column, but the date still resists me, reporting 01/01/04. Setting F=7 results in NaN being imported. Could this have someting to do with the fact that the time comes before the date? Am I going to have to build a function to digest this as text and then ferret out the contents?

Hoping I just missed something simple in a few hours of reading the manual and troubleshooting...
 sample.txt.zip (939 bytes)
If you will attach a zip archive of a sample file, I will see what I can figure out.

November 30, 2017 at 05:51 am - Permalink

Much appreciated. I attached a sample of the data to my previous post.


In the meantime, I Tried this method: http://www.igorexchange.com/node/2484

and I can process the string that gets imported and convert it to a datetime in seconds, but I feel like loadwave should have handled this correctly and that I'm doing something wrong. As noted before, I've successfully used loadwave's custom date formats with other inputs, but this time, it's failing.

edit:
I should note that I can already handle the *first* timestamp in the row; it's the second one I can't get - and I need both to compare them for timing purposes.

November 30, 2017 at 07:10 am - Permalink

This seems to do the trick. It assumes the existence of a symbolic path named MTaylor:
Function Load()
    String columnInfoStr = ""
    columnInfoStr += "N=DT,F=8;"                // Date/Time in YYYY-MM-DD<space>HH:MM:SS format
    columnInfoStr += "N='_skip_',F=-2;" // Skip <double-quote>HH:MM:SS<space>DD.MM.YYYY and treat as text
    columnInfoStr += "N=wave0;"             // First numeric column
    columnInfoStr += "N=wave1;"             // Second numeric column
    columnInfoStr += "N=wave2;"             // Third numeric column
    columnInfoStr += "N=wave3;"             // Fourth numeric column
    columnInfoStr += "N=wave4;"             // Fifth numeric column
    LoadWave/J/D/P=MTaylor/O/K=0/R={English,2,2,2,2,"Year-Month-DayOfMonth",40}/B=columnInfoStr "sample.txt"
End

November 30, 2017 at 07:16 am - Permalink

Quote:
I should note that I can already handle the *first* timestamp in the row; it's the second one I can't get - and I need both to compare them for timing purposes.


That changes everything. I will see what I can do.

BTW, there is an extraneous double-quote in the data that does not seem to make much sense.

If you have control over the file format, change it to remove the extraneous double-quote and to write both date/times using the same format with the date first.

November 30, 2017 at 07:18 am - Permalink

There is no straight-forward way to load your file as it stands using LoadWave. The main problem is that Igor expects date/time data to be <date><space><time> but your second date/time data has the time before the date.

You could work around that by loading the time and date as separate columns and then combining them, but this is complicated by the fact that, in order to load the first date/time, space must not be a delimiter but in order to load the time/date, space must be a delimiter. These are contradictory requirements.

The presence of the extraneous double-quote also complicates matters.

If possible, I would change the code that generates the file to omit the extraneous double-quote and to use the YYYY-MM-DD<space>HH:MM:SS format for both date/times.

If you can not change the code that generates the file, I would take the approach of creating a cleaned-up version of the file and loading that. Here is an example: http://www.igorexchange.com/node/2607

Here is another example: http://www.igorexchange.com/node/856

I think I would make it so that the cleaned-up version uses the format that I recommended above. Having done that, you could load the data into Igor with this:
Function Load2()
    String columnInfoStr = ""
    columnInfoStr += "N=DT1,F=8;"           // Date/Time in YYYY-MM-DD<space>HH:MM:SS format
    columnInfoStr += "N=DT2,F=8;"           // Date/Time in YYYY-MM-DD<space>HH:MM:SS format
    columnInfoStr += "N=wave0;"             // First numeric column
    columnInfoStr += "N=wave1;"             // Second numeric column
    columnInfoStr += "N=wave2;"             // Third numeric column
    columnInfoStr += "N=wave3;"             // Fourth numeric column
    columnInfoStr += "N=wave4;"             // Fifth numeric column
    LoadWave/J/D/P=MTaylor/O/K=0/R={English,2,2,2,2,"Year-Month-DayOfMonth",40}/B=columnInfoStr "sample.txt"
End

November 30, 2017 at 07:30 am - Permalink

Thanks for your help!

Unfortunately, I do not have control over the input format.
I ended up using the sscanf method mentioned earlier by first storing teh data as a text wave, and then converting it to a double using a wave assignment command, followed with setscale to make it a "dat". All is working now :)

Cheers

November 30, 2017 at 08:45 am - Permalink