Dataverse sql server - nvarchar column not showing in proper format in excel vba recordset field..

Question

Hi,
We are trying to fetch record from MS Dataverse using ODBC driver 17/18 for Sql Server into Excel using Macro(VBA) code.
Issue: There are some columns with type nvarchar which are not showing in proper format as it shows for other columns.
I checked the Recordset for nvarchar type column field is showing Field Type = 202 and this Field DataType Enum = adVarWChar and ADO type = A null-terminated Unicode character string.

Code used to fetch Record:

Dim con1 As Object
Dim rs1 As ADODB.Recordset
Dim strSql As String

Dim conStr As String

Set con1 = CreateObject("ADODB.Connection")

con1.Open "Driver={ODBC Driver 17 for SQL Server};DataTypeCompatibility=80;Server=xxxxx.crm.dynamics.com;Initial Catalog=xxxxx;TrustServerCertificate=Yes;Authentication=ActiveDirectoryInteractive;Connection Timeout=60;"

strSql = "select * from Account"

con1.CommandTimeout = 0

Set rs1 = New ADODB.Recordset
rs1.ActiveConnection = con1
rs1.CursorType = adOpenForwardOnly
rs1.LockType = adLockReadOnly
rs1.Source = strSql

rs1.Open

Do Until rs1 Is Nothing
   WorksheetData rs1
   Set rs1 = rs1.NextRecordset
Loop

con1.Close
Set rs1 = Nothing

Record Value of nvarchar column:

Any Help on this will be appreciated.
Thank You in advance.

Answer 1

The issue you are experiencing is likely due to the fact that the nvarchar columns are being retrieved as Unicode strings, but Excel is not able to properly handle these types of strings and is displaying them as garbled text.

There are a few different ways to handle this issue:

1. You can convert the nvarchar columns to varchar before retrieving them from the Dataverse. This can be done by using the CAST or CONVERT function in your SQL query. For example:

strSql = "select CAST(column_name as varchar(max)) as column_name from Account"

1. You can change the ADO type in your VBA code to adVarChar, which will cause the nvarchar columns to be retrieved as regular strings. rs1.Fields("column_name").Type = adVarChar
2. You can convert the nvarchar columns to a different data type that Excel can handle, such as a number or a date. This can be done by using the CAST or CONVERT function in your SQL query.
3. You can use a third-party tool that can handle the Unicode strings and display them correctly in Excel, for example, the "Microsoft Access Database Engine" which is able to handle Unicode data.

It's worth noting that converting nvarchar columns to varchar could cause data loss if the column has characters that are not supported by the varchar data type.