DEV Community: Wild Cat

Convert DataTable to Array, List and Dictionary in UiPath

Wild Cat — Sat, 12 Jul 2025 12:47:24 +0000

Convert a specific column of a DataTable to an Array

'When no search condition is specified
dt.AsEnumerable.Select(Function(x) x("ColumnName").ToString).ToArray

'When a search condition is specified (e.g., excluding empty fields
dt.AsEnumerable.Where(Function(x) x("ColumnName").ToString <> "").Select(Function(x) x("ColumnName").ToString).ToArray

Convert a specific column of a DataTable to a List

'When no search condition is specified
dt.AsEnumerable.Select(Function(x) x("ColumnName").ToString).ToList

'When a search condition is specified (e.g., excluding empty fields
dt.AsEnumerable.Where(Function(x) x("ColumnName").ToString <> "").Select(Function(x) x("ColumnName").ToString).ToList

Convert specific columns of a DataTable to a Dictionary

'When no search condition is specified
dt.AsEnumerable.ToDictionary(Function(x) x("KeyColumn").ToString, Function(x) x("ValueColumn").ToString)

'When a search condition is specified (e.g., excluding empty fields
dt.AsEnumerable.Where(Function(x) x("KeyColumn").ToString <> "" AndAlso x("ValueColumn").ToString <> "").ToDictionary(Function(x) x("KeyColumn").ToString, Function(x) x("ValueColumn").ToString)

Convert column names and values of a DataRow to a Dictionary

'Convert from a DataRow
dr.Table.Columns.Cast(Of DataColumn).ToDictionary(Function(x) x.ColumnName, Function(x) dr(x.ColumnName).ToString)

'Convert from the first row of a DataTable
dt.Columns.Cast(Of DataColumn).ToDictionary(Function(x) x.ColumnName, Function(x) dt.Rows(0)(x.ColumnName).ToString)

Convert values of a DataRow to an Array

'Convert from a DataRow
dr.ItemArray.Select(Function(x) x.ToString).ToArray

'Convert from the first row of a DataTable
dt.Rows(0).ItemArray.Select(Function(x) x.ToString).ToArray

Convert values of a DataRow to a List

'Convert from a DataRow
dr.ItemArray.Select(Function(x) x.ToString).ToList

'Convert from the first row of a DataTable
dt.Rows(0).ItemArray.Select(Function(x) x.ToString).ToList

Convert column names of a DataTable to an Array

dt.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToArray

Convert column names of a DataTable to a List

dt.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToList

Connect MS Access to SQL Server using ADO

Wild Cat — Sun, 13 Oct 2024 01:15:19 +0000

Summary

This article explains how to connect MS Access to SQL Server using ADO. It also covers how to measure and compare performance across different CursorLocation, CursorType, and LockType settings.

Common procedures

The following reference needs to be added to the VBA project references.
Microsoft ActiveX Data Objects x.x Library

'Set values of SQL Server
Private Const ServerName   As String = "myServerName"
Private Const DatabaseName As String = "myDatabaseName"
Private Const UserID       As String = "myID"
Private Const Password     As String = "myPassword"

Public Sub OpenConnection(ByRef cn As ADODB.Connection)    
    cn.ConnectionTimeout = 100 '100 seconds
    cn.CommandTimeout = 100    '100 seconds

    '# SQL Server Authentication Mode
    cn.ConnectionString = "Provider=SQLOLEDB;" & _
                          "Server=" & ServerName & ";" & _
                          "Database=" & DatabaseName & ";" & _
                          "USER ID=" & UserID & ";" & _
                          "PASSWORD=" & Password & ";"

    '# Windows Authentication Mode
    'cn.ConnectionString = "Provider=SQLOLEDB;" & _
    '                      "Server=" & ServerName & ";" & _
    '                      "Database=" & DatabaseName & ";" & _
    '                      "Integrated Security=SSPI;"

    cn.Open    
End Sub

