When merging data from different sources, duplicate entries can arise
that need to be cleaned up before further processing. This lesson shows
how a table is passed as JSON to a .NET function, freed of duplicates
there, and the result loaded back as a clean table. A second topic shows how process appendTable and the
AppendRow command can be used to insert table rows directly
via a helper script, without building JSON strings manually.
1. The Screen Layout
The script defines two tables: sampletable with the source
data (intentionally containing duplicates) and cleantable
for the cleaned result. The pushbutton triggers the input script
rm_duplicate.txt.
GuiXT
Table (11,1) (21,65) name="sampletable" title="Examples"
Column "Name" size=20 name="name"
Column "Company" size=20 name="company"
Column "Role" size=20 name="role"
Table (22,1) (32,65) name="cleantable" title="Examples (No Duplicates)"
Column "Name" size=20 name="name"
Column "Company" size=20 name="company"
Column "Role" size=20 name="role"
// Create structure and table variable, then load JSON
CreateStructure V[struct] name company role
CreateTable V[sampletable] include=V[struct]
CopyText fromText="table" toTable="V[sampletable]" -json
// Build test data as JSON array (intentionally contains duplicates)
process appendTable V[sampletable] "Alice Johnson" "Acme Corp" "Manager"
process appendTable V[sampletable] "Bob Smith" "TechHub" "Developer"
process appendTable V[sampletable] "Carol White" "BizCo" "Analyst"
process appendTable V[sampletable] "David Lee" "Acme Corp" "Developer"
process appendTable V[sampletable] "Alice Johnson" "Acme Corp" "Manager"
process appendTable V[sampletable] "Eva Martinez" "SoftWave" "Designer"
process appendTable V[sampletable] "Frank Turner" "TechHub" "Analyst"
process appendTable V[sampletable] "Grace Kim" "BizCo" "Manager"
process appendTable V[sampletable] "Henry Brown" "CloudBase" "Developer"
process appendTable V[sampletable] "Bob Smith" "TechHub" "Developer"
process appendTable V[sampletable] "Isabella Clark" "SoftWave" "Developer"
process appendTable V[sampletable] "James Wright" "Acme Corp" "Designer"
process appendTable V[sampletable] "Karen Scott" "CloudBase" "Analyst"
process appendTable V[sampletable] "Liam Harris" "BizCo" "Developer"
process appendTable V[sampletable] "Mia Thompson" "TechHub" "Manager"
process appendTable V[sampletable] "Carol White" "BizCo" "Analyst"
process appendTable V[sampletable] "Noah Davis" "SoftWave" "Analyst"
process appendTable V[sampletable] "Olivia Moore" "CloudBase" "Designer"
process appendTable V[sampletable] "Peter Wilson" "Acme Corp" "Manager"
process appendTable V[sampletable] "Mia Thompson" "TechHub" "Manager"
Pushbutton (21,69) "Remove Duplicates" process="rm_duplicate.txt"
GuiXT
Parameter table // Table with columns name company role
Parameter name
Parameter company
Parameter role
CreateStructure V[tablerow] name company role
Set V[tablerow.name] "&V[name]"
Set V[tablerow.company] "&V[company]"
Set V[tablerow.role] "&V[role]"
AppendRow V[tablerow] table=V[table]
Return
1.1 Inserting test data with process appendTable
Instead of building the JSON string manually line by line, each test row
is inserted directly into V[sampletable] using
process appendTable. The keyword process calls
a named input script and passes values as parameters — here the table
variable as the first argument, followed by name, company, and role.
The helper script process_appendTable.txt receives the values via
Parameter statements. It first creates a temporary structure
V[tablerow] with the three columns and fills the fields
using Set. Then AppendRow V[tablerow] table=V[table]
appends the completed row to the passed table. This approach is more
readable than manual JSON construction, as no escaping of quotation marks
is required.
2. The input script rm_duplicate.txt
The input script creates the target table, passes
sampletable as JSON to the .NET function and loads the
cleaned result directly into cleantable.
GuiXT
CreateStructure V[struct] name company role
CreateTable V[cleantable] include=V[struct]
CallVB cleantable = DataStructure.Utils.RemoveDuplicates "&V[sampletable]"
return
CallVB cleantable = ... automatically serialises
V[sampletable] as a JSON array and passes the string to the
.NET function. The return value is also a JSON array, which GuiXT
automatically loads into the target table cleantable. The
column names (name, company, role)
must match in both tables and in the JSON.
3. The .NET Code
The function RemoveDuplicates receives the JSON array of the
table, removes duplicate entries and returns a cleaned JSON array.
GetRawText() is used to detect duplicates, returning the
exact JSON text of each element. Two elements are considered identical
when their JSON text matches character for character. A
HashSet<string> keeps track of all previously seen
texts. HashSet.Add() returns true when the
value was new and false when it was already present. Only
new entries are added to the result list.
Imports System.Collections.Generic
Imports System.Text.Json
Namespace DataStructure
Public Class Utils
Public Function RemoveDuplicates(table As String) As String
Dim doc As JsonDocument = JsonDocument.Parse(table)
Dim seen As New HashSet(Of String)()
Dim uniqueElements As New List(Of JsonElement)()
For Each element As JsonElement _
In doc.RootElement.EnumerateArray()
' Serialize each element to a canonical string for comparison
Dim key As String = element.GetRawText()
If seen.Add(key) Then
uniqueElements.Add(element)
End If
Next
Return JsonSerializer.Serialize(uniqueElements)
End Function
End Class
End Namespace
using System.Collections.Generic;
using System.Text.Json;
namespace DataStructure
{
public class Utils
{
public string RemoveDuplicates(string table)
{
JsonDocument doc = JsonDocument.Parse(table);
var seen = new HashSet<string>();
var uniqueElements = new List<JsonElement>();
foreach (JsonElement element in doc.RootElement.EnumerateArray())
{
// Serialize each element to a canonical string for comparison
string key = element.GetRawText();
if (seen.Add(key))
{
uniqueElements.Add(element);
}
}
return JsonSerializer.Serialize(uniqueElements);
}
}
}
Note: Since GetRawText() compares JSON text character
for character, two entries must be formatted exactly the same to be
detected as duplicates. Since GuiXT automatically serialises
V[sampletable] as a JSON array during the
CallVB call, the format is always consistent — making
duplicate detection reliable.