Appending data via columns into excel spreadsheet in a While loop

[madambaf]

An extension of these questions:

http://forums.ni.com/t5/LabVIEW/write-to-excel-spreadsheet-in-columns-and-append-to-file-in-a/td-p/1...

http://forums.ni.com/t5/LabVIEW/Write-to-spreadsheet-file-append/td-p/1166189

I followed their advice and was able to append the data from next iterations into new columns in a spreadsheet file. However, as described in this example program (http://zone.ni.com/devzone/cda/epd/p/id/5345), the "Write to Spreadsheet File.vi" and "Read to Spreadsheet file.vi" takes up alot of resources in While loops and after many interations in my program, it begins to interfere with the speed of data aquisition.

When I replaced the "Write to Spreadsheet File.vi" with the "Write to Text File" function (along with the additioanal components to get it to work) I saw a huge reduction in resource usage. Unfortunately I lost the ability to append new data via columns in a spreadsheet file. 

The above links point to the fact that the data must be read and then converted into an array in which new data can be appended. This new array is then written to a text file with the new data showing up as an additional column. I am trying to find some way to maintain the "Write to Text File" function yet add the capability to read from spreadsheet files that is least resource intensive. Are there any ideas? Thank you in advance.

[daveTW]

Hello madambaf,

I have to admit, I didn't read all of the posts that you linked to, but as I understand, you want to write text data to a CSV- file and want to append data column- wise. The right way ist described from tbob here, but what about saving your csv- data transposed. There you can append new data as a new row, you only have to transpose it once when opening the csv- file. And if you need the file opened in Excel and it interferes with the limitation to only 256 columns, you write your tool to transpose the csv- file before opening it with excel.

[madambaf]

Hi daveTW,

Your correct that I want to write text data to a CSV-file and then append new data column-wise. I rewrote my question below, a well as my approaches, to include the essential parts that I took from the various other posts I linked too to provided an easier time for individuals to help with my question. Can you please check below to see if you understood my question correctly though I believe you have.

I never actually considered doing the transposition all at once after the program had completed its task and build the final array. This is due to the fact that I initilaly thought the program would have to keep the entire array in memory in order to transpose the rows to columns at the end and hence store unnecessary information. However I see no reason why this has to be the case and I will attempt this approach. Thank you for the help.

My question consists of two parts:

(1) Best stated in this forum http://forums.ni.com/t5/LabVIEW/write-to-excel-spreadsheet-in-columns-and-append-to-file-in-a/m-p/10...

and this http://forums.ni.com/t5/LabVIEW/Write-to-spreadsheet-file-append/td-p/1166189

• "I have a loop in which for each iteration, an array gets written to a spreadsheet file.  I know I can transpose the input data and have it show up in a column in the spreadsheet file.  However, on the next iteration, I want the new array to get appended to the spreadsheet file in a new column, instead of in the same column after the previous array.  Any ideas?" - Godnumber1

Although I had already had this feature in my program, this was the solution given by a forum member

• "If you want to append columns, you would have to read in the spreadsheet data and use Insert Into Array to append to columns.  Just specify column index instead of row index.  Or you could transpose the array, Insert Into Array using row index, then transpose again.  Then write it all back." -tbob

(2) How to accomplish this same task without using the "Write to Spreadsheet File.vi" or "Read to Spreadsheet File.vi" which, as stated best here, 

http://zone.ni.com/devzone/cda/epd/p/id/5345, is not very efficient in a while loop. As a response to the forum topic by Godnumber1, another individual provided a template on how to accomplish this:

• "Are you actually writing to an Excel Spreadsheet?  Or are you using the Write to Spreadsheet file function which does not write to an Excel spreadsheet, but creates a text file that Excel is able to open?

  I'm going to assume the latter.  There is no way to just "append" a column to it.  You will need to read the whole file, append the column to the array, and write out the whole file column overwriting the previous file."

I'm trying two different approaches to reach my goal:

(A) Take what I already have working and then decompose the the "Read to Spreadsheet file.vi" and "Write to Spreadsheet file.vi" into their basic read function and "Write to text file" functions to prevent the continuous opening and closing of files that the "Read to Spreadsheet file.vi" and "Write to Spreadsheet file.vi" naively does. 

(B) Modify the example found here, http://zone.ni.com/devzone/cda/epd/p/id/5345, to allow for data to be added into a new column instead of the same column in the spreadsheet. 

In the first case I am trying to figure out what are the essentially components of the VIs that I need to keep to maintain functionality. In the second case I am trying to figure out the bare minimum of components I need to achieve my goal. I am hoping that two approaches will converge to the correct answer but so far I have had no luck. 

Hopefully this clears things up. I am too unfamiliar with the terminology of "arrays" or "transpose" which is preventing me from describing my task out in a more concise manner. 

[Arham-H]

Hi,

You mentioned in your first post that you were able to append the columns in the spreadsheet however it was taking up a lot of resources. To avoid adversely affecting the data acquisition loop you can use a consumer / producer architecture as show in the example here. Your data acquisition will be done in the producer loop and the append to spreadsheet file will be performed in the consumer loop.

Regards,

[daveTW]

Hello madambaf,

I made a quick example of what I meant.