Using the same format like Excel

[J.Dred]

Hello everybody,

I need to make a convention to a Time Stamp which should be interpreted as number in Excel ( see the below picture,  format cell... number) and save the data to txt or excel file. The Point is I can`t save the raw data to excel , first I need to do a conversion as given.

How can I build this algorithm? and save my chart elements together with their time stamp like:

x1,y1,z1, time-stamp     ( e.g  100,100,300, 37918,87500  >>> means x=100,y=100,z=300 at 24.10.2003 21:00:00,10)

x2,y2,z2, time-stamp     (        101,102,301, 37918,87501  >>> means x=101,y=102,z=301 at 24.10.2003 21:00:00,20)

.....

.....

                                        (         105,100,300, 37918,87581  >>> means x=100,y=100,z=300 at 24.10.2003 21:00:01,00)

...

...x1000000,y1000000,z1000000, time-stamp

p.s: I already found the days which I divided the time passed after 1.1.1904 up to now and divide it by 86400 and round it to next number , this part is done but in excel after the comma is for the time e.g 0,5 means 12:00:00 AM    resp. 0,6  14:24:00 AM  resp. 0,51 means 12:14:24

p.p.s: x1y1z1 to x10y10z10 is 1 seconds , so each row is 100 ms !

p.p.p.s: I could send the date and time as sting to the text file but Excel crashes after few hundred thousand alues , so I want to do the conversion first in Labview and then save already converted values in a txt resp. excel file.

Pls help

pls see the picture below.

[RavensFan]

I think your problem is that you are using a comma has your decimal separator rather than a decimal point ".".

If you are writing out your various values to a text file and the values are comma delimited, then the commas you are using as a decimal point are being confused as a delimiter rather than being an inherent part of the decimal value. I suggest using the standard of a period as the decimal point (but I'm not sure if Excel would still have a problem if your windows settings are using an international setting of comma).  Perhaps wrapping the text part of your time and date in quotes so Excel will import the date and time as a single entity rather than 2-3 entities because of the comma embedded in it.   But that doesn't explain why Excel would be crashing. 

It sounds like you are already handling the 1/1/1904 in Labview vs. 1/1/1900 in Excel base date okay as well as seconds in LV vs. days in Excel..

(By the way, I think 0.5 in Excel is 12:00:00 PM not 12:00:00 AM)

Which version of Excel are you using?  Most versions of Excel only got to 65,000 + rows.  Only the latest version goes up to 1 million or something like that.  Could that be the source of your crash?

Edit:  Perhaps one other possibility would be to use another character as a delimiter such as TAB or any other character that doesn't usually show up in data files such as pipe |  or tilde ~

Message Edited by Ravens Fan on 12-12-2007 10:19 PM

[J.Dred]

Mr Raven,

pls see my code...

regards

[RavensFan]

Try this.  I attached as picture because I can't save back to LV 7.

I'm not sure what the 3600 is for in your calcs.

I learned something new, that LV bases it date on 2PM on 1/1/1904.  (Not sure why, I thought it would be midnight 1/1/1904).  It think the code handles this, timezone, and the other conversions okay, but you will need to verify these.  My output text file came out okay, but my numbers are based on a period for the decimal point.  If you are using commas, you may still have an issue with the text file to Excel import.

[J.Dred]

HI RAVEN,

thnx , I verified my formula and in my code the calculation of time is done correctly 😉

Here ,the function you use after the loop , I couldn`T manage to to find , would you pls tell me its name?

Message Edited by J.Dred on 12-13-2007 11:27 AM

[RavensFan]

Write to spreadsheet file in File I/O pallette.  But it's possible it did not exist until more recent versions of Labview.

It basically takes the array and puts each element together into a single string using delimiters and end of file characters as necessary and writes it out using the text file I/O functions.

Also look at the array to spreadsheet string in the String pallette. 

[J.Dred]

i have done it exactly as you have described , but in the txt file , instead of a new line after each string, I have a "box" sign after each string .

I dont get why the data is in one row ,instead of new line after each step in loop.

would you pls see the vi?

regards


Message Edited by J.Dred on 12-16-2007 01:14 AM

[RavensFan]

Instead of using the line feed consant, use the carriage return/line feed constant.

[J.Dred]

this is the result file with carrige return.i tried it before the line feed both doesn`t work.

your suggestion ?

regards

[J.Dred]

somehow html interprator can show it in a right way, but if you open the thext file with Windows Editor it shows everythingin one row.

I will import the result actually to an Excel file, do you think that it may cause problem when everything is in one row?

regards