Excel empty cells

Help
1

I have ID’s in my excel sheet, each id has other corresponding details related to the ID, there are cells in the excel sheet without any id but with other details,
I want to filter out only the id’s and its details, not the other empty cells in between. How to approach?

2

Hi @shreyaank

If I got it right, you want to get the rows when the ID column when ID is not empty.

Try this:

  • read the Excel to a DataTable

  • create new DataTable with your filtered results with an assign activity by using the following code:
    yourNewDataTable = yourReadDataTable.Select("[columnID]<>''").CopyToDataTable()

Don’t forget to initialize yourNewDataTable in the variables pane with
New System.Data.DataTable

1 Like
3

It does work, but its not considering the first 7-8 rows inspite of them having column ID, its skipping the first 7-8 entries with column ID and then extracting the rest of the entries with column id …any idea why?

4

My first idea would be to check whether you loaded the entire range with Read Range activity in the first place.
You can check it by re-saving the file right away with a different name without filtering.

Could you also specify how you know it skips those rows? Did you output the result to a table or saved it again to a file?

5

Yes I am looping through a datatable and comparing with the source excel, there are missing rows. Not sure why! The same datatable without the filter displays all the entries, but the one that’s filtered skips some of the column id’s