spreadsheet excel file problem (intervals)

[freemason]

hi guys, need some help

please see the attached

Problems:

1) at every 6secs starting, it should write 3 secs of data, stop and wait
for next 6sec start, that means while the ""true time" is true it should
write to spreadsheet essentially 3secs of data every 6 secs (example)

in the "output array" it records 3 values at every 6 secs
but also writes empty arrays in the bottom, while the case is false
(i guess because of the shift register values in false case) as we wait for more values
more empty arrays are being written and ocassionally one extra value is being added
after 1st loop (only some times)

2) also the values in 1st column are to be numbered serially, it is taking the
values of the while loop iteration number at the time it is true inside.
can it be made serially numbered? ( by not linking to iteration number of while loop)

3)the excel file has repetitions of the same old values added with new values
all the time and makes excel file large than what is required

4) how do i program the spreadsheet.vi to get like this in the excel sheet, that will be
saved ,i.e., every 3 secs of data side by side, but by default its written at the bottom

5) how can i include 2nd column with the current time of the data acquisition

3 secs of data                      3 secs of data
#  time     data1  data2         #  time     data1  data2     
1   4.65    3.85    6.22          1   4.65    3.85    6.22
2   5.67    7.23    3.66          2   5.67    7.23    3.66
3   5.90    2.78    1.76          3   5.90    2.78    1.76
4   9.08    6.84    6.34          4   9.08    6.84    6.34
5  1.34     2.98    5.87          5  1.34     2.98    5.87

[RavensFan]

1.  I'm not sure why you are getting blank rows.  Maybe that can be figured out and resolved once the other issues get cleared up.

2.  Correct, Don't use the iteration loop number here.  Create another shift array for a integer value.  Initialized it with zero.  Wire it through in the false case, but increment by 1 in the true case.

3.  That's because you continually append to your string array.  So you collect the data in the string array and occasionally right it out.  But you don't bother to reinitialize the string array to an empty array again.  So the data lingers to be written out all over again.  I'm not sure if what you're doing should require reshaping from a 2-D array to a 1-D array or taking single string values and turning them into 1 element 1-D arrays before merging them together into a 2-D array.  A lot of unnecessary manipulation.  You should look at the Format into String function to create a one-line string of all your data that you can append.

4.  You are not going to be able to easily right the data side by side using the Write to Spreadsheet File function.  You'd have to collect all the data in a single array and write it all out at one time at the end of the program.  Side by side means the newer data has to be written after the older data has already been written.  There is no way to append data to the end of a row in a text file without reading it all back in and writing it all back out again.

5.  You seem to have found the time and date function.  You should be able to use the outputs for format together the date and time into a string.  There are also some other functions that will do that for you.

You also have a race condition by the use of the local variable "True time".  There is no control as to whether the lower half of your code will execute before or after the upper half of the code where the value gets assigned to that boolean.

[freemason]

Thank you so much for the reply.

interestingly, in the simple case attached below

1) the "record if true"  seems to buffer the "continous
values" even when the case is false and display/record
everything when case becomes true and excel sheet
will have all the values from start to end instead of
having only the values that run during true case

2) how can i record only the values that run
during button press (boolean true case)

any solution would be of great help

thanks

[RavensFan]

That's because you are appending data to the array on every iteration of the loop regardless of the status of the boolean switch.

You need to put the append function in the case structure as well in the True case.  In the false case, just wire the array wire straight through without appending data.

Also, get rid of the local variable "Continuous Values" (and check your spelling on continuous).  You have a race condition again.  Wire the array wire directly to the write spreadsheet file.

Message Edited by Ravens Fan on 01-21-2009 10:40 AM

[freemason]

thank u for correcting my mistakes,

i still have 2 little problems as below

1) the 1st value no."1" in the "# values" column doesnt show up if i dont initialize
and if initialized it shows the last number +1 of previous operation, i guess something to do with
feedback or shift registers........

2) the 1st column in the excel file is supposed to have the heading array values, but doesnt showup
even if initialized with these strings before entering while loop so i can have them as headings
for 1st time

date          - - - - - - - - - - - -
time           - - - - - - - - - - - -
#values     - - - - - - - - - - - -
data1         - - - - - - - - - - - -
data2         - - - - - - - - - - - -
data3         - - - - - - - - - - - -

 

