Only delete empty rows in Excel spreadsheet

Help StudioX
,
1

Hello,
I’m using spreadsheet for a program in StudioX.
I have 135 rows with header in total with data. Within the 135 rows, I see 8 rows in between at random places to be completely blank row.
For example: Rows 1 is header. Row 2 to row 10 filled completely. Row 11 is blank and again row 17 is blank. Likewise I have few blank rows.
can you tell be how to delete the blank rows completely using StudioX program ?

1 Like
2

Us that are always the same rows that are blank
You can use the delete tow activity and mention the specific rows in it

Else you can Loop through the data and check what all rows are blank then delete them
You can read the data, perform the operation and paste the output data using Write to Excel

3
  1. delete row activity will not help as each time, blank rows appear in different row numbers
  2. the moment StudioX finds that the entire row is blank, it shows execution on it’s own.
  3. read the data and paste will also be like #2 above
4

Hey,

I hope this will help you…

After using read range and store the values in datatable, use this one in assign activity.

DataTable = DataTable.AsEnumerable.Where(Function(x) Not String.IsNullOrEmpty(x(0).ToString)).CopyToDataTable

This will check for each row - if 1st column is empty or not.
It will add all the rows which are not empty in the 1st column.

Note: You can replace 1st column with the primary key where you feel it will not be null.

1 Like
5

Hi

Here is the package that has an activity called “Delete empty rows”. It may help you to remove the empty rows.

Cheers @Prinal_C

1 Like
6

@Prinal_C , here is your solution.

  1. Read excel file with read range.
  2. Use filter Data Table.
    2.1. read your excel manual like A=0, B=1, C=2… you may know how many columns may be there on your excel sheet.
    2.1. Filter rows side: enter your column index like 0 or 10 on column text.
    2.2. Keep the operation as " is empty".
    2.3. Select the Remove radio button.
  3. Output Columns side:
    3.1 Select the Keep radio button.
    3.2. mention the column to be kept as it is.
    3.3 Ok.
  4. Write range to re-write.
7

Hi.

I didn’t ask, but it says what I wanted to do.
Your answer solved all my problems.

You are a genius!
Thank you very much! :heart:

8

It showing that not string is not declared