Public Sub OpenRecordsetToRead(ByRef cn As ADODB.Connection, _
                               ByRef rs As ADODB.Recordset, _
                               ByVal sql As String)
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockReadOnly
    rs.ActiveConnection = cn
    rs.Source = sql
    rs.Open
End Sub

Public Sub OpenRecordsetToUpdate(ByRef cn As ADODB.Connection, _
                                 ByRef rs As ADODB.Recordset, _
                                 ByVal sql As String)
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.ActiveConnection = cn
    rs.Source = sql
    rs.Open
End Sub

Public Sub CloseRecordset(ByRef rs As ADODB.Recordset)
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
    End If
End Sub

Public Sub CloseConnection(ByRef cn As ADODB.Connection)
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If
End Sub

Microsoft documentation on ConnectionString of ADODB.Connection
Microsoft OLE DB Provider for SQL Server Overview

Use SQL SELECT statement

Note: The following code utilizes the common procedures provided at the top of this page.

Public Sub GetRecordset()

    On Error GoTo ErrHandler

    Dim sql As String

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    sql = "SELECT * FROM TEST_TABLE"

    Call OpenConnection(cn)
    Call OpenRecordsetToRead(cn, rs, sql)

    If rs Is Nothing Or (rs.BOF And rs.EOF) Then
        Exit Sub
    End If

    Do Until rs.EOF
        Debug.Print rs.Fields(0).Value 'Show 1st filed of table
        Debug.Print rs.Fields(1).Value 'Show 2nd filed of table
        rs.MoveNext
    Loop

    Call CloseRecordset(rs)
    Call CloseConnection(cn)

    Exit Sub

ErrHandler:
    Call CloseRecordset(rs)
    Call CloseConnection(cn)
    Debug.Print "ErrNumber:" & Err.Number & " " & Err.Description

End Sub

Use SQL statement of INSERT, UPDATE and DELETE

Note: The following code utilizes the common procedures provided at the top of this page.

Public Sub ExecuteSQL()

    On Error GoTo ErrHandler

    Dim cn As New ADODB.Connection
    Dim sql As String

    Call OpenConnection(cn)

    sql = "INSERT INTO TEST_TABLE (No, FirstName, LastName) Values(1,'John','Smith')"

    'cn.BeginTrans '#Begin transaction    
    cn.Execute sql    
    'cn.CommitTrans '#Commit transaction

    Call CloseConnection(cn)

    Exit Sub

ErrHandler:
    'cn.RollbackTrans '#Rollback
    Call CloseConnection(cn)
    Debug.Print "ErrNumber:" & Err.Number & " " & Err.Description
End Sub

Properties of ADODB.Recordset

ADODB.Recordset has three properties to set.

CursorLocation
CursorType
LockType

Microsoft documentation on ADO settings

If the properties are configured incorrectly, they will be automatically adjusted to the appropriate settings.

The actual properties returned to your application depend on the data provider and the database.

The following table illustrates how these properties are modified in my environment. The properties modified are highlighted in red.

The following code generates this table.

Note1: The following code utilizes the common procedures provided at the top of this page.

Note2:The code below measures execution time by reading all table records. I suggest testing this with a smaller table first to avoid long wait times.

