Excel Reading a column

[Vignesh_LS]

I have a table something like this

Row 10 (not fixed) gives the total of the values in that column
Number of values in a column can differ

Requirement 1 :- Fetch the total when there are blank rows in b/w [column 2]
Requirement 2 :- If total doesn’t exist then calculate the total [column 1]
Requirement 3 :- Calculate or fetch the total (if it exists) when there are no blank rows in b/w [column 3]
Is there an activity to get column letter based on column name ?

Ignore the colours - they are not fixed
Column headers are present in Row 2 and the header names are fixed but column index can change from file to file

Kindly help me with this.

[Madhavi]

Please find below steps-

1. Use read range activity to get the data from excel into a datatable variable, dtData

2. Use Filter Data table activity to check if there any rows contains column 2 value as empty.
   Please find below image for conditions-
   

   
   
   The output of this activity will be a datatable, dtFilteredData

3. Check is filtered datatable has any records to confirm on empty cells in Column2
   dtFilteredData.Rows.Count > 0

4. If empty cells exists, fetch the data from last row as total,
   decTotalCol2 = dtData.Rows(dtData.Rows.Length - 1)(“Column2”).tostring

5. Check Column1 contains total,
   decTotalCol1 = dtData.Rows(dtData.Rows.Length - 1)(“Column1”).tostring
   If doesn’t contain total
   if(string.Isnullorempty(decTotalCol1))
   Calculate sum of first column
   decTotalCol1 = Convert.ToDecimal(dtData.Compute(“SUM(Column1)”, string.Empty))

6. Repeat steps 2 to check column3.
   Check if there are no empty cells
   if(dtData.Rows.Count = 0)
   Check Column3 contains total,
   decTotalCol3 = dtData.Rows(dtData.Rows.Length - 1)(“Column3”).tostring
   If doesn’t contain total
   if(string.Isnullorempty(decTotalCol3))
   Calculate sum of third column
   decTotalCol3 = Convert.ToDecimal(dtData.Compute(“SUM(Column3)”, string.Empty))

[Vignesh_LS]

Hello Madhavi,

Thank you for the quick response.

Can you kindly change the column header names [Column1, Column2, Column3] to [Hello, Hii, Bye] or some random names and try ?

The filter data table seems to consider “Column0” as anything under ‘A’ and “Column1” as anything under ‘B’ and so on…

[Madhavi]

When you do read range, select the Add Headers property. That will consider the first row in the data range as header. Otherwise, the default column names, Column0, Column1, Column2, so on gets assigned.
In your case as Columns can be shuffled, you have to select the Headers property.

[Vignesh_LS]

i did select the add headers property and also my headers are in row 2 and not in row1. This is exactly why i want the column header names to be changed and tried.

Also, i believe .Length in decTotalCol1 = dtData.Rows(dtData.Rows.Length - 1)(“Column1”).tostring here should be .Count and not length, as it isnt a member of the datarow collection

Is there a way to get the index of a column name ? provided the column name is in any row other than row 1

[Madhavi]

Can you please send your workflow file as well as the input excel. I can review and send it back.

[Vignesh_LS]

RobotEvents 1.xlsx (10.7 KB) Sequence2.xaml (7.1 KB)

Through the output data table i know that the column names are “XYZ Excel”, “Column1”, “Column2”. Here since row 1 is merged the column names are this way but my actual column headers are present in row 2.

Since i know the column names now (considering row 1 names), I have hardcoded the name in subsequent steps

If i get to know the column index of the actual column headers (present in row 2 as C1, C2, C3) then the solution can be made dynamic

In Simple terms, if i get the column index or cell index of say “C3” [The header name in my file], the problem is solved.

[Madhavi]

@Madhavi

Calculate sum of third column

I have updated cell range in the read range activity. Now you get your column names.
Also added placeholders for the flow. Please check and implement according to the instruction.
Sequence2.xaml (10.4 KB)

[Vignesh_LS]

i dont see where you have made the changes and also changing the range from “” to anything else would not serve the purpose.

[aksh1yadav]

Hey @Vignesh_LS

You can check below sample and let me know
Excel Reading a column- Vignesh_LS_Sample .xaml (12.1 KB)

Regards…!!
Aksh

[Vignesh_LS]

@aksh1yadav - Thanks man. Your solution works like a charm for the file i provided earlier. But my bad i kind of missed few important points in that file.

Am attaching a replica of the file that am working on and your solution fails for this. Although i have prepared a working solution but i feel the solution can be much more shorter when tried with “Read Column Activity”. Can you try to provide a solution by using this activity in the workflow that i have attached as am failing here.S - Requirement Sorted.xaml (17.7 KB) TestFile_Forum.xlsx (13.0 KB)

@Madhavi - Thank you for your help as well. Havent tried your complete solution but have picked few logics though to get to this stage.

Kindly ignore the colours in the file and the column indexes are not fixed but the header names in row 3 are fixed.

[aksh1yadav]

Hey @Vignesh_LS

@Vignesh_LS

Although i have prepared a working solution but i feel the solution can be much more shorter when tried with “Read Column Activity”.

U can do … that too.

the thing only i am assuming now is that there will be no blank entry in that column because you are reading single column so do not know total column is having Total or not or just last value etc.

please find below solution which you asked for but be aware i am considering you know that there is no blank value.
there are more workaround i know but as you asked for it now and based on time availability check it out.
S - Requirement Sorted.xaml (17.8 KB)

Regards…!!
Aksh

Regards…!!
Aksh