10-05-2009 05:28 AM
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.........
10-05-2009 06:02 AM
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.
10-05-2009 06:24 AM
10-05-2009 06:28 AM
^^^
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?
10-05-2009 06:44 AM
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.
10-05-2009 07:31 AM
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.
10-05-2009 08:21 AM
10-05-2009 08:55 AM
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.
10-05-2009 09:06 AM
10-05-2009 09:21 AM
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