Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with deleting records with two duplicate columns using VB.NET

Posted on 2016-11-25
12
Medium Priority
?
53 Views
Last Modified: 2016-11-27
Hi,

How do you delete duplicate records if SN and AGD have same information using VB.NET? For example if the following file contains:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <AOP6A>
    <SN>10411</SN>
    <AGD>CAR</AGD>
    <CHPT>104</CHPT>
    <RIC>DEALER</RIC>
  </AOP6A>
 <AOP6A>
  <SN>10411</SN>
    <AGD>CAR</AGD>
    <CHPT>104</CHPT>
    <RIC>DEALER</RIC>
  </AOP6A>
<AOP6A>
<SN>10412</SN>
    <AGD>CAR</AGD>
    <CHPT>104</CHPT>
    <RIC>DEALER</RIC>
  </AOP6A>
</NewDataSet>

I would like to have the following file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <AOP6A>
    <SN>10411</SN>
    <AGD>CAR</AGD>
    <CHPT>104</CHPT>
    <RIC>DEALER</RIC>
  </AOP6A>
<AOP6A>
<SN>10412</SN>
    <AGD>CAR</AGD>
    <CHPT>104</CHPT>
    <RIC>DEALER</RIC>
  </AOP6A>
</NewDataSet>

Thanks,

Victor
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41901405
DataSet ds
ds = new DataSet()
ds.ReadXml(xml file path)
use linq to filter duplicate rows..
0
 

Author Comment

by:vcharles
ID: 41901422
Can you pleaze send me the source code.
Victor
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41901442
Try...

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim a = New DataSet()
        a.ReadXml("E:\pawan.xml")

        Dim b = a.Tables(0).AsEnumerable().[Select](Function(d) New With { _
         Key .SN = d.Field(Of String)("SN"), _
         Key .AGD = d.Field(Of String)("AGD"), _
         Key .CHPT = d.Field(Of String)("CHPT"), _
         Key .RIC = d.Field(Of String)("RIC") _
        }).Distinct().ToList()

        Console.WriteLine()

    End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:vcharles
ID: 41901459
Hi,

To avoid writing the fields names, is there a way to modify the code for identical records instead of the two fields?

Thanks,

Victor
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41901540
Ok.. try

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim a = New DataSet()
        a.ReadXml("E:\pawan.xml")

        'Dim b = a.Tables(0).AsEnumerable().[Select](Function(d) New With { _
        ' Key .SN = d.Field(Of String)("SN"), _
        ' Key .AGD = d.Field(Of String)("AGD"), _
        ' Key .CHPT = d.Field(Of String)("CHPT"), _
        ' Key .RIC = d.Field(Of String)("RIC") _
        '}).Distinct().ToList()

        Dim k As DataTable
        k = a.Tables(0)
        Dim result = k.AsEnumerable().Distinct(System.Data.DataRowComparer.[Default]).ToList()


        Console.WriteLine()

    End Sub

Open in new window

0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 41901796
Hi Victor;

The below code snippet should do what you need without using tag names.
Dim xdoc = XDocument.Load("C:\Working Directory\Victor.xml")

Dim results = (From node In xdoc.Root.Elements()
               Group node By node.Name.LocalName Into nodeGroup = Group
               From ng In nodeGroup
               Let nodeStr = ng.ToString().Replace(vbNewLine, "").Replace(" ", "")
               Select ng, nodeStr).GroupBy(Function(k) k.nodeStr).Select(Function(n) n.First().ng)

xdoc.Root.ReplaceAll(results)
xdoc.Save("C:\Working Directory\VictorUpdated.xml")

Open in new window

0
 

Author Comment

by:vcharles
ID: 41902233
Hi Fernando,

Thank you for the solution.

Hi Pawan,

Using your approach, how do you save the results to an xml file?

Thanks,

Victor
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 41902304
Hi Victor;

The solution by Pawan does not meet the requirements as posted by not using Tag names such as SN, AGD, CHPT and  RIC.
0
 

Author Closing Comment

by:vcharles
ID: 41902418
Thank You.
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 41902428
Not a problem Victor, glad to help.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41902734
@Fernando Soto-
Have you seen my first solution? See below-  , I had used the tags, But later the Author told me not to use them.

Dim b = a.Tables(0).AsEnumerable().[Select](Function(d) New With { _
        Key .SN = d.Field(Of String)("SN"), _
         Key .AGD = d.Field(Of String)("AGD"), _
         Key .CHPT = d.Field(Of String)("CHPT"), _
         Key .RIC = d.Field(Of String)("RIC") _

        }).Distinct().ToList()
0
 

Author Comment

by:vcharles
ID: 41903101
Hi Pawan,

I wanted to compared two fields without including all the fields in my code.

Thanks,

Victor
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question