Public Sub OutputAdoRecordsetProperty()

    On Error GoTo ErrHandler

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim arrayCursorLocation() As Variant
    Dim arrayCursorType()     As Variant
    Dim arrayLockType()       As Variant
    Dim currentCursorLocation As Variant
    Dim currentCursorType     As Variant
    Dim currentLockType       As Variant
    Dim fieldsResult          As String
    Dim currentResult         As String
    Dim filePathResult        As String
    Dim startTime             As Double
    Dim endTime               As Double
    Dim executionTime         As Double
    Dim currentField          As Long
    Dim currentValue          As Variant

    arrayCursorLocation = Array(adUseClient, adUseServer)

    arrayCursorType = Array(adOpenDynamic, _
                            adOpenForwardOnly, _
                            adOpenKeyset, _
                            adOpenStatic)

    arrayLockType = Array(adLockBatchOptimistic, _
                          adLockOptimistic, _
                          adLockPessimistic, _
                          adLockReadOnly)

    filePathResult = CurrentProject.Path & "\AdoRsPropertyAbility.csv"

    fieldsResult = "Setting CursorLocation," & _
                   "Setting CursorType," & _
                   "Setting LockType," & _
                   "Actual CursorLocation," & _
                   "Actual CursorType," & _
                   "Actual LockType," & _
                   "adAddNew," & _
                   "adApproxPosition," & _
                   "adBookmark," & _
                   "adDelete," & _
                   "adFind," & _
                   "adHoldRecords," & _
                   "adIndex," & _
                   "adMovePrevious," & _
                   "adNotify," & _
                   "adResync," & _
                   "adSeek," & _
                   "adUpdate," & _
                   "adUpdateBatch," & _
                   "RecordCount, " & _
                   "Execution time"

    Call WriteCurrentResult(filePathResult, fieldsResult)

    Dim sql As String
    sql = "SELECT * FROM TEST_TABLE"

    For Each currentCursorLocation In arrayCursorLocation
        For Each currentCursorType In arrayCursorType
            For Each currentLockType In arrayLockType

                Call OpenConnection(cn)

                startTime = Timer

                rs.CursorLocation = currentCursorLocation
                rs.CursorType = currentCursorType
                rs.LockType = currentLockType
                rs.ActiveConnection = cn
                rs.Source = sql
                rs.Open

                'Setting Cursor Location
                currentResult = GetCursorLocation(currentCursorLocation) & ","

                'Setting CursorType
                currentResult = currentResult & GetCursorType(currentCursorType) & ","

                'Setting LockType
                currentResult = currentResult & GetLockType(currentLockType) & ","

                'Actual  CursorLocation
                currentResult = currentResult & GetCursorLocation(rs.CursorLocation) & ","

                'Actual  CursorType
                currentResult = currentResult & GetCursorType(rs.CursorType) & ","

                'Actual  LockType
                currentResult = currentResult & GetLockType(rs.LockType) & ","

                'CursorOptionEnum adAddNew
                currentResult = currentResult & rs.Supports(adAddNew) & ","

                'CursorOptionEnum adApproxPosition
                currentResult = currentResult & rs.Supports(adApproxPosition) & ","

                'CursorOptionEnum adBookmark
                currentResult = currentResult & rs.Supports(adBookmark) & ","

                'CursorOptionEnum adDelete
                currentResult = currentResult & rs.Supports(adDelete) & ","

                'CursorOptionEnum adFind
                currentResult = currentResult & rs.Supports(adFind) & ","

                'CursorOptionEnum adHoldRecords
                currentResult = currentResult & rs.Supports(adHoldRecords) & ","

                'CursorOptionEnum adIndex
                currentResult = currentResult & rs.Supports(adIndex) & ","

                'CursorOptionEnum adMovePrevious
                currentResult = currentResult & rs.Supports(adMovePrevious) & ","

                'CursorOptionEnum adNotify
                currentResult = currentResult & rs.Supports(adNotify) & ","

                'CursorOptionEnum adResync
                currentResult = currentResult & rs.Supports(adResync) & ","

                'CursorOptionEnum adSeek
                currentResult = currentResult & rs.Supports(adSeek) & ","

                'CursorOptionEnum adUpdate
                currentResult = currentResult & rs.Supports(adUpdate) & ","

                'CursorOptionEnum adUpdateBatch
                currentResult = currentResult & rs.Supports(adUpdateBatch) & ","

                'RecordCount
                currentResult = currentResult & rs.RecordCount & ","

                'Measure execution time
                Do Until rs.EOF
                    For currentField = 0 To rs.Fields.Count - 1
                        currentValue = rs.Fields(currentField).Value
                    Next
                    rs.MoveNext
                Loop
                endTime = Timer
                executionTime = endTime - startTime
                currentResult = currentResult & executionTime

                Call CloseRecordset(rs)

                Call WriteCurrentResult(filePathResult, currentResult)

                Call CloseConnection(cn)

            Next currentLockType
        Next currentCursorType
    Next currentCursorLocation

    MsgBox "Output has been completed.", vbInformation

    Exit Sub

