UiPath 2024.10 – Fixing Data Type Mismatch in Bulk Insert

Help Studio
, , ,
1

:rocket: UiPath 2024.10 – Fixing Data Type Mismatch in Bulk Insert :rocket:

If you’re using Bulk Insert in UiPath 2024.10, you may encounter the error:

:warning: “Columns have data type mismatch.”

:mag: Why Does This Happen?
Even if column names match, their data types might differ between your DataTable and the database table, causing the failure.

:white_check_mark: How to Fix It?

:point_right: Clone the DataTable & Convert Columns to String

:small_blue_diamond: VB NET:

For Each col As DataColumn In clonedDT.Columns
col.DataType = GetType(String)
Next

:small_blue_diamond: LINQ:

clonedDT = originalDT.AsEnumerable().
Select(Function(row) clonedDT.LoadDataRow(row.ItemArray.Select(Function(field) field.ToString()).ToArray(), False)).
CopyToDataTable()

:point_right: Move Data to the Cloned DataTable

:small_blue_diamond: For Each Row + Add Data Row:

For Each row As DataRow In originalDT.Rows
clonedDT.Rows.Add(row.ItemArray)
Next

:small_blue_diamond: LINQ:

clonedDT = originalDT.AsEnumerable().CopyToDataTable()

:point_right: Ensure Database Columns Are NVARCHAR for compatibility.

By ensuring data type consistency, you can seamlessly execute Bulk Insert in UiPath 2024.10 without errors.

2

This reminds me of https://www.youtube.com/watch?v=Nz8ssH7LiB0. Transforming all data to a string/nvarchar is like putting all of the shapes in the square hole.

A person is placing a red triangular block into a matching shape slot on a white shape sorter toy. (Bijschrift door AI)
A person is placing a red triangular block into a matching shape slot on a white shape sorter toy. (Bijschrift door AI)397×522 28.4 KB

This might be ‘good enough’ if you have a simple use case, but I wouldn’t recommended doing it like this in more complex situations. It can mess up the data that you had.

For true data type consistency, keep the database columns as you defined them, find out which columns didn’t have the correct data type and convert the data in there carefully. Put the shapes in the right hole!