Multiple spreadshits into one single file

[Raknugar]

Good Morning everyone!

Im trying to create a single file (spreadshit or excel or csv) capable of put toghether other 12 spreadshits i already have.

im doing a project of gettings reads from 12 set of sensors, each set of sensors has a temperature sensor and a proximity sensor.

The data is sent from arduino with the help of an xbee slave to a xbee master and read by serial port, then i decode the information of each set and have a display for each set.

I can also write and save the information received to a spreadshit file (one for each, doing a total of 12 spreadshits) in this current format:

17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,5	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10
17/03/2015 14:34	20,44	0	10

where first i have the time of the reading, followed by the temperature values, the rpm values (from proximity sensor, currently 0 cause i have none connected), and finally the number of the set of sensors, in this example is the 10th one

I would like to know how can i create a file (it can be excel or csv or spreadshit) where i could store the data in this kind of configuration:

17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,5	0	1		17/03/2015 14:34	20,5	0	2		17/03/2015 14:34	20,5	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3
17/03/2015 14:34	20,44	0	1		17/03/2015 14:34	20,44	0	2		17/03/2015 14:34	20,44	0	3

I dont know if this is possible

also i cant think of a more organized way to do it, any ideas would be welcome

my project is still on going, so if you find some of missing code on the case structures dont worry, im currently only using set of sensors 1, 2 and 10. everything else is working

the main spreadshit on south part of the code is only getting every reading and storing it in a non-organized way altogether, thats what i want to change.

Can anyone help me?

I'd like to apoligyze for any grammar errors cause english is not my mother language

Kind Regards from Portugal

[NiAchilles]

Good morning.  The best way to do this is to use the insert into array function.  Pass the array through a shift register and add the data from each sensor by using the insert into array.  Figure out how columns you need to index for each sensor and multiply that by the sensor number to get the proper index.  Hope that helps.

[Stuart.Parkinson]

Hi.

First, I must say I found your vi very difficult to read. You really should get into the habit of laying out your code neatly - it will save you hours and hours of debug time in the future and makes it much easier for others to help. Try to keep data flowing from left to right and reduce the kinks and corners in your lines. See this help page for more tips.

Next, have you thought about how you really want your data stored and how it will be used? The example spreadsheet is perhaps not the most convenient layout if you want to do processing in excel, though how you do lay it out depends a lot on your data. For example, how fast are you getting data? At the moment it looks like you read messages as fast as they arrive in whatever order they arrive, and they each get a unique timestamp. If you were reading 12 channels once a second, and all 12 channels data arrived in a few milliseconds, then it would make sense to have one timestamp for a block of readings of all 12 - eg...

Time     | Temp1 | RPM1 | Temp2 | RPM2 | ...

12:00:01 | 22.4  | 0    | 23.2  | 0    | ...

12:00:02 | 22.4  | 0    | 23.2  | 0    | ...

etc.

If individual timestamps are important then perhaps

Time         | Chan | Temp | RPM |

12:00:01:000 | 1    | 22.4 | 0   |

12:00:01:006 | 2    | 22.4 | 0   |

12:00:01:013 | 4    | 22.4 | 0   |

12:00:01:036 | 1    | 22.4 | 0   |

etc.

Once you decide on the best format then you can write all the data to the same file as you go around the loop rather then 12 separate files.

Also currently you open each file every time around the loop (slow) and then don't close it (bad practice)!. Open files outside the loop, write inside the loop, and close when loop finishes (like you do on the file at the bottom of the page).

Another thought - you have 12 case statements which all do exactly the same thing. Any time you find yourself repeating code, think about putting it in a sub-vi. In this case you could have written a sub-vi that did all the calculations, and just dropped that sub-vi into each case statement. Much quicker and much neater.

Have a think about this and let us know a bit more about your data rate and other needs, and I will try to help more.

[Raknugar]

Thanks for the help 🙂 ill try to see if i can do it properly

[Raknugar]

Could only see your answer now.

Yes like i said i didnt have much time to organize everything, its my bad, sorry for the trouble.

Im saving in 12 diferent files cause i didnt know how to get it all into 1 single file, like the one you suggested. Its a great example and solution, ill try to do it.

