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
210 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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