Solved

How to remove data separated by a "," and put them in a new table or column in the same table?

Posted on 2011-02-12
11
215 Views
Last Modified: 2012-05-11
Hello,
I have a table with the following data (Using ACCESS and VB.NET)
ROW1: M1, M2, M3
ROW2: MY, MU, MU
ROW3: VF, NH, YH
Is there a way to loop through each row, extract data separated by a "," and put them in another table or a column in that same table?
ROW1: M1
ROW2: M2
ROW3: M3
ROW4: MY
etc..
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
  • 3
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:s_chilkury
ID: 34878817
In VB.Net retrieve the records from access table and store in DataSet say OldDS.

Split the DataSet each row with ',' separation and iterate for each row and add in a new DataSet NewDS.

Then save this NewDS to new table in access.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34878839
you can use recordsets to do that
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr

set rsNew=currentdb.openrecordset("NewTable")

set rs=currentdb.openrecordset("Table")

rs.movefirst
do until rs.eof

 rowArr=split(rs!fieldName,",")
 for j=0 to ubound(rowarr)
      rsNew.addnew

      rsNew!fieldName=rowArr(j)

      rsNew.update
rs.movenext
loop

rs.close
rsNew.close
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34878850
forgot the "next"

you can use recordsets to do that
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr

set rsNew=currentdb.openrecordset("NewTable")

set rs=currentdb.openrecordset("Table")

rs.movefirst
do until rs.eof

 rowArr=split(rs!fieldName,",")
 for j=0 to ubound(rowarr)
      rsNew.addnew

      rsNew!fieldName=rowArr(j)

      rsNew.update
 next
rs.movenext
loop

rs.close
rsNew.close
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 

Author Comment

by:vcharles
ID: 34878912
Thanks for the code. What do I import in my project to use dao? I normally use system.data.oledb with thye followingf code to connect to my database.
Module:
Public ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOPT2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=test"
Public objConnection As New OleDb.OleDbConnection(ConnectionString)
Public objDataSet As New DataSet

Form Load:
 Dim objDataAdapter As New OleDb.OleDbDataAdapter("select * from chapter order by chpt", objConnection) 'where country in(SELECT COUNTRY FROM AOP5 WHERE SN like " & "'" & result & "'" & ")order by country", objConnection)
Dim objDataAdapter1 As New OleDb.OleDbDataAdapter("select Country_NAME as NATIONS, country from country ORDER BY country_name", objConnection) 'where country in(SELECT COUNTRY FROM AOP5 WHERE SN like " & "'" & result & "'" & ")order by country", objConnection)
Dim objDataSet As New DataSet
        'fill dataset
        objConnection.Open()
        objDataAdapter.Fill(objDataSet, "Chapter")
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34879821
You do not need to revert back to DAO to achieve this.

Do you want to store these in separate table? Do you want to avoid duplication?
0
 

Author Comment

by:vcharles
ID: 34879934
Yes, I would like to include them in a separate table without duplicates. Thx.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34879960
Is it the list of countries or something else?
0
 

Author Comment

by:vcharles
ID: 34880017
It's a different column, for example:
ROW1: M1, M2, M3
ROW2: MY, MU, MU
ROW3: VF, NH, YH
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 34884092
To do it with .NET code, try this

I am assuming that dTable is already populated with the comma separated values from DB.
Also, dResult is the datatable with 1 column which will store the separated values

Dim CSVs as String
For i As Integer = 0 to dTable.Rows.Count - 1
    CSVs = dTable.Rows(i).Item(0)
    For each value in CSVs.Split(",")
         Dim drow as DataRow = dResult.NewRow()
         drow(0)=value
         dResult.Rows.Add(drow)
    Next
Next


you can then save dResult to DB.
0
 

Author Comment

by:vcharles
ID: 34885306
Hi,

I'm not getting an error but the there's no data in the ITEMA column of AOP6C. Goal is to loop through AOP6B string values separated by a "," and poulate ITEMA of AOP6C. Any idea what is wrong with thye code?

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002org.mdb;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"
        Dim da As New OleDb.OleDbDataAdapter("Select ITEMA from AOP6B", ConnectionString)
        Dim da2 As New OleDb.OleDbDataAdapter("Select ITEMA from AOP6C", ConnectionString)

        ' objConnection.Open()
        Dim dTable As New DataTable
        Dim dResult As New DataTable
        Dim CSVs As String
        For i As Integer = 0 To dTable.Rows.Count - 1
            CSVs = dTable.Rows(i).Item(0)
            For Each value In CSVs.Split(",")
                Dim drow As DataRow = dResult.NewRow()
                drow(0) = value
                dResult.Rows.Add(drow)
            Next
        Next
        da2.Fill(dResult)
        da2.Update(dResult)

Thanks,

Victor
0
 

Author Closing Comment

by:vcharles
ID: 35157810
Thank You!
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

627 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