Powershell datatable created a null row issue

Question

I'm writing a Powershell script to create an unique contacts in datatable. The steps are as following:

I first extracted the data from a CSV and put the data in $dt_uniqueContacts datatable (with just a single column). Then Append more records from other source using Rows.Add

$dt_uniqueContacts.Rows.Add($arow) | Out-Null

This expanded the original $dt_uniqueContacts size. I have "Out-Null" in every Rows.Add operations to avoid creating null row as suggested by other thread.

After this, I applied the -unique to make $dt_uniqueContacts unique and overwrote it back.

$dt_uniqueContacts = $dt_uniqueContacts.Select("", "Contacts ASC") | Select-Object Contacts -Unique

The issue is after the unique operation, the $dt_uniqueContacts created an extra row at the beginning. You cannot display it and it doesn't affect the rows.count, however, when doing the following, it will throw an error on first loop (against that null row). I don't know how to remove that row nor how to detect with a "If" statement, null or "" not working.

foreach ($r in $dt_uniqueContacts) {
    $aContact = $r.Contacts
    if ($aContact.Contains("'")) {...

it throw this error:

Method invocation failed because [System.DBNull] does not contain a method named 'Contains'.

The issue happened only when any function is included (ie. .Contains in this case). If I skip the .Contains line, the $null row in $dt_uniqueContacts foreach will not generate an error and executed like there is no $null row.

Any suggestions?

Thanks all in advance.

Answer 1

I use what @JosefZ suggested in the comments, detected by using DBNull before the details, like ->

foreach ($r in $dt_uniqueContacts) { 
  $aContact = $r.Contacts 
  if ([System.DBNull]::Value -ne $aContact) { 
    $filter = "FullName = '$($aContact.Replace("'", "''"))'" 
    $arow = $dt_resources.Select($filter)
    ....