DataTable segrgation

Help Studio
, ,
1

Hi Community,

I have one Excel file i have to identify the company type there is one condition After Blank row only one row and then again blank row then it should be single company and if after blank row. there is multiple rows. and after that again blank row then it should be multiple company and i want to seggregate single and multiple company.
how i can achive this anyone could help me it would be great.
I am Attaching screenshot to understand better

Billing2
Billing21153×336 5.04 KB

2

@Akash_Javalekar1

  1. Read Excel File:
  • Use the “Read Range” activity to read the Excel file into a DataTable.
  1. Iterate Through Rows:
  • Use a “For Each Row” activity to iterate through each row in the DataTable.
  1. Check for Blank Rows:
  • Use an “If” activity to check if the current row is blank.
  • You can check for blank rows by examining a specific column that you expect to be empty for blank rows.
  1. Count Rows in a Company:
  • Inside the “For Each Row” loop, use another “While” loop to count the number of rows for each company.
  • Continue looping as long as you encounter non-blank rows.
  1. Determine Company Type:
  • After the “While” loop, use another “If” activity to check the count of rows for a company.
  • If the count is 1, mark it as a single company; otherwise, mark it as multiple companies.
  1. Store Results:
  • You can use variables or another DataTable to store the results.
  • For each company type, add the relevant information to the result storage.
  1. Write Results to Excel:
  • Use the “Write Range” activity to write the results back to an Excel file.

cheers…!

3

@Akash_Javalekar1

or else use this linq query

singleCompany = yourDataTable.AsEnumerable().Select(Function(row, index) index > 0 AndAlso String.IsNullOrWhiteSpace(row("CompanyColumn").ToString())).Distinct().Count() = 1

cheers…!

4

Are you looking for 2 tables:

  • dtMulti, ADAV1,12,13,BUSNJ1,15
  • dtSingle, BESW1

Or are you looking for 3 tables:

  • ADAV1,12,13
  • BUSNJ1,15
  • BESW1

for the 3 table case we can do:

Assign Activity:
TableList | DataType: List(of DataTable) =

(From d in dtData.AsEnumerable()
Where not (isNothing(d(0)) OrElse String.IsNullOrEmpty(d(0).ToString.Trim))
Let gk = System.Text.RegularExpressions.Regex.Match(d(0).toString, "^\D+").Value.Trim
Group d by k1=gk into grp=Group
Select t=grp.CopyToDataTable).ToList

ADDED: illustrations

grafik
grafik235×505 33.2 KB

5

from:

We keep in mind that the LINQ Select is selecting the compare result, which is a Boolean

grafik

This is reasonable as we can expect

  • a lot of rows where the Select indexed value is > 0
  • Filled and Empty Company Code Column Values

Finally LINQ is resulting to: False

grafik
grafik839×67 17.7 KB

6

Like there coule be any kind of data means as i mentioned in the screenshot if there are continue rows then it should be multiple scenario and if before and after blank row and single row then it should be single company

7

And i want to identify if single row or multiple row and if single row get another column value and if multiple need to get same value and proceed further.

8

Can you send sample workflow for this that would be helful

9

This worked but i want to identify if its single or multiple base on that i want to take decision if its single row i want to create invoice for single company and if its multiple create multiple invoices.

10

some open topics are still present and cannot be cleared form the descriptions / feedback

as we can check the row count from each separated table after the grouping by we do know if it is

  • single - tableVar.Rows.Count = 1

OR

  • multi case tableVar.Rows.Count > 1
11

can we do this into one table ?

12

Thank You so much for the kind help it is worked

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.