reading data from excel (time stamp) file and plot it on x-y chart

[FAGdesign]

i am working on reading data from excel file and plot it on x-y chart. the sample excel file i have attached. the first commmn will be time stamp and next are different analog channels. i want to plot the time stamp on x-axis and and the channels on y-axis. i have tried related examples and posts but i could not reach up to the mark. is there any simple way to do this?

[Michel_Gauvin]

Can you post the code you have tried so far?

[FAGdesign]

here are these two files on which iam working. As i am continuouely working on this, these are the last modified one. And the excel file is as above. i hope this will be enough. Am i working on right track???

[Michel_Gauvin]

Unfortunately I only have LabVIEW 2013 installed on my computer.

[FAGdesign]

i am unable to give time stamp value from 1st column of excel to X-axis of
graph.i have attached the vi in which i have tried 3 different ways along with excel.
somebody kindly look into this and help me to proceed on right option.

[Michel_Gauvin · Accepted solution]

Here's one way of doing it.  I did this very quick, so there's always a better way.  I have convert your *.xlxs to a *.csv file to simplify things.  Make sure you take care of the DST (daylight saving times).

Let me know if you have questions.

(To use the VI below, drag the image to your Desktop and then drag the image from your Desktop to the block diagram of an empty VI)

[Bob_Schor]

Just because a .csv file is displayed with an Excel-looking icon, it does not mean that this is an Excel file!  You are not opening the file in Excel, but rather reading it as a pure text file, which means you (the Programmer) have to parse the TimeStamp values and return some number (seconds since 1 Jan 1904?).

I strongly suspect (and will try to test out this evening) that if you use LabVIEW's Report Generation Toolkit to read these data into Excel and get Excel to give the data back to you, you may have plottable data.

Bob Schor

[Bob_Schor]

Well, I did try.  The problem I'm having is with the LabVIEW TimeStamps, which are stored in a fairly proprietary NI format that doesn't "play well" with Excel's Time format.  The problem of getting data into and out of Excel with the Report Generation Toolkit is actually fairly simple -- a function to open the file, a function to read all of the data into a 2D array, and a function to close it.  Then you do what you want with the array.

It's getting late here, and my brain is a little fried.  I actually did get an idea while composing this Post that may provide a solution -- I'll have to play around a bit.

Are you generating the Data Files using LabVIEW?  Do you need to write Date/Time values, as opposed to "elapsed time" (the difference of two TimeStamps, which I believe is a Dbl -- yep, it's the number of seconds between the two TimeStamps.

Bob Schor

[Bob_Schor]

This turned out to be an exceedingly difficult problem, primarily because (a) I was using Excel ("real" Excel), (b) I was writing Time data, and (c) LabVIEW and Excel handle Time differently (LabVIEW in Seconds, Excel in Days).

However, I came up with a routine that did the following:

1. In a For loop, generated 20 data points, consisting of a TimeStamp, a Random Number, and a String representation of the TimeStamp.  Since Excel does not know how to deal with LabVIEW's "unique" representation of TimeStamps, I converted the TimeStamps to Dbl.  I packaged the TimeStamp + Random number in an array of 2 Dbl, and exported this Array and the String Time to give me a 2D Raw Data (Dbl) array and a 1D Raw Times array.  Since the Report Generation Toolkit routines like to use 2D arrays, I converted the 1D String Array to a 2D array and transposed it so it was a column array.
2. I used a "Wait" function in the For loop to let the points have different times.  To save the "wait", I also made a Diagram Disable/Enable that lets me use a saved output of these data.
3. I opened Excel, and wrote the Raw Data array using Append Table to Report.  I then wrote the String Array using the same Append function, but setting the Starting Position to Row 0, Column 2.
4. I saved the Report as E:\Time Test.xlsx, and did a Dispose Report.
5. There's a "feature" of the Report Generation Toolkit and Excel -- you need to wait a bit after closing Excel before reopening it, so I wait a half-second.
6. Examining the Excel file, the first column consists of numbers like 3511436892.434, the second numbers like 0.91, the third strings like 4/9/2015 11:08.  Note that the first column is the value of the LabVIEW TimeStamp, the number of seconds since 1 Jan 1904.
7. I open the saved E:\Time Test.xlsx, and Excel Get Data to input a Dbl 2D array, saved as Saved Data.  Since I didn't tell it to read only 2 columns, it reads all three, including the column where I wrote the String version of the TimeStamp.  Inspecting this column shows it has numeric values like 42103.464028 -- this is Excel's version of the TimeStamp, days since 1 Jan 1900.
8. I then ask it to read a 2D String array, starting in Column 2.  I extract the first column and call it Saved Times.
9. From the first column of Saved Data (the LabVIEW TimeStamp column), I convert the numbers to TimeStamps and call this Recovered TimeStamps.

This is a lot of work to save and recover LabVIEW TimeStamp information in an Excel Worksheet.  By writing both the numeric and String representation to Excel, you gain the ability to "see" the Time data in the Worksheet, yet preserve the precision in LabVIEW without having to write a function to convert Excel to LabVIEW TimeStamps (I tried doing that several years ago -- it was sufficiently difficult that I decided to not use TimeStamps with Excel).

Bob Schor

[JÞB]

http://forums.ni.com/t5/LabVIEW-Idea-Exchange/Promote-Functions-on-Timing-palatte/idi-p/1672870

A great Idea that will never be implemented

https://decibel.ni.com/content/docs/DOC-19689

Functions to convert timestamps to or from Excel epoch

Caution: the vi.s were developed in CST US  you may need to adjust the timestamp constant on the BD for your local zone  (Timestamps don't travel well and the epoch for Excel is LOCAL!