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

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
 
vcharlesAsked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
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
 
s_chilkuryCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
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
 
vcharlesAuthor Commented:
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
 
CodeCruiserCommented:
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
 
vcharlesAuthor Commented:
Yes, I would like to include them in a separate table without duplicates. Thx.
0
 
CodeCruiserCommented:
Is it the list of countries or something else?
0
 
vcharlesAuthor Commented:
It's a different column, for example:
ROW1: M1, M2, M3
ROW2: MY, MU, MU
ROW3: VF, NH, YH
0
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.