How to check if certain column range of a databale is empty

Help Studio
1

I have this excel file that I read range into. It contains many columns that I need to use. Let’s say it contains around 25 columns. I need to check if the entire columns 16-25 values are empty or whitespace, if yes I’ll skip the file, if not do the process. I know how to check columns individually, but putting multiple “AND” Condition with all the required columns is quite bad to look at. I hope you can help me. Thank you.

2

Hi Raffy,

If you are getting the names of all the columns in a string array or you are getting the range of column objects in DataColumns object array, I would suggest to use a For Each Row in Datatable activity where you can assign a index counter variable called rowCounter and within that you can use another loop for iterating over the array, there you can put a condition simply stating the Datatable.Rows(rowCounter).Item(currentColItem).Equals(“”) Or String.IsEmpty(Datatable.Rows(rowCounter).Item(currentColItem))

If the condition is True, assign a boolean variable as True and exit the loops. Once, all the loops are completed, you can check the value of the flag you can perform the rest of the activities

Ensure that before entering the loops the initial value of the boolean variable is False. Hope it helps you.

3

Thanks, just tweaked it a little to fit with mine. I just also want to ask. I am considering on splitting my other excel file into two datatable. I am read ranging the file and it always have this empty column in the middle. Can i ask if you know if it is possible to split the datatable into two, using that empty column as the delimiter? Thanks

4

Hi,

Glad it got resolved. Coming to your second query, first you would need to have a string array prepared with the column names that you want to store in first splitted data table and also store the second set of column names in a second string array variable.

If you dont have them handy and let say it needs to be fetched dynamically, I would suggest first get all the column names from your excel file. To get this you can use Read Range and populate a data table first, then you can iterate within <datatable_variable>.Columns and within that use item.ColumnName to retrieve the current column and store in into a string array or a string list. This variable will have all your column names. From here you can separate your list into two sets of array variables consisting of columns that will be present in your splitted data tables.

Next create two data table variables to hold the splitted data and there for each data table variable use an assign activity:

<splitted_datatable_variable1> = <original_datatable_variable>.DefaultView.ToTable(False,<array_of_first_set_of_column_names>)

<splitted_datatable_variable2> = <original_datatable_variable>.DefaultView.ToTable(False,<array_of_second_set_of_column_names>)

This way you will be able to get two data tables with data from original datatable splitted based on that delimiter.

5

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