Write to Spreadsheet

[laps682]

Hello,

I am working on centralizing data acquisition. I have successfully been able to record various parameters such as temperature, humidity, (at what time), etc. I have used the "Write to Spreadsheet" block to transfer the recorded array of data into excel files. So right now, I have multiple excel files with one column of data for each parameter. My ideal finished product would be one excel file with all the data combined like this:

Time:               Temperature:                  Voltage:

1 PM                40 C                                 2V

2 PM                50 C                                 3V

3 PM                60 C                                 4V

This can of course be achieved by manually cutting and pasting all the data from each Excel file but I am confident somebody knows a way or method to make LabVIEW do this for me! Any ideas?

[etvg]

If you acquire your data in a for loop, you can use the functions "create file" preceeding the for loop, inside your loop create an array using "build array" for each of your data points, feed the resulting array into a "array to spreadsheet string", and the resulting string from the previous function into a "write to text file" function. You can then close the file using the "close file" function post for loop. You feed the refnum from the "create file" to the "write to file", and finally the "close file" along with error wires. I can post an example in a few minutes.

[etvg · Accepted solution]

Attached is a VI which shows what I was mentioning in my previous response.

[altenbach]

---

@laps682 wrote:

I have used the "Write to Spreadsheet" block to transfer the recorded array of data into excel files.

---

"Write to spreadsheet" does not write excel files. Please check your code.

[etvg]

Ah, I even overlooked the excel part unfortunately. If you need it to be an excel spreadsheet I believe there is a specific function to create .xlsx files. However the way I create files, posted above, should be readable by excel as it is tab delimited. 

Edit: It seems that if you want to still use write to spreadsheet file, you can just open those files in excel, despite them not specifically being excel files. Building an array for all your data and feeding it into the write to spreadsheet file should get it into the column format you're looking for to have all the data in one file. 

[laps682]

Thank you, that is what I was looking for. Yes, I used a different block to write the .xlsx file but the VI etvg works just fine as you can open it from Excel anyway. 

[etvg]

Glad I could help! Also, keep in mind, if you are doing more than one experiment (measuring data 2+ times per running the VI) in the future, and you want to write this data the same file (not make a file per data collection or overwrite previous data), you will most likely need to get rid of the "close file" function to write to the file multiple times. The close file just keeps the VI from taking up too much computational space.

[laps682]

Hello, for the VI that etvg provided, how do you specify which folder you want to create the file in if you set the block to "create"? I tried entering file paths for the file path nodes but they only seem to be associated with the "open" and "replace" nodes. I have a USB plugged in and when I don't specify anything, it seems as if the USB drive is the default location when prompting me to name my file but what if I wanted to make the default location someplace else? 

I tried something like entering C:\Users, but I get an error which I found out is due to "C:\Users" not referring to a specific file... but I want to specify a default folder location...

[laps682]

Also, I tried implementing the headers that was suggested and you can see the result below... not sure why the tabs are also not applied in the same way as the numbers but I am assuming it is due to one of the arrays being numeric and the other a string but I am not sure, can somebody advise? 

For the file name, I realized that I can just create a control that prompts the user to name their file. However, if the filename already exists, an error occurs at the "close file" block. How do I go about prompting/displaying text on the front panel or even a dialog box that says "Filename already exists, please choose another" whenever this error occurs?

[cstorey]

 wrote:

Also, I tried implementing the headers that was suggested and you can see the result below... not sure why the tabs are also not applied in the same way as the numbers but I am assuming it is due to one of the arrays being numeric and the other a string but I am not sure, can somebody advise? 

If you have the same number of tabs between your headers as are between your data columns then Excel will import it properly.  However a text editor like Notepad will not display the headers directly above the data columns if the data values are wider (have more characters) than the headers.  You can fix this by padding your headers with spaces and using a fixed number of decimal points in your data so that headers and data have the exact same character count.  But really, I wouldn't worry about how pretty it looks in Notepad if your end goal is a spreadsheet.

For the file name, I realized that I can just create a control that prompts the user to name their file. However, if the filename already exists, an error occurs at the "close file" block. How do I go about prompting/displaying text on the front panel or even a dialog box that says "Filename already exists, please choose another" whenever this error occurs?

You've specified "create" in the file open VI, which means the name you choose must be unique since in this mode it will only create a new file.  You could use "Open or Create" which would allow you to overwrite the data.  If you want to append to existing data then look at the file set position VI in the File I/O advanced pallet.  You would open the file, set the file pointer to the end of the file and then do the file write.