How to detect for empty field in excel and throw an error to notify user

Help Activities
1

Dear all,

Good morning,

i would like to design a process to check for string is null for each row data.

Identify it and notify user which row data is empty and throw error.

i am now use the “String.IsNullOrEmpty(row(“DELIVERY DATE”).ToString)” to check one by one.

currently it can only detect for one row and throw error only. How can i set it to detect multiple empty field for a row, throw error and notify user on multiple empty field.

image
image903×145 3.82 KB

image
image1366×768 79 KB

Kindly advise.

thanks

2

this sounds perfect for the filter data table activity!

you can set up whatever columns you want with whatever conditions (in this case, is empty)
image

you can loop through the output datatable and handle the empty fields in the rows as needed.

if you would like to retain the index number of the rows that match your criteria relative to the original table, i would suggest using the .Select function and creating your own query DataTable.Select Method (System.Data) | Microsoft Learn and then using the datatable.rows.indexOf function with the original datatable to get the original row number (DataRowCollection.IndexOf(DataRow) Method (System.Data) | Microsoft Learn)

3

@YashBrahmbhatt
what i want is to use for each row to loop over the currentDT

use if activity , to set condition to check if there is empty field, then use " throw" activity for exception.

and have a message box to notify which row item is empty? (like BA REF.CODE & BA PIC are empty, please fill in)

i will need to have this kind of verification check point before send email to notify for delivery.

but now my problem is i can only use the string null condition to check and detect the row item one by one, and throw exception.

so if let said my first activity is to check for DELIVERY DATE column, and it is empty then it will throw for exception and wont proceed for next checking.

even i set the throw exception to be in the end of the checking, i still dont know how to get the message box to show which row item is null when there is more than one.

hope above clarifiy.

i dont think for case use filter data table can help.

thanks

4

Hmmmm, try something like
Main.xaml (13.6 KB)

5

Find some starter help here:

Get_RowIdx-ColIdx_AnyBlankCells.xaml (9.3 KB)
grafik
grafik

ColumnNamesForBlanks.xaml (8.5 KB)
grafik
grafik

In case of we want to have a report and a Boolean to check if there are blanks in the datatable we can do:
grafik
grafik
grafik
grafik

(From i In Enumerable.Range(0, dtData.Rows.Count)
From j In Enumerable.Range(0, dtData.Columns.Count)
Where isNothing(dtData.Rows(i)(j)) OrElse String.IsNullOrEmpty(dtData.Rows(i)(j).toString.Trim)
Let ra = New Object(){i,j, dtData.Columns(j).Columnname}
Select dtReport.Rows.Add(ra)).Count > 0
1 Like
6

@YashBrahmbhatt

received with many thanks.

Can i have your project dependencies, as there is missing activity.?

image

7

@YashBrahmbhatt

i have manage to update the project dependencies and run the process.

Could you advise how can i output the dictionary ?

i want to get the statement like below, but i need the column name.

image

Thanks.

8

@ppr

thanks for your project file.

i am quite new here.

so i would like to check with you for the below XAML …the result is what i want, but how can i print it to be show column name? and for the row index how can i do it instead of start from 0? (i want it to follow exactly in my excel )

image
image1037×604 35.6 KB

Thanks

9

@irene0227
just checkout also the other demos, as in some also the columnname is handled / retrieved

just add an offset to the index e.g.

(From i In Enumerable.Range(0, dtData.Rows.Count)
From j In Enumerable.Range(0, dtData.Columns.Count)
Where isNothing(dtData.Rows(i)(j)) OrElse String.IsNullOrEmpty(dtData.Rows(i)(j).toString.Trim)
Let ra = New Object(){i + 2,j, dtData.Columns(j).Columnname}
Select dtReport.Rows.Add(ra)).Count > 0

Let ra = New Object(){i + 2 ,j, dtData.Columns(j).Columnname}

+1 as Excel starts with 1 and not with 0, +1 as typically the first excel row is the column header row = +2