Read excel date and time and plot with timestamp in x-axis

[has64]

Hi,

I just start learning LabVIEW and any help would be highly appreciated for my current problem. I have excel file attached. I would like to plot column 'M' on the y-axis with the corresponding time and date on column 1 and 2 on x-axis.
I also would like to have two plots of average 'M' corresponding to weekday and weekend separately.

Thank you for your help in advance. 

[PalanivelThiruvenkadam]

If you are very new to LabVIEW. First try to go through LabVIEW Basics.

---

@has64 I have excel file attached. I would like to plot column 'M' on the y-axis with the corresponding time and date on column 1 and 2 on x-axis.
1. Try to read the excel file, use Report Generation Toolkit if you have already installed else go with Excel activex for reading Excel File
2. Use the read values to plot graph

---

[Bob_Schor]

There have been a number of posts (some as recently as the last two weeks) discussing the issue with TimeStamps, as Excel and LabVIEW use different schemes to encode date and time.  This might be part of your problem ...

Bob Schor

[has64]

---

@PalanivelThiruvenkadam wrote:

If you are very new to LabVIEW. First try to go through LabVIEW Basics.

---

@has64 I have excel file attached. I would like to plot column 'M' on the y-axis with the corresponding time and date on column 1 and 2 on x-axis.
1. Try to read the excel file, use Report Generation Toolkit if you have already installed else go with Excel activex for reading Excel File
2. Use the read values to plot graph

---

---

Thank you Palanivel. Could you please let me know what block of Report Generation Toolkit I should apply? 

[has64]

Thank you, Bob, 

It seems that are some discussions, but I can't find a case applicable to my case. Could you please help me to find.

[Bob_Schor]

Well, while writing to an Excel Workbook might be not entirely unreasonable for a LabVIEW beginner, reading from Excel, particularly when you need to consider the format of the Excel WorkSheet, is a little more complicated and complex.  It takes a bit of "fussing around" (I've been noodling on this for about an hour) and is not for the faint of heart.

It is always a good idea to "look at the data" and decide what you want to do with it.  Are all of the rows and columns exactly the same Data Type (i.e. all Dbls, all Strings)?  Are any of the Rows or Columns "Header" material (which you may not want to include in the Read)?  Are any of the Rows or Columns Date or Time information (which are really numeric quantities, but expressed in a peculiar Excel encoding)?

Once you know the answers to these questions, divide your WorkSheet into "regions" of similar data types, only including the data you want to read (i.e. ignore headers if you don't intend to read them).  Decide on the "boundaries" (the top-left Cell and bottom-right Cell) of each Region.  With this information, and the knowledge of the Data Type you want to read (String or Dbl), proceed to read (and process) the data.

[To be sung to the tune of "It's My Party ..."]

"It's your Data, and it will Parse if you want (it) to, Parse if you want to, Parse if you want to.  You'll Parse it True, if you LabVIEW it, too."

So, what are the Regions you want to read?  What Type, and what Boundaries?  When you figure this out, we can talk about actually getting the Data into LabVIEW and using it to do something useful (like plot some data).

Incidentally, what do you want for the X axis?  I'm assuming you want "Elapsed Minutes", not "Date/Time" (who cares if this point was collected at noon on 1 Apr 2018 -- it is more important that it was halfway through the third day ...).

Bob Schor

[has64]

Thank you Bob for your time. You are right.

Let me ask the question in this way: How can I plot 'N' (which has a resolution of one minute) in a single plot with timestamp (HH:MM) in x-axis (date is not important anymore). I have attached a new excel file. 

Thank you guys for your time and help in advance. 

[Bob_Schor]

Well, for one thing, you need to be very careful about your Excel file.  The file you attached, I'm guessing, is a copy of the original 7-column, 90K row file where you cleared (not deleted) the rows after Day 1.  When LabVIEW asks Excel to read data, it doesn't know how much data there are.  You can tell it (specify "I want to read N rows") or you can ask it (but then it requires that Excel thinks there are no "blank" cells).

The next thing to worry about is the difference between Excel TimeStamps and LabVIEW TimeStamps.  LabVIEW saves seconds since the Epoch Date (1 Jan 1904), while Excel saves days since its Epoch Date (1 Jan 1900, or 31 Dec 1899).

There are "interesting" issues with dealing with Time (as a LabVIEW TimeStamp variable), as opposed to "Minutes" (an I32, for example).  Here's what I mean:

• Excel shows Minutes as 1:00 (1 minute, 0 seconds).  This is stored (by Excel) as a Float representing Seconds since the Epoch Date.
• You read this with LabVIEW as a String -- 1 minute = 0.019 days.  We can convert it to seconds by multiplying by 60*60*24=86400 and rounding to Integer ...
• If you convert the number of seconds to a TimeStamp, you can now format it as a LabVIEW TimeStamp, but you'll need to "play" a bit with the Format to get it to show 0:01 (as opposed to 31 Dec 1903, 19:01 -- it takes into account your Time Zone).  You are probably better off transforming Seconds into a Cluster of Hours and Minutes (two divisions by 60).  The only problem, of course, is that the Time will be expressed as an Integer, not a formatted TimeStamp (i.e. it will be 100, not 1:40).
• If you really are going to be displaying thousands of points, you probably do not want your Time Axis as "D:H:M" (as it might be unwieldy).  You should think about this and experiment.

Bob Schor

[has64]

Thank you Bob to follow up my problem. 

The explanation is not easy for me to digest as a beginner to LabVIEW. Can you please read the attachment data and plot with minute resolution LabVIEW file?

Thank you again, 

[Bob_Schor]

Do you want the 90K+ version, or the one-day version of the Data plotted?  If the 90K version, do you want all 90K points plotted, or (since your screen resolution can only resolve about 1000 points) only every 100th point (or the average of each 100 points)?  Data Analysis requires a little thought before simply "plowing ahead" ...

Bob Schor