04-14-2016 10:33 AM
Hi all,
I'm trying to do something in LabView but I don’t know how to do it. I have 250 excel files with data. Out of those data sheets I would like to select 2 columns with data and do some calculations on it (linear regression). I have no problems to program the calculation. But I want to know how I can open all the excel files of 1 folder one by one (automatically) to read them into LabView and do the calculation. After doing the calculation I would like to put the result of the calculation in the same file were I stored the data. Does anybody know if this is possible and how to do this?
Thanks in advance
Michel
04-14-2016 10:35 AM
Search for Report Generation Toolkit.
04-14-2016 12:29 PM
@Michel000 wrote:Hi all,
I'm trying to do something in LabView but I don’t know how to do it. I have 250 excel files with data. Out of those data sheets I would like to select 2 columns with data and do some calculations on it (linear regression). I have no problems to program the calculation. But I want to know how I can open all the excel files of 1 folder one by one (automatically) to read them into LabView and do the calculation. After doing the calculation I would like to put the result of the calculation in the same file were I stored the data. Does anybody know if this is possible and how to do this?
Thanks in advance
Michel
If you have Excel, you will find that this is so much easier to do in VBA than in LabVIEW.
I did this just yesterday.
04-14-2016 09:07 PM
It is always helpful (to us) if you post some code (as a VI). From this, we can tell (a) what version of LabVIEW you are using (LabVIEW 2014 and 2015 include the Report Generation Toolkit, for example), (b) how much LabVIEW you seem to know (so we neither insult you by explaining how a While loop works, or bewilder you by talking about LabVIEW OOP), (c) if there's a simple error, we can point it out, and (d) if it is a real mystery, we can try your code out on our machines and see what works.
It sounds to me like your question involves locating files of a particular type (.xlsx?), organizing them into, say, an array, and then processing them (how?) one at a time. However, without code to guide me, I'm not sure if this is your real question or not!
Assuming that the above is the question, check out the List Files function found on the File I/O, Advanced File Functions Palette. It allows you to specify a pattern such as .xlsx or .xls* for your search and returns an array of file names that can be passed to a FOR loop for individual file processing.
Bob Schor
04-15-2016 02:27 AM
I believe that this is what you had in mind?
However you should take in consideration what Bob_Schor just said
04-15-2016 02:50 AM
thanks all for the tips.
@AdamTrojak --> that looks as a good start, thank you
@Bob_Schor following answers on your questions:
- i have labview 2015
- i know how to work/program with labview to use loops and simple calculation. The problem is that my knowledge is verry limited about LabView/Excel connection
- in attachement you can find the excel file of my data. The information that I wan't is in the first tab (called "meetwaarden"). I would like to extract all the data of colum M and N.
- all the files are .xls
- i would like to have al the information in one array so i can process than that array.
my biggest problem is that i have now idea how to start extracting data out of all my excel files, I will try to start now with the picture of adamTrojak 🙂
04-15-2016 08:59 AM
Because you have LabVIEW 2015, you also have the Report Generation Toolkit that has excellent "tools" for dealing with Excel. I'm attaching code that reads the "tijd (u)" and "O2" columns from the Workbook you posted, and I'll also explain how it works. First, the code. This is a "Snippet" (you can tell by the "2015" in the upper-right corner). Although this "looks like" a .PNG file, if you open LabVIEW 2015 and drag this image to the Block Diagram, NI Magic will transform it into LabVIEW Code.
So here's how it work:
When I first ran this, it looked like the first column was all 0's. However, that was just an "artifact" of LabVIEW's Display format, 2 significant digits. If I changed the Display properties to 6 digits, everything was there.
If you understood my earlier Post about using LabVIEW's List Folder function to get an array of all of the Filenames, you should be able to turn that into an Array of File Paths and, in a For loop, put the above Snippet and wire the specific Path where I used the Path to the Workbook you provided. You'll get a "data" array that you can further process, and can do this for each Workbook in your folder.
This should solve your problem.
Bob Schor
05-04-2016 04:00 AM
@Bob_Schor
Hi Bob,
thanks a lot for your tips and tricks, it was already very helpful.
I needed some time to try to make my program but I'm still struggling with some problems. In attachment you can find the program that I already made. In the first part you can recognize your excel reading part J in the while loop I do my calculations. The last part is used to write the calculated data to excel again.
I’m still struggling with the following things:
Can you give me some suggestions or hints to solve those problems? J
Kind regards
Michel
05-04-2016 08:40 AM
@Michel000 wrote:
- To now when my array is finished I used a button to calculate the size of my array. Although it looks like also empty cell are counted? See comments below.
- Is it normal that it takes 1 day to read one excel file? I think I did something wrong because he opens every time the excel value to read in 1 measurement so the excel file is opened for a few 1000 times…. You are definitely doing something wrong -- LabVIEW should be able to read Excel files almost as fast as Excel (I haven't actually done a head-to-head comparison, but I was reading 200-row, 100-column Workbooks (only one Worksheet was that big -- the other two were considerably smaller) in less than a second.
- It’s not possible to write the data in excel. First I collect all the data in a loop and at the end I try to write everything in one time to excel, for that the last true/false case in my program become true. But eventually nothing happens in this case? Yep, something is wrong.
Can you give me some suggestions or hints to solve those problems? J See below.
Hello, Michel.
There is a lot of stuff going on in your Block Diagram -- so much, in fact, that it is difficult to understand what you are trying to do, and therefore difficult to figure out where, how, and why your code is going "sideways".
What I would suggest is to Simplify and Self-Document. Let's start with the left side of Calculate Slope. What is it you are trying to do? I see some "Magic Numbers" present -- try to document them, or replace them with more "transparent" equivalents.
My recommendation would be to start with writing a test VI with just this simplified "Read Excel and show me the Time and O2 Columns." If you want, you can try plotting them, as well. For now, skip the stuff in the While Loop.
OK, let's look at the center Frame of your Sequence Structure. I just erased some text that said "Delete the Sequence Structure, you don't need it", but realized that was wrong -- there's nothing that ties it to the first Frame that involves reading Excel! [I thought the Error Line ran through the While loop, which would have appropriately serialized things, but I see, now, that it doesn't -- why not?].
Ah, now I see the relationship. The Loop uses the Time, O2, and Size(s) that you just read, but which you did not pass in via Wires (in keeping with the LabVIEW #1 Principle of Data Flow). Of all the ways of passing variables into loops, one of the worst ways of doing it is via Value Properties (you can find better explainers than I from answers in this Forum). Use a Wire! Take the wires going to your Indicators and bring them into your While loops (you can leave the indicators outside the loops). Run wires from these three Input Tunnels to where you now have Value Property nodes, delete the nodes, and use the (appropriate) wire in its place. Now you can get rid of the entire Sequence.
So let's turn our attention to the final Frame, where you are trying to write something to your Excel file. Again, Start Small. Take the simple VI you wrote above (that has the basic elements of the First Frame, with better "self-documenting" code and doing fewer things so you can test it and make sure it is working) and add a simple output section using the same principles -- use a well-defined (and simple) data set to write, document what you are doing, make clear where it should go, and see what happens.
I can tell you right now that nothing will be written. How do I know? There is no Report File Path on the Save Report to File function. [You probably want to write to the same file that you opened at the beginning of the program -- branch the Path wire and run it over to the Report File Path input].
One final suggestion. See all that complicated "stuff in the middle" that separates Excel Read from Excel Write? Do yourself a favor and create a sub-VI that takes two inputs, Time and O2 (you don't need Sizes -- do you see why?) and produces a few (3? what are they? sorry, I can't read Dutch) outputs. Run the Error Line through it (always a good idea with sub-VIs -- use the 4-2-2-4 Connector Pane, and run Error In and Error Out through the lower corners) to "anchor" it in the processing sequence. You now have a "Read Excel section, a Manipulate the Data section, and a Save Excel section. If you make a nice Icon for your sub-VI, your code almost documents itself!
By the way, are you developing this code within a LabVIEW Project? [Hint -- the only "correct" answer to this question is "Yes"]. If you are not, that should be absolutely the first thing that you do. Other than code that never gets beyond the stage of "Untitled 1" and "Untitled 2", (almost) every VI that you develop should be part of a LabVIEW Project, particularly if it needs to "interact" with other VIs that you are developing.
Bob Schor
05-04-2016 09:58 AM
Hi bob,
Thanks for your quick and comprehensive answer. To give you an idea about what is going on the wile loop I can tell you the following:
I am making my whole program just to calculate slopes out of my data.
More in detail:
I have the data array in excel with time and oxygen concentration. This data have a kind of sine wave shape. Each time the curve go down I would like to calculate the slope of this curve between 2 fixed points (Lower and upper). So first I collect the data of 1 line in a table, after I collected the data I calculate the slope out of it. I store this data in a shift register and I controls the following data if it is between the lower and upper point. When it is not, the data is not stored, when it is, I store it again in a new array. After that I calculate the slope again. And so on and so on….
As you can see in the data there is an up going line/slope and a down going line/slope. I only need the down going lines so I remove the calculated slopes that are positive.
To be honest I just started a blank VI and not a project … I change it immediately!
I will try to implement al your suggestions during the common days I keep you informed!
Tanks a lot,
Regards
Michel