Blank rows Excel Automation

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_javalekar3

Welcome to the community

how do you need the output?

you need in separate sheets or how it is?

cheers

3

IT should be in same sheet

4

@akash_javalekar3

so we need to write multiple and single in separate columns?

cheers

5

Actually, if single company i want to pick another column data and if multiple want to do same?

6

if single company need to pick data and create invoice for single company and if multiple company create invoice for the same

7

Could you please help how I can achieve this.

Thanks in advance.

8

@akash_javalekar3

create a string variable str and initially assign with single

  1. Read the file into datatable
  2. then use for each row in datatable activity…and in the properties panel assign a variable to index property say index
  3. inside loop use if condition with index+1=dt.Rowcount
  4. on else side use if condition with currentRow("CompanyCode").ToString.Trim.Equals(String.Empty)
  5. now on then side of second if add assign with str = “Single”
  6. on else side add if condition with str.Equals("Single") and dt.Rows(index+1)("CompanyCode").ToString.Trim.Equals(String.Empty) on then then side assign str ="single" and else side str ="Multiple"
  7. Now on the very first then side do nothing

Now after then condition check str value if it says single then it is single else it is multiple

cheers

9

Hi here you have mentioned “Single” but in excel not mentioned as single or multiple?

10

@akash_javalekar3

that is to seggregate i mentioned as single and multiple…in excel i am not checkignf or it…i am deciding based on first column values if it is single or multiple based on previous and next rows

cheers

11

Could you please send me the xaml if possible?

12

@akash_javalekar3

Send me an excel attachment with input an doutput required not screenshots please

Cheers

13

Hi @akash_javalekar3

Check below for your reference

Hope this may helps you

Thanks,
Srini

14

Tried this approach but facing issue

15

@akash_javalekar3

Can you please explain what issues you are facing?

Cheers

16

in if condition its showing object reference not to set an instance of object.

17

@akash_javalekar3

You will get that error when any variable is not initiated

please check this…sample attached…check practice test 4.1.1.xaml

Flow:

image
image518×651 21 KB

image
image522×639 17.1 KB

image
image519×643 17.3 KB

image
image519×653 16.6 KB

Output:
image

BlankProcess - Copy (3).zip (13.1 KB)

cheers

18

@akash_javalekar3

  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…!

19

@akash_javalekar3

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…!