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
214 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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