ErrHandler:
    Call CloseRecordset(rs)
    Call CloseConnection(cn)
    MsgBox "ErrNumber:" & Err.Number & " " & Err.Description

End Sub

Private Function GetCursorLocation(ByVal lngCursorLocation As Long) As String
    Select Case lngCursorLocation
        Case 2
            GetCursorLocation = "adUseServer"
        Case 3
            GetCursorLocation = "adUseClient"
    End Select
End Function

Private Function GetCursorType(ByVal lngCursorType As Long) As String
    Select Case lngCursorType
        Case 0
            GetCursorType = "adOpenForwardOnly"
        Case 1
            GetCursorType = "adOpenKeyset"
        Case 2
            GetCursorType = "adOpenDynamic"
        Case 3
            GetCursorType = "adOpenStatic"
    End Select
End Function

Private Function GetLockType(ByVal lngLockType As Long) As String
    Select Case lngLockType
        Case 1
            GetLockType = "adLockReadOnly"
        Case 2
            GetLockType = "adLockPessimistic"
        Case 3
            GetLockType = "adLockOptimistic"
        Case 4
            GetLockType = "adLockBatchOptimistic"
    End Select
End Function

Private Sub WriteCurrentResult(ByVal filePathResult As String, _
                               ByVal currentResult As String)
    Open filePathResult For Append As #1
    Print #1, currentResult
    Close #1
End Sub

Connect to SQL Server using ADO And ODBC connection string

The code below shows how to connect to SQL Server using ADO And an ODBC connection string.

It is not intended for standalone tables in SQL Server. Rather, it manages the connection between Access tables and SQL Server tables, as in the following example.

Select records by joining Access tables with SQL Server tables.
Insert records from an Access table into a SQL Server table.

'Set values of SQL Server
Private Const ServerName   As String = "myServerName"
Private Const DatabaseName As String = "myDatabaseName"
Private Const UserID       As String = "myID"
Private Const Password     As String = "myPassword"

'Create ODBC connection string
Public Function GetConODBC() As String
    GetConODBC = _
        "[ODBC" & _
        ";DRIVER=SQL Server" & _
        ";SERVER=" & ServerName & _
        ";DATABASE=" & DatabaseName & _
        ";UID=" & UserID & _
        ";PWD=" & Password & _
        "]."
End Function

'Get records using ADO and ODBC connection string
Public Sub GetRecordset_ADO_ODBC()

    Dim sql As String

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection

    sql = "SELECT * FROM " & GetConODBC & "SQLServerTable;"

    rs.Open sql, cn

    Do Until rs.EOF
        Debug.Print rs.Fields(0).Value 'Show 1st filed of table
        Debug.Print rs.Fields(1).Value 'Show 2nd filed of table
        rs.MoveNext
    Loop

    rs.Close
    cn.Close

End Sub

'Get records by joining Access table with SQLServer table
Public Sub GetRecordsetByJoiningAccessWithSQLServer_ADO_ODBC()

    Dim sql As String

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection

    sql = _
        "SELECT AccessTable.A, SQLServerTable.B " & _
        "FROM AccessTable " & _
        "LEFT JOIN " & GetConODBC & "SQLServerTable " & _
        "ON AccessTable.ID = SQLServerTable.ID;"

    rs.Open sql, cn

    Do Until rs.EOF
        Debug.Print rs.Fields(0).Value 'Show 1st filed of table
        Debug.Print rs.Fields(1).Value 'Show 2nd filed of table
        rs.MoveNext
    Loop

    rs.Close
    cn.Close

End Sub

