Loadwave -> custom datetime

mtaylor
mtaylor's picture
Posts: 98
Joined: 2010-04-13
Location: United States

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...

AttachmentSize
sample.txt.zip939 bytes

[ last edited November 30, 2017 - 06:52 ]
Posts: 1635
Joined: 2007-06-21
Location: United States

If you will attach a zip archive of a sample file, I will see what I can figure out.


mtaylor
mtaylor's picture
Posts: 98
Joined: 2010-04-13
Location: United States

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.


[ last edited November 30, 2017 - 07:10 ]
Posts: 1635
Joined: 2007-06-21
Location: United States

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


Posts: 1635
Joined: 2007-06-21
Location: United States

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.


Posts: 1635
Joined: 2007-06-21
Location: United States

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


mtaylor
mtaylor's picture
Posts: 98
Joined: 2010-04-13
Location: United States

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


Back to top