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
208 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now