LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

excel

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

 

0 Kudos
Message 1 of 10
(4,085 Views)

Search for Report Generation Toolkit.

0 Kudos
Message 2 of 10
(4,082 Views)

@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.

 

 

0 Kudos
Message 3 of 10
(4,053 Views)

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

0 Kudos
Message 4 of 10
(4,020 Views)

I believe that this is what you had in mind?

However you should take in consideration what Bob_Schor just said

0 Kudos
Message 5 of 10
(3,990 Views)

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 🙂

0 Kudos
Message 6 of 10
(3,980 Views)

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.

Retrieve Columns M and N.png

So here's how it work:

  1. The first function, New Report, opens your Report (wired to the "Template In" terminal) using Excel.  You can also wire an .xlsx file in here.  It outputs a "LabVIEW Report Object" (the green chain), which the other RGT functions need.
  2. By default, the first Worksheet is opened.  This happens to be the Worksheet that you want.  If you needed another Worksheet, there is a function (poke around the RGT Palette for Excel-Specific Functions) to select the Worksheet.
  3. The second function asks Excel to return the position of the last row.  It comes back bundled into a Cluster, and I extract the last row, which is 19558 (wow, big Workbooks).
  4. There are two functions above that take "Excel addresses" (e.g. M5, the first cell on your Worksheet with numeric data) and return LabVIEW Row/Column numbers (M5 = row 4 (fifth row counting from 0), column 12 (13th column)).  The Row/Column values get bundled into a Cluster.  We "know" the starting Row/Column (assuming all the Worksheets are the same, it's M5).  We know the last column (it's the next column, so we simply increment the column) and we have the last Row from step 2.
  5. The third function is Get Data.  We wire in a 2D array (I hid the Index Display for "neatness"), and we also wire in the Start and End locations from step 4.  This gives us a 2D array that I named "data".
  6. We use Dispose Report to close Excel.

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

0 Kudos
Message 7 of 10
(3,957 Views)

@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:

  • 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?
  • 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….
  • 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?

Can you give me some suggestions or hints to solve those problems? J

 

Kind regards

Michel

 

0 Kudos
Message 8 of 10
(3,825 Views)

@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.

  •  What is M5?  (There should be at least a Free Label that says "Starting Location" or something like that.
  • Why is Y incremented?
  • What do you expect to see when you do the Excel Read?  Use a better descriptor for the output than "Data".
  • Where do the mysterious "Column Number" and "Column Number 2" come from?  What are they?
  • You appear to want to extract a particular Column from your 2D array "Data".  If that's what you intend, you are taking many extra and unnecessary steps -- a simple Index Array, wiring the desired Column into the lower "Column" input will, in a single step, return the chosen Column to you as a 1D array..

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

 

0 Kudos
Message 9 of 10
(3,806 Views)

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 …Smiley Embarassed I change it immediately!

I will try to implement al your suggestions during the common days I keep you informed!Smiley Wink

 

Tanks a lot,

 

Regards

Michel

0 Kudos
Message 10 of 10
(3,795 Views)