Victor Charles
asked on
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
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
you can use recordsets to do that
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr
set rsNew=currentdb.openrecord set("NewTa ble")
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
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr
set rsNew=currentdb.openrecord
set rs=currentdb.openrecordset
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
forgot the "next"
you can use recordsets to do that
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr
set rsNew=currentdb.openrecord set("NewTa ble")
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
you can use recordsets to do that
dim rs as dao.recordset, rsNew as dao.recordset, j, rowArr
set rsNew=currentdb.openrecord
set rs=currentdb.openrecordset
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
ASKER
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.OL EDB.4.0;Da ta Source=|DataDirectory|\AOP T2002org.m db;Persist Security Info=True;Jet OLEDB:Database Password=test"
Public objConnection As New OleDb.OleDbConnection(Conn ectionStri ng)
Public objDataSet As New DataSet
Form Load:
Dim objDataAdapter As New OleDb.OleDbDataAdapter("se lect * 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("se lect 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(objDat aSet, "Chapter")
Module:
Public ConnectionString As String = "Provider=Microsoft.Jet.OL
Public objConnection As New OleDb.OleDbConnection(Conn
Public objDataSet As New DataSet
Form Load:
Dim objDataAdapter As New OleDb.OleDbDataAdapter("se
Dim objDataAdapter1 As New OleDb.OleDbDataAdapter("se
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDat
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?
Do you want to store these in separate table? Do you want to avoid duplication?
ASKER
Yes, I would like to include them in a separate table without duplicates. Thx.
Is it the list of countries or something else?
ASKER
It's a different column, for example:
ROW1: M1, M2, M3
ROW2: MY, MU, MU
ROW3: VF, NH, YH
ROW1: M1, M2, M3
ROW2: MY, MU, MU
ROW3: VF, NH, YH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.OL EDB.4.0;Da ta Source=|DataDirectory|\aop t2002org.m db;Persist Security Info=True;Jet OLEDB:Database Password=testaopupdate"
Dim da As New OleDb.OleDbDataAdapter("Se lect ITEMA from AOP6B", ConnectionString)
Dim da2 As New OleDb.OleDbDataAdapter("Se lect 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
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.OL
Dim da As New OleDb.OleDbDataAdapter("Se
Dim da2 As New OleDb.OleDbDataAdapter("Se
' 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
ASKER
Thank You!
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.