Analyzing Excel Report

[Basavaraj093]

Hi all,

I am working on a LabVIEW project where I need to retrieve and combine data from multiple Excel sheets based on a selected date range. Each sheet's name corresponds to a date (e.g., "01-01-2025", "02-01-2025"). I can already get the sheet names and the required data from individual sheets using ActiveX.

Thanks to Someone who posted it in forum.

Requirement:

Filter sheets based on a date range (e.g., from "02-01-2025" to "04-01-2025").

Combine specific data (e.g., quantity) from the filtered sheets into a single total.

Example:

For sheets with the following data:

01-01-2025: Qty = 10

02-01-2025: Qty = 4

03-01-2025: Qty = 7

04-01-2025: Qty = 30

05-01-2025: Qty = 35

If I select the date range from 02-01-2025 to 04-01-2025, the total quantity should be 41 (4 + 7 + 30).

How can I efficiently filter the sheets by date range, retrieve the quantity data from each filtered sheet, and calculate the total in LabVIEW?

I have attached what I have done, The Excel is different, I can't share original one, but the report looks like this only.

[Bob_Schor]

Consider your data format:  Date in the form "01-01-2025" (which I assume is a string) and "Qty = 40" (which I assume is another string).  I'm assuming that you have two Excel columns to read, one of Data strings, one of Quantity strings.

I would make a Cluster with two elements -- Sortable Date (an I32) and Quantity (a Dbl, or an I32 if always an integer).  You need a function "Make Sortable Date" that changes your "Date" (01-02-2025) which is difficult to sort and compare into "Sortable Data" (20250102) which is (Day #) + 100*(Month #) + 10000*(Year #).  All you now need is to translate Date into Sortable Data -- fortunately, Scan from String (look in String Palette) can recognize 2-digit integer, "-", 2-digit integer, "-", 4-digit Integer into "Month #", "Day #", "Year #".  Similarly, the string "Qty - 40" can be turned into an integer "Quantity".

Read your sheet data and build an Array of Clusters.  Sort the Array -- it will sort on the first element, "Sortable Date".  Now your task almost solves itself -- you can enter a Data Range, change the Dates to Sortable Dates, the process the "sorted" Array of Clusters.  Start accumulating Quantity when you find the Array you just constructed looking for a Sortable Date >= First Sortable Date, and stop when Sortable Date > Last Sortable Date.  Do yourself a favor and end the Array with an entry with Sortable Date of "99999999".

Bob Schor

[Basavaraj093]

Thank you for your reply,

The Sheets names are Sorted in the excel itself. 

If I have total 5 sheets and only want from 3 Using Date, I can get required Sheets.

and I tried your method also

  I am Struck After This how to get those sheet data. In different arrays. 

[Bob_Schor]

Am I correct that you have a single WorkBook, on which there are multiple WorkSheets, each with a "date-like" name, and on each Sheet there are data that you want to process?

Here's how you could do it:

1. Form an Array that has the names of the WorkSheets.
2. Using techniques already discussed, sort the names so they are in "temporal" order (i.e. the earliest date is first).
3. Now start processing in "date" order.  Skip until you get a Date in the range you want.
   1. Get the actual WorkSheet name corresponding to this "acceptable Date".
   2. Use "Excel Get Worksheet" function to access the chosen WorkSheet.
   3. Use "Excel Get Data" (with a 2D Array wired to the Data Type input).  This will allow you to specify the coordinates of the area on the Worksheet to read.

Bob Schor 

[Basavaraj093]

---

@Bob_Schor wrote:

Am I correct that you have a single WorkBook, on which there are multiple WorkSheets, each with a "date-like" name, and on each Sheet, there are data that you want to process?

You are Correct.

---

@Bob_Schor wrote:

Here's how you could do it:

1. Form an Array that has the names of the WorkSheets.

---

Done. Below is the array that give all the sheets present in the Workbook.

Using techniques already discussed, sort the names so they are in "temporal" order (i.e. the earliest date is first).

---

It will already give sorted array.

Now start processing in "date" order.  Skip until you get a Date in the range you want.

---

It is giving me required sheets.

I want to get a data from these sheets.

If I use Excel Get Data, I only get single sheet data.

If I use a for loop and get indexed data, I will get 3D array. 

From this how can I get required column data.

[Bob_Schor]

Please attach a Workbook similar to the pictures you just posted.  Explain how to recognize the data that you want to extract from, say, Sheet 01-01-2025, and how you would like the data from this sheet to be "combined" with the data from the other two selected Sheets.

Note that the data from a single Sheet will probably be a 2D array of data (possibly numeric, but we don't know because we can't see the data).  You are correct that if you read 2D data from three Sheets, you can get the results as 3 2D arrays, which can either be "stacked" to make a 3D array, not all the "rows" having valid data, or "concatenated" to make a single 2D Array.

One very nice thing about LabVIEW is that it is easy to write tiny routines to show yourself the difference between the two ways of combining arrays from 3 sheets to get a single Array containing all the elements (and maybe a little more).

Here are three sheets, "1", "2", and "3".  Sheet 1 has 5 rows of 2 columns of "1", Sheet 2 has 4 rows of 2 columns of "2", and Sheet 3 has 3 rows of 2 columns of "3".  If you run this, you'll see that combining them using Build Array gives an appended 3D array of 3 "pages", with Page 1 having all "1"s, Page 2 having 4 rows of 2 and one of 0, and Page 3 having 3 row of 3 and 2 of 0 (because the default action of Build Array is to force all the "missing data" to be a default value, in this case 0).

However, if you right-click Build Array and choose concatenate inputs, you will end up with a 12 x 2 Array of 5 rows of 1 followed by 4 rows of 2 followed by 3 row of 3.  Isn't that what you want?

Bob Schor 

[Basavaraj093]

Hi thanks for your reply,

---

@Bob_Schor wrote:

Please attach a Workbook similar to the pictures you just posted.  Explain how to recognize the data that you want to extract from, say, Sheet 01-01-2025, and how you would like the data from this sheet to be "combined" with the data from the other two selected Sheets.

---

I have already attached required documents in my first post you can download it.

or you want just a excel workbook here I have attached it.

---

@Bob_Schor wrote:

Explain how to recognize the data that you want to extract from, say, Sheet 01-01-2025, and how you would like the data from this sheet to be "combined" with the data from the other two selected Sheets.

When we select the date from date and time picker it will fetch that sheet data into these indicators, I am populating the ring for selecting required project.

Once I select the Project, I will select the model. 

Selected model name, QTY, Yield all will be displayed as shown below.

But this is for single Day, I want to add range for it.

---

@Bob_Schor wrote:

However, if you right-click Build Array and choose concatenate inputs, you will end up with a 12 x 2 Array of 5 rows of 1 followed by 4 rows of 2 followed by 3 row of 3.  Isn't that what you want?

Bob Schor 

---

Yes, I want this only, but how could I get the data for concatenating the inputs.