As for my readings, im getting readings from each set of sensors with an interval of 10 seconds, so yeah, some come at same time, other with a few diference in seconds, i wanna be able to organize my data like the second example you showed. 

As for the file opening in loop, i wanted to closed those, even out of loop, but i went into a little trouble and errors, so for now i put them like that. the final objective is to remove the 12 files, and get everything into a single one, like the one you showed me. But i dont know how to make title layers and how to rearrange the different values into the same line of same period of time.

my objective is to make something like this 

Time     | Temp1 | RPM1 | Temp2 | RPM2 | ...

12:00:01 | 22.4  | 0    | 23.2  | 0    | ...

12:00:02 | 22.4  | 0    | 23.2  | 0    | ...

[Raknugar]

Update:

I already made some of the changes you pointed out, i hope its more easy to see now.
For the cases inside the loop, i cant do the subvi for now cause it will suffer changes, some of my set of sensors will have diferent ones, but for now i have to only focus on temperature and rpm.

I also moved out the file paths that open each of the 12 files for each set of sensors. Its just so i can keep a track of every set individually, just to be safe (my devices still need some comunication issues resolved).

I also put some legends to help you understand what my code does.

I understood your solution for doing something like this: 

Time     | Temp1 | RPM1 | Temp2 | RPM2 | ...

12:00:01 | 22.4  | 0    | 23.2  | 0    | ...

12:00:02 | 22.4  | 0    | 23.2  | 0    | ...

problem is:

I dont know how to put those labels on the csv file for identification and 

My serial COM only sends 1 reading at a time, from a random set of sensors (thats why i put an extra ID from 1 to 12 in each one), so even its impossible for me to make a multi reading in one timestamp, but is it possible to write on the file in 10 to 10 seconds?

cause all my devices send the information from 10 to 10 seconds each, so even if the set 1 then 2 then 4 then 7 then 3, came in different order, all the 12 should arrive before the set 1 repeats its reading again, well technically speaking, and so write only the 12 readings in one single timestamp?

i know my explanation is not the best, english is not my main language, i hope you can understand what im trying to say. Also, many thanks for the help once again, you've been great 🙂

[Stuart.Parkinson · Accepted solution]

Okay, so to add the labels to the file just use the write to text file vi and write a string containing the labels (do this first, outside the loop, straight after you create the file).

But that is not your main problem here.

You have data coming in on one channel from 12 sources at random times in a random order, and you want to collect it together in sets of 12 and output one line for each set. That is quite tricky! It means you have to wait until you have all 12 before you write the line, so you need a way of keeping track of which ones have come in and only writing when you have them all.

Or, if your timing is accurate, you could just collect up whatever comes in over a ten second period, write each value to the right place in a 12 element array (Car 1 data to element 1, Car 4 data to element 4, etc), and write a line to the file every ten seconds from that array.

But then of course you have the problem of what happens if the timing drifts or one of the channels doesn't arrive at the expected time. The error handling will get quite complex.

Your vi as it is will write one full line of 12 pairs of data every time a message comes in - even though the message only contained one new data pair. I don't think this is what you want.

If you want to do it simply, I would just write one line per data pair (the second of my two format examples earlier).

If you are up for a challenge then have a go at writing a line of 12 each time, but since your data can arrive randomly this will be a lot of work. If I had realised the random aspect to the messages before then I probably wouldn't have suggested that format earlier!

[Raknugar]

Again, many thanks, you've been a life savior!

that indeed worked for the label option

As for the rest, yes im sorry i didnt point that out earlier, it was my fault. But i get where you going, yesterday i tried to do some tests while i was waiting for your answer, and for a short period of time, i did manage to write them all at the same time, cause its seems when the writing on spreadsheet, since it has a lot of elements to complete its configuration writing, seems to wait for all the data of the 12 sets to arrive, before putting them into the spreadsheet, but i had all the 12 working, as you suggested, some errors may appear, for example, what if one of those sets doesnt sends data in time? i really need to find some solution about timing to keep everything worked on. that thing you said, to put every reading on a holding array and then writing that array into the spreadshit, seems to be good, need to tested it for problems. 

If it gets too complex, then yes, ill go with your second formating solution.

Thanks for everything once again!

and for the corrections you did and lessons learned

Cheers mate