Datatable converting with nulls

Help Robot
,
1

Hi, I have a question. I am trying to handle nulls in a data table for various types. I may have like 2 columns in a csv that need to be converted. Then imported into a remote database

I have read the csv up and there are a large amount of data so I have been using invoke code. This works well if there is no nulls.

Dim column As DataColumn = table.Columns(columnname)

    Dim newcolumn As DataColumn = New DataColumn("temporary", type.GetType("System.datetime"))
    newcolumn.AllowDbNull = true
    table.Columns.Add(newcolumn)
  
    For Each row As DataRow In table.Rows
  	
  		 
            row("temporary") = Convert.ChangeType(row(columnname), type.GetType("System.datetime"))
    Next

    table.Columns.Remove(columnname)
    newcolumn.ColumnName = columnname

And i use to use belareva with perfect null success in the legacy version. But the files are too big ( a million to 10 million ). What ever I do for code or manual activities to add code flows throws up on nullable types like date types or int64’s which are basically the two converted column types I need to do.

I have tried add data column with null checked. Not sure what I am missing.

Any help would be appreciated.

2

Hi,

I think the above should be the following.

If Not String.IsNullOrEmpty(row(columnname).ToString) Then
    row("temporary") =Convert.ChangeType(row(columnname), Type.GetType("System.DateTime"))
Else
    row("temporary") =DbNull.Value
End If

Can you try this?

Regards,

3

This seems to work, now one would have to get the original coulmn index and reset the ordinal. I think that should be easy. Any advice on that?

4

Hi,

It will be as the following, for example.

Dim indexCol As Int32 = table.Columns(columnname).Ordinal
table.Columns.Remove(columnname)
newcolumn.ColumnName = columnname
table.Columns(columnname).SetOrdinal(indexCol)

Regards,

5

Yoichi, thank you so much, you are a god send! Much appreciated this completed a hard project for me.

I think it will be good to check if the column should be nullable, but for this project, today. It will work fine. If you have any advice I would appreciate that, but your advice is perfect. Thank you so much. I have marked you as solution.

1 Like
6

Still getting an issue with INTS on some files

Not sure if code is the issue here or file

Dim indexCol As Int32 = table.Columns(columnname).Ordinal

Dim column As DataColumn = table.Columns(columnname)

    For Each row As DataRow In table.Rows
  	If Not String.IsNullOrEmpty(row(columnname).ToString) Then
  	    row("temporary") =Convert.ChangeType(row(columnname), Type.GetType("System.Int64"))
  	Else
  	    row("temporary") =DbNull.Value
  	End If
    Next

    table.Columns.Remove(columnname)
    table.Columns("temporary").ColumnName = columnname
  table.Columns(columnname).SetOrdinal(indexCol)

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
—> System.FormatException: Input string was not in a correct format.
at System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)
at System.String.System.IConvertible.ToInt64(IFormatProvider provider)

7

Looks to also happen on dates still

8

Hi,

For now, can you try to check content of variable when error occurs using the following code?

Dim indexCol As Int32 = table.Columns(columnname).Ordinal
Dim column As DataColumn = table.Columns(columnname)
For Each row As DataRow In table.Rows
	Try
  	    If Not String.IsNullOrEmpty(row(columnname).ToString) Then
  	        row("temporary") =Convert.ChangeType(row(columnname), Type.GetType("System.Int64"))
  	    Else
  	        row("temporary") =DbNull.Value
  	    End If
	Catch
		Console.WriteLine(row(columnname).ToString)
  	    row("temporary") =DbNull.Value
    End Try
Next
table.Columns.Remove(columnname)
table.Columns("temporary").ColumnName = columnname
table.Columns(columnname).SetOrdinal(indexCol)

Or if you can accept to convert all the non-integer value to null, the following will work.

Dim indexCol As Int32 = table.Columns(columnname).Ordinal
Dim column As DataColumn = table.Columns(columnname)
Dim v As Int64
For Each row As DataRow In table.Rows
  	If Int64.TryParse(row(columnname).ToString, v)   Then
  	    row("temporary") =v
  	Else
  	    row("temporary") =DbNull.Value
  	End If
Next
table.Columns.Remove(columnname)
table.Columns("temporary").ColumnName = columnname
table.Columns(columnname).SetOrdinal(indexCol)

Regards,

9

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