[RavensFan]

1.  You are feeding the string value for the sequence # from the string feedback node.  So it is empty on the first iteration.  You are also incrementing that number twice (once in the case structure and again afterwards).  You don't need that string feedback node at all.  All it is doing is pulling data from the previous iteration and loses it after that.  Just use the numeric shift register to maintain the count like you are doing.  Put the string functions in side the case structure since you only use them when that case is true.

2.  The reason you don't get the original header information is that the Array to Spreadsheet function is set to create a new file inside the while loop.  Thus once the header is created before the loop, it gets overwritten inside the loop.  You need to wire a true constant to it so it appends the data to the file.  However you will  still have architecture problems.  Remember in message two #2 how I told you once you write out the array, you will need to reinitialize the shift register to be empty so that you don't maintain the old data to get rewritten into the file the next time around.  But since you are writing out each single piece of data has its collected.  I am just sending that to the Array to Spreadsheet file function.  The shift register is now holding only the data for what is stored on screen.

3.  You are not creating an Excel file.  You are just creating a text file that happens to be formatted in such a way that it can be opened and imported into Excel or other spreadsheeet programs.  NI did a horrible job naming the Array to Spreadsheet function and you'll see numerous threads in the forum where people are consistently confusing this function with the ability to create Excel spreadsheets.

4.  You will not be able to create a column of header information.  As I said in message two #4, you cannot create side by side information using this function.  When data is written out to a text file, it is essentially written out line by line, or row by row.  You cannot append data to the end of a line or row.  You can only append new data to the end of the file as a new line or row.

5.  Pay attention to wiring.  You have several instances where wiring flows from right to left rather than left to right.  It makes it difficult to track the flow of data through the program when wires run in haphazard directions.

I cleaned up the file a bit and move the functions into the case structure to make things function more like how I think you want them.

[freemason]

thanks,

this seem to work properly but only when the transpose is true (vertical in excel) ,

date	Time	# values	data 1	data 2	data 3
1/21/2009	########	1	3.131421	2.720793	0.850137
1/21/2009	########	2	6.379169	1.922371	8.888305
1/21/2009	########	3	9.30708	6.740256	8.658603
1/21/2009	########	4	3.794669	6.494008	2.676793
1/21/2009	########	5	1.893391	6.529622	1.405179
1/21/2009	########	6	7.262705	1.448104	9.249764
1/21/2009	########	7	9.778245	9.196569	0.404124
1/21/2009	########	8	8.851197	5.997591	2.342014

if it is false then i get only 1 row which has 1 value each of the variables, and no headings at all as below

1/21/2009

########

28

6.308354

3.259139

0.921604

i understand the 4) ans but i was trying to get something like below which i suppose it should when i dont transpose, or can it be programmed (read from excel manipulate and write to new format or transpose only the first headings row and then attach as first column) somehow to look like this, i cannot figure it out.

date        - - - - - - - - - -

time        - - - - - - - - - -

#values   - - - - - - - - - -

data 1      - - - - - - - - - -

data 2      - - - - - - - - - -

data 3      - - - - - - - - - -

may be i am missing something

[RavensFan]

You cannot write data into a data file by column.  You can only write it in by rows.

Yes transpose will write things in the other direction, but only the first time you do it.  When new data is appended to the file, that means it goes to the end of the file.

Your only real option is to take the VI has I gave it to you.  Allow it to write the data row by row, and once you import it into Excel, let Excel transpose the data for you.

Otherwise, you could abandon the idea of using the Array to Spreadsheet text file option and use ActiveX programming to directly enter the data into an Excel spreadsheet.  There is a forum on Excel and numerous threads discussing the interface with LabVIEW to Excel using ActiveX.

Message Edited by Ravens Fan on 01-22-2009 09:22 AM

[Nghtcrwlr]

Hello Ravens Fan

I too need a similar application of excel. Need to save data values in excel at every 2 seconds. i.e 00:00:02 then at 00:00:04 ..... and so on. Could you  please convert the vi u have uploaded here in LabVIEW7.1 Version????

Thanks

[RavensFan]

See attached.  It will have a broken run arrow because version 7.1 did not have a Write Spreadsheet File function that could handle strings.  But if you open that subVI, it will tell you how to modify so that it can.