Import Millisecond Time

[Aviatrix]

I have an Excel spreadsheet with a date and time column.  The time is in milliseconds.  I convert this to Diadem time and get a number on the order of 63 trillion; e.g. 63,616,443,804.143 (12/5/2015 at 10:23:24.143 AM).  I have data at a minimum rate of 10 Hz, and sometimes at 100 or 1,000 Hz so it's vital that the Diadem time be imported with the fractional seconds.

I convert the spreadsheet to a tab-delimited text file.  The milliseconds in the time data are still there.  I import the text file into Diadem using the Wizard, and I manually insert    .ff   at the end of the m-d-y hh:mm:ss time format.  I've also tried inserting    :ff   (colon instead of a period); the syntax isn't clear from the Wizard help file.

When I look at the data in Diadem, all of the times have been rounded to the nearest second, so that the time mentioned above now shows up as  6.3616443804E+10 with all of the decimal places truncated.

I'm using Diadem with the November 2015 SP1 on a 32-bit machine running Windows 7.

[Brad_Turpin]

Hi Aviatrix,

Can you please post a version of your file as it appears directly from the acquisition device (I'm not sure whether that's Excel or ASCII)?  From your description, it should be possible to write you a DataPlugin that will load your Date and Time columns into one DateTime channel that contains all the millisecond information, but I won't know for sure until I can see your data file for myself and test the DataPlugin I write with it.

You could also try the following datetime format string in the Wizard:

MM/DD/YYYY hh:mm:ss.fff PP

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[Aviatrix]

Hi Brad,

The data is already in one column for the date and time (63616443804.143).  As I said, I'm already using the m/d/y hh:mm:ss.fff    format in the wizard as I input the data.

The problem is that the milliseconds are stripped out during the import process and all of the times are rounded to the nearest second.  That's the problem I'm trying to solve.  How do I import time and preserve the milliseconds?

Here's a small sample of what I start with:

ARINC Diadem Time	Pressure Altitude
Sec	Ft
63616443804.040	612
63616443804.143	614
63616443804.320	615
63616443804.340	617
63616443804.440	618
63616443804.540	620

This is what I get after importing the above data:

ARINC Diadem Time	Pressure Altitude
Sec	Ft
6.3616443804E10	612
6.3616443804E10	614
6.3616443804E10	615
6.3616443804E10	617
6.3616443804E10	618
6.3616443805E10	620

When I plot the data, it drops out nine of every ten points since the times are identical.   The milliseconds are gone, not just hidden.

As a work-around, I've found that I can copy and paste the date and time values directly from Excel into the View panel in Diadem after I've imported the data.  The milliseconds copy correctly using this method.  But, I have 12 tasks in our data system, and 12 corresponding date and time columns of over 100,000 values each, so this isn't a trivial job.

thanks,

Victoria

[Brad_Turpin]

Hi Victoria,

You actually said ".ff", not ".fff", and there is a difference between "m/d/y hh:mm:ss.fff " and "MM/DD/YYYY hh:mm:ss.fff PP".  When your datetime format has AM/PM in it, the format string has to match exactly.  Normally you can get away with "M/D/YYYY" or "s.ff", but not when there is an AM/PM suffix.  So far all I have to go on is that one datetime you posted in the body of your second to last entry above.  What you posted in your last entry looks like the result in DIAdem, expressed as a DBL, but it doesn't look like a datetime column at all.  If the one datetime you posted in the body of your second to last message above is really what's in your data file, please do try the "MM/DD/YYYY hh:mm:ss.fff PP" format string exactly as I specified it.

If that doesn't work, please post the first few lines of your data file (doesn't have to be real data, but it must be in the exact same format).  I'm still not certain what you're starting with in your data file.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[Aviatrix]

Hi Brad,

At this point, I'm just trying to import the number with the decimals intact.  I change it to time after it's in Diadem.  No matter what format I use to import it as time, it doesn't work, but if I can bring in the number I can deal with it from there.

This is what I'm importing (a small sample from an Excel spreadsheet:): 

ARINC Diadem Time	Pressure Altitude
Sec	Ft
63616443804.040	612
63616443804.143	614
63616443804.320	615
63616443804.340	617
63616443804.440	618
63616443804.540	620

The numbers in the first column are rounded to the nearest unit when they are imported, stripping out the decimal places.   I'm trying to import these numbers with all 14 significant digits.  Instead, I get this:

ARINC Diadem Time	Pressure Altitude
Sec	Ft
6.3616443804E10	612
6.3616443804E10	614
6.3616443804E10	615
6.3616443804E10	617
6.3616443804E10	618
6.3616443805E10	620

Victoria

[Brad_Turpin]

Hi Victoria,

There's no reason that column of (large) decimal numbers should be imported into DIAdem as integers, so there must be something not very obvious about your data file or the Data Plugin you were using.  Can you post a data file for me to try or email me one at brad.turpin@ni.com?

Brad Turpin

DIAdem Product Support Engineer

National Instruments