LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

UNABLE TO READ DATE FORMAT(DD/MM/YY) FROM EXCEL

HI...

 i have written a code to read excel sheet.my problem is my program is working fine but in the first column i am having dates in dd/mm/yy format....This column is not getting value when the vi is running.Rest all the columns show the correct data except the date column...i have also changed the seperator and used '-' ,'.' but no use....

 

sometimes it gives some random number there in 1st colum of array where i am storing the data after reading...i am not understanding why this is happening...

 

pls can any1help me out.........

0 Kudos
Message 1 of 23
(10,791 Views)

A bit more info is needed before we can give you definitive answers

 

1) What format is the "Excel" file. Is it in excel 'binary' xls format, or text (csv, txt)?

2) What are you doing to read the file? (read from spreadsheet file? ActiveX? something else?)

3) Can you give an example of a "random" number and the corresponding expected date?

 

That should get us started and reduce the number of guesses we have to make.

 

Rod.

 

0 Kudos
Message 2 of 23
(10,783 Views)
 
Download All
0 Kudos
Message 3 of 23
(10,778 Views)

^^^ 

i am reading data in text format from excel....problem is except date column all is working fine.

 

for eg:if my date is 12/7/09 in sheet then when the sheet is read i am getting some random number like 41375 in the column of array...

 

i have attached my program n  excel sheet in previous post...

 

pls tell me where i am commiting mistake?

 

 

 

0 Kudos
Message 4 of 23
(10,774 Views)

I can't read the VI, so I looked at the Excel file. The formatting in the first column is suspect as different rows ar formatted differently. Try expanding the width to see the values and you'll see....

 

Rows 2, 4 and 6 are formatted as a date, with the actual content being the excel number for the date: 40155, 39941 and 40002.

 

Rows 3 and 5 appear to contain the txt represention of the date.

 

You'll see a difference if you set all rows to the "general" fomat. It may or may not be of consequence, but in the view of the dates immediately after the file is opened, they also differ in that rows 3 and 6 have the month a single digit (without a leading zero) and the others show a 2-digit month

 

I can't tell if this is the cause of the difference as my firewall prevents the LabVIEW from being downloaded.

 

Rod.

 

0 Kudos
Message 5 of 23
(10,769 Views)

EDIT to my previous mesage, too late to edit the post...

 

The groupings are rows 2, 4, 5 and 3, 6.

 

PS you said for 12/7/09 you get a number "like" 41375. Did you mean that in the specific case of 12 July 2009 you get exactly 41375? If this is the Excel representation of the date, I would expect 40006.

 

Could you give two or three more examples of your date-number pairs to see if there's a pattern to them?

 

Rod.

 

 

 

0 Kudos
Message 6 of 23
(10,757 Views)
I looked through your VI and Excel file. You are haveing a problem because of the way that you are using the date in Excel. You are using the day month year. That is not a format that excel understands. I would reccomend that you put a "'" sign inside the field to make it text and you can do what ever you want to. See the attached file.
Tim
GHSP
0 Kudos
Message 7 of 23
(10,743 Views)

Excel is perfectly capable showing (and correctly dealing with) the date in the standard dd/mm/yyyy format. Internally it converts the date to the number of days since the Epoch. All it needs is for the PC to have it's locale set to have the correct format for the date.

 

The problem of mis-interpretation of day and month can arise if the PC's locale sets the date format to mm/dd/yy, which some other countries, including the USA, use.

 

 

Rod.

 

0 Kudos
Message 8 of 23
(10,734 Views)
First of all I did not say Excel was not capable of reading it just not they way he currently has it set up. Second even if he get this thing to work with dates that is when he is having problems. If he chooses to use dates then there will be more work in converting back and forth between LabVIEW and Excel. Again not impossible. You can sotre the date as a string and this will make you life easy unless you want to do all of the conversions. If you just add the ' symbloe before your date field that will convert it into a string and your VI reads it fine.
Tim
GHSP
0 Kudos
Message 9 of 23
(10,724 Views)

I'm going to take a guess and say the day/month thing is a typo.

In Excel when a cell is formatted to date in windows (By Default) it starts from Jan 1 1900 as day 1. Mac uses Jan 1 1904 as Does LabVIEW.

Formatting to date in Excel calculates number of days since 1900, 12/08/2009 is equal 40155 days. LabVIEW calculates seconds since 1904.

If the formatting in the spreadsheet cannot be changed because the data is already there then you will need to convert the number in LabVIEW by pulling out the value, subtract 4 years of days, then multiply by seconds in a day. This will give you a time LabVIEW can use. get the date and then insert the data back into the array.

See .png

Convert Excel Date.PNG

 

 

 

Message 10 of 23
(10,714 Views)