Victor Charles
asked on
How do you delete duplicate data in a string
Hello,
I have an ACCESS table with duplicate data in a string, for example:
ROW1: BEL,BEL,CAN,CZE,DEU
ROW2: CAN,CZE,DEU,USA,USA
ROW3:BEL,FRA,FRA,CZE,DEU
How do you loop through the table and delete the duplicate countries.
Thanks,
Victor
I have an ACCESS table with duplicate data in a string, for example:
ROW1: BEL,BEL,CAN,CZE,DEU
ROW2: CAN,CZE,DEU,USA,USA
ROW3:BEL,FRA,FRA,CZE,DEU
How do you loop through the table and delete the duplicate countries.
Thanks,
Victor
You will not be able to do it in SQL. You'll have to resort to VBA.
In VBA, get each row (or field) into a string, convert it to an array using the split function, sort the array (this will need to be written as a VBA function since there is no inbuilt sort), then select the unique values and do what you need to do with that ... either store them, or "join" them back into a string and put it back into the table, or print it, or whatever.
Ss
In VBA, get each row (or field) into a string, convert it to an array using the split function, sort the array (this will need to be written as a VBA function since there is no inbuilt sort), then select the unique values and do what you need to do with that ... either store them, or "join" them back into a string and put it back into the table, or print it, or whatever.
Ss
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the solution. Is there a way to sort the countries in the string? For example, in a row contains the following the data: BEL,USA,FRA,CAN.
Yes. Try this
Dim dTable As New DataTable
dTable.Columns.Add("Countr y", GetType(String))
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country")
If dTable.DefaultView.Count = 0 Then
Dim dRow as DataRow = dTable.NewRow()
drow(0) = drv2.item("Country")
dTable.Rows.Add(drow)
End If
dTable.DefaultView.RowFilt er = ""
Next
For i As Integer = 0 to dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & ","
Next
Dim dTable As New DataTable
dTable.Columns.Add("Countr
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt
If dTable.DefaultView.Count = 0 Then
Dim dRow as DataRow = dTable.NewRow()
drow(0) = drv2.item("Country")
dTable.Rows.Add(drow)
End If
dTable.DefaultView.RowFilt
Next
For i As Integer = 0 to dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & ","
Next
ASKER
Thanks for the code. I included it at the end of the existing code but I received the following error on the deign mode: "Variable SQLStr is used before it has been assigned a value" and when I run the project I received the folowing error: "The expression contains an invalid string constant: 'GRC." on line: dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country").
Dim ConnectionString As String = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=|DataDirectory|\aop t2002org.m db;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(Conn ectionStri ng)
objConnection.Open()
'Dim da As New OleDb.OleDbDataAdapter("Se lect NIIN, code3 from IDENTIFICATION_DATA", objConnection)
Dim da As New OleDb.OleDbDataAdapter("Se lect FIF, COUNTRYFUZEALLA from AOP5", objConnection)
Dim dt1 As New DataTable
da.Fill(dt1)
da.Dispose()
da = Nothing
'da = New OleDb.OleDbDataAdapter("Se lect NIIN, RN from REFERENCE_NUMBER_DATA", objConnection)
da = New OleDb.OleDbDataAdapter("Se lect FIF, COUNTRY from AOP5A", objConnection)
Dim dt2 As New DataTable
da.Fill(dt2)
Dim dv2 As DataView
dv2 = dt2.DefaultView
Dim cmd As OleDbCommand
Dim SQLStr As String
For Each dr As DataRow In dt1.Rows
'.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"
If dv2.Count > 0 Then
SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"
' For Each drv2 As DataRowView In dv2
'SQLStr &= drv2.Item("COUNTRY") & ","
'Next
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item( "COUNTRY") ) = False Then
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
'add closing apostrophe
SQLStr &= "'"
'add WHERE clause
SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
' MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)
'MsgBox(SQLStr)
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
End If
Next
Dim dTable As New DataTable
dTable.Columns.Add("Countr y", GetType(String))
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country") **Error Line
If dTable.DefaultView.Count = 0 Then
Dim dRow As DataRow = dTable.NewRow()
dRow(0) = drv2.Item("Country")
dTable.Rows.Add(dRow)
End If
dTable.DefaultView.RowFilt er = ""
Next
For i As Integer = 0 To dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & "," ****Error line
Next
MsgBox("COMPLETED")
Dim ConnectionString As String = "Provider=Microsoft.Jet.OL
Dim objConnection As New OleDb.OleDbConnection(Conn
objConnection.Open()
'Dim da As New OleDb.OleDbDataAdapter("Se
Dim da As New OleDb.OleDbDataAdapter("Se
Dim dt1 As New DataTable
da.Fill(dt1)
da.Dispose()
da = Nothing
'da = New OleDb.OleDbDataAdapter("Se
da = New OleDb.OleDbDataAdapter("Se
Dim dt2 As New DataTable
da.Fill(dt2)
Dim dv2 As DataView
dv2 = dt2.DefaultView
Dim cmd As OleDbCommand
Dim SQLStr As String
For Each dr As DataRow In dt1.Rows
'.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"
If dv2.Count > 0 Then
SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"
' For Each drv2 As DataRowView In dv2
'SQLStr &= drv2.Item("COUNTRY") & ","
'Next
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item(
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
'add closing apostrophe
SQLStr &= "'"
'add WHERE clause
SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
' MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)
'MsgBox(SQLStr)
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
End If
Next
Dim dTable As New DataTable
dTable.Columns.Add("Countr
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt
If dTable.DefaultView.Count = 0 Then
Dim dRow As DataRow = dTable.NewRow()
dRow(0) = drv2.Item("Country")
dTable.Rows.Add(dRow)
End If
dTable.DefaultView.RowFilt
Next
For i As Integer = 0 To dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & "," ****Error line
Next
MsgBox("COMPLETED")
Change this
dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country").
to
dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country") & "'"
Also, the code I provide needs to replace this code
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item( "COUNTRY") ) = False Then
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
dTable.DefaultView.RowFilt
to
dTable.DefaultView.RowFilt
Also, the code I provide needs to replace this code
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item(
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
ASKER
I replaced the code as shown below, but it still doesn't sort to countries.
For Each dr As DataRow In dt1.Rows
'.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"
If dv2.Count > 0 Then
SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"
' For Each drv2 As DataRowView In dv2
'SQLStr &= drv2.Item("COUNTRY") & ","
'Next
'For Each drv2 As DataRowView In dv2
'If SQLStr.Contains(drv2.Item( "COUNTRY") ) = False Then
'SQLStr &= drv2.Item("COUNTRY") & ","
'End If
'Next
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item( "COUNTRY") ) = False Then
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
'add closing apostrophe
SQLStr &= "'"
'add WHERE clause
SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
' MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)
'MsgBox(SQLStr)
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
End If
Next
Dim dTable As New DataTable
dTable.Columns.Add("Countr y", GetType(String))
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt er = "Country='" & drv2.Item("Country") & "'"
If dTable.DefaultView.Count = 0 Then
Dim dRow As DataRow = dTable.NewRow()
dRow(0) = drv2.Item("Country")
dTable.Rows.Add(dRow)
End If
dTable.DefaultView.RowFilt er = ""
Next
For i As Integer = 0 To dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & "," '****Error line
Next
Thanks.
For Each dr As DataRow In dt1.Rows
'.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
dv2.RowFilter = "FIF='" & dr.Item("FIF") & "'"
If dv2.Count > 0 Then
SQLStr = "Update AOP5 Set COUNTRYFUZEALLA='"
' For Each drv2 As DataRowView In dv2
'SQLStr &= drv2.Item("COUNTRY") & ","
'Next
'For Each drv2 As DataRowView In dv2
'If SQLStr.Contains(drv2.Item(
'SQLStr &= drv2.Item("COUNTRY") & ","
'End If
'Next
For Each drv2 As DataRowView In dv2
If SQLStr.Contains(drv2.Item(
SQLStr &= drv2.Item("COUNTRY") & ","
End If
Next
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
'add closing apostrophe
SQLStr &= "'"
'add WHERE clause
SQLStr &= " Where FIF='" & dr.Item("FIF") & "'"
' MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)
'MsgBox(SQLStr)
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
End If
Next
Dim dTable As New DataTable
dTable.Columns.Add("Countr
For Each drv2 As DataRowView In dv2
dTable.DefaultView.RowFilt
If dTable.DefaultView.Count = 0 Then
Dim dRow As DataRow = dTable.NewRow()
dRow(0) = drv2.Item("Country")
dTable.Rows.Add(dRow)
End If
dTable.DefaultView.RowFilt
Next
For i As Integer = 0 To dTable.Rows.Count - 1
SQLStr &= dTable.Rows(i).Item(0) & "," '****Error line
Next
Thanks.
ASKER
Thank You.
ASKER
Below is the code I'm using to create the string of countries, is there a way to modify it to avoid duplicate countries? The problem is NSNs are sometimes duplicated which causes multiple countries to be included in the string.
Thanks.
Victor
Dim ConnectionString As String = "Provider=Microsoft.Jet.OL
Dim objConnection As New OleDb.OleDbConnection(Conn
objConnection.Open()
'Dim da As New OleDb.OleDbDataAdapter("Se
Dim da As New OleDb.OleDbDataAdapter("Se
Dim dt1 As New DataTable
da.Fill(dt1)
da.Dispose()
da = Nothing
'da = New OleDb.OleDbDataAdapter("Se
da = New OleDb.OleDbDataAdapter("Se
Dim dt2 As New DataTable
da.Fill(dt2)
Dim dv2 As DataView
dv2 = dt2.DefaultView
Dim cmd As OleDbCommand
Dim SQLStr As String
For Each dr As DataRow In dt1.Rows
'.RowFilter = "NIIN='" & dr.Item("NIIN") & "'"
dv2.RowFilter = "NSN='" & dr.Item("NSN") & "'"
If dv2.Count > 0 Then
SQLStr = "Update AOP5 Set COUNTRYALL='"
For Each drv2 As DataRowView In dv2
SQLStr &= drv2.Item("COUNTRY") & ","
Next
'strip off trailing comma
SQLStr = SQLStr.Substring(0, SQLStr.Length - 1)
'add closing apostrophe
SQLStr &= "'"
'add WHERE clause
SQLStr &= " Where NSN='" & dr.Item("NSN") & "'"
' MsgBox(SQLStr)
cmd = New OleDbCommand(SQLStr, objConnection)
'MsgBox(SQLStr)
cmd.ExecuteNonQuery()
cmd.Dispose()
cmd = Nothing
End If
Next