'Insert into SQL Server table from Access table
Public Sub InsertIntoSQLServerFromAccess_ADO_ODBC()

    Dim sql As String

    Dim cn As ADODB.Connection

    Set cn = CurrentProject.Connection

    sql = _
        "INSERT INTO " & GetConODBC & "SQLServerTable " & _
        "SELECT * FROM AccessTable;"

    cn.Execute sql

    cn.Close

End Sub

Automate WinMerge comparison via Excel VBA Tool

Wild Cat — Sat, 10 Aug 2024 13:57:12 +0000

Summary

Via this tool, WinMerge compares files in the specified folder including those in subfolders, and outputs the comparison results in HTML format.

The target files are those with the same name in the same folder structure.

How to use

Input setting values in 'Tool' sheet in the tool.
Push the button on 'Tool' sheet in the tool.
WinMerge exports comparison results in HTML format.

Setting

Source code

'********************************************************************
'[Summary]
' Via this tool, WinMerge compare files in the specified folder
' including those in subfolders, and output the comparison results
' in HTML format.
' The target files are those with the same name in the same
' folder structure.
'********************************************************************

Option Explicit

'Declare Sleep Function
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal MilliSecounds As LongPtr)

'Declare a FileSystemObject variable as a module-level variable.
Dim FSO As Object

'********************************************************************
'[Summary]
' Main Procedure
' 1.Validate input values
' 2.Execute WinMerge
'********************************************************************
Sub ExportWinMergeReport()

    Dim startTime As Date: startTime = Now

    'Assign setting values to variables
        With ThisWorkbook.Sheets("Tool")

            Dim inputFolderPath1 As String 'Source folder 1
            Dim inputFolderPath2 As String 'Source folder 2
            Dim outputFolderPath As String 'Destination folder
            Dim filePathWinMerge As String 'WinMerge EXE file

            inputFolderPath1 = .Range("B2").Value
            inputFolderPath2 = .Range("B3").Value
            outputFolderPath = .Range("B4").Value
            filePathWinMerge = .Range("B5").Value

        End With

    'Validate input values
        Call ValidateInputValues(inputFolderPath1, inputFolderPath2, outputFolderPath, filePathWinMerge)

    'Execute WinMerge
        Call ExecWinMerge(inputFolderPath1, inputFolderPath2, outputFolderPath, filePathWinMerge)

        Set FSO = Nothing

    'Completion Message
    MsgBox "Completed" & vbLf & "Time " & Format(Now - startTime, "hh:mm:ss"), vbInformation

End Sub

'********************************************************************
'[Summary]
' Sub procedure to validate input values
'
'[Argument]
' inputFolderPath1 : Source folder 1
' inputFolderPath2 : Source folder 2
' outputFolderPath : Destination folder
' filePathWinMerge : WinMerge EXE file
'********************************************************************
Sub ValidateInputValues(ByVal inputFolderPath1 As String, _
                        ByVal inputFolderPath2 As String, _
                        ByVal outputFolderPath As String, _
                        ByVal filePathWinMerge As String)

    'Validate that values are entered in each field
        If inputFolderPath1 = "" Then
            MsgBox "The first source folder is not entered in the input field.", vbInformation
            End
        End If

        If inputFolderPath2 = "" Then
            MsgBox "The second source folder is not entered in the input field.", vbInformation
            End
        End If

        If outputFolderPath = "" Then
            MsgBox "The output folder is not entered in the input field.", vbInformation
            End
        End If

        If filePathWinMerge = "" Then
            MsgBox "The file Path of WinMerge EXE file is not entered in the input field.", vbInformation
            End
        End If

    'Validate that folders and files exist
        Set FSO = CreateObject("Scripting.FileSystemObject")

        If Not FSO.FolderExists(inputFolderPath1) Then
            MsgBox "The first source folder doesn't exit.", vbInformation
            Set FSO = Nothing
            End
        End If

        If Not FSO.FolderExists(inputFolderPath2) Then
            MsgBox "The second source folder doesn't exit.", vbInformation
            Set FSO = Nothing
            End
        End If

        If Not FSO.FileExists(filePathWinMerge) Then
            MsgBox "WinMerge EXE file doesn't exist", vbInformation
            Set FSO = Nothing
            End
        End If

End Sub

'********************************************************************
'[Summary]
' Sub procedure to execute WinMerge
'
'[Argument]
' inputFolderPath1 : Source folder 1
' inputFolderPath2 : Source folder 2
' outputFolderPath : Destination folder
' filePathWinMerge : WinMerge EXE file
'********************************************************************
Sub ExecWinMerge(ByVal inputFolderPath1 As String, _
                 ByVal inputFolderPath2 As String, _
                 ByVal outputFolderPath As String, _
                 ByVal filePathWinMerge As String)

    Dim inputFilePath1 As String
    Dim inputFilePath2 As String
    Dim outputFilePath As String
    Dim exeCode As String
    Dim oFile As Object
    Dim exeTimeLimit As Date
    Dim canContinue As Boolean
    Dim exeResult As Long

    'Create the folder to export results of WinMerge
    If Not FSO.FolderExists(outputFolderPath) Then
        FSO.CreateFolder (outputFolderPath)
    End If

    'Execute WinMerge
    For Each oFile In FSO.GetFolder(inputFolderPath1).Files

        'Check for the existence of the comparison target file.
        'If the same file exists, execute WinMerge.
        If FSO.FileExists(inputFolderPath2 & "\" & oFile.Name) Then

            inputFilePath1 = inputFolderPath1 & "\" & oFile.Name
            inputFilePath2 = inputFolderPath2 & "\" & oFile.Name
            outputFilePath = outputFolderPath & "\" & FSO.GetBaseName(oFile) & ".htm"

            exeCode = filePathWinMerge & " " & _
                      inputFilePath1 & " " & _
                      inputFilePath2 & _
                      " /or " & outputFilePath & _
                      " /noninteractive /minimize /xq /e /u"

            exeResult = Shell(exeCode, vbMinimizedNoFocus)

            If exeResult = 0 Then
                MsgBox "Failed to execute WinMerge.", vbInformation
                Set FSO = Nothing
                End
            End If

            'Wait for the output of the comparison result.
            'Maximum time is 10 secounds.
                exeTimeLimit = Now + TimeSerial(0, 0, 10)
                canContinue = False

                Do
                    If FSO.FileExists(outputFilePath) Then
                        canContinue = True
                        Exit Do
                    End If
                    Call Sleep(100) 'Wait 100 milliseconds
                Loop Until canContinue Or Now > exeTimeLimit

                If Not canContinue Then
                    MsgBox "Failed to output the following report file." & vbLf & vbLf & outputFilePath, vbInformation
                    Set FSO = Nothing
                    End
                End If

        End If

    Next

    'Execute WinMerge for subfolders as well.
    Dim subfolder As Object
    Dim subInputFolderPath1 As String
    Dim subInputFolderPath2 As String
    Dim subOutputFolderPath As String

    For Each subfolder In FSO.GetFolder(inputFolderPath1).Subfolders

        'If a subfolder exists, execute recursive processing.
        If subfolder.Name <> "" Then
            subInputFolderPath1 = subfolder.Path
            subInputFolderPath2 = inputFolderPath2 & "\" & subfolder.Name
            subOutputFolderPath = outputFolderPath & "\" & subfolder.Name
            Call ExecWinMerge(subInputFolderPath1, subInputFolderPath2, subOutputFolderPath, filePathWinMerge)
        End If

    Next

End Sub

Command line parameters of WinMerge

The following are the command line parameters of WinMerge used in this tool.

/or
Specifies an optional output folder to save comparison result files.

/noninteractive
Exits WinMerge after the process is completed.

/minimize
Starts WinMerge as a minimized window.

/xq
Does not show the message about identical files.

/e
Enables you to close WinMerge with a single Esc key press.

/u
Prevents WinMerge from adding either path to the Most Recently Used list.