parse data seperated by comas

B

BLTibbs

I have a table in access that has imported 4 fields. Each field has data
seperated by comas. Each chunk of data (between the comas) is information
that is a unique vendor value. If field 1 has data for 6 vendors then fields
2-4 will also have data for 6 vendors and each chunk of data between the
comas correlates to the data in the same position in the other 3 fields
(confused yet) - here is an example...

field 1 = [sellerID]
contents are:
A18OZMH8UQINVM , A2B7KD8W65ON41 , A1AIVX1XT5B5H7 , AQWX9I76NVSJD ,
A353CQKEQ1MM9N , A2LHZW69NOBUX3

field 2 = [feedbackrating]
contents are:
4.7 , 4.7 , 4.7 , 4.9 , 4.7 , 4.8

field 3 = [totalfeedback]
contents are:
39593 , 2227 , 5252 , 100 , 124 , 43

field 3 = [price]
contents are:
$0.89 , $1.00 , $1.45 , $7.99 , $16.55 , $27.99

what this means is that the first chunk of data in field 1 (A18OZMH8UQINVM )
has a feedbackrating of (4.7 - the first one) and the last chunk of data in
field 1 (A2LHZW69NOBUX3) has a feedback rating of (4.8), etc.

What I need to do is create somehow to rebuild this file so that I have a
record for the each chunk of data in each field. like this:
sellerID, feedbackrating, totalfeedback, price
A18OZMH8UQINVM, 4.7, 39593, $0.89
A2B7KD8W65ON41, 4.7, 2227, $1.00

Is there some way to do this with code or a macro? Please help! Thanks in
advance.
 
T

tina

after some fiddling, i came up with a public procedure that works. it takes
the values from the fields in the "imported" table, and creates separate
records in a new table. i named the table with the imported values
"tblImport", and the new table "tblImportResult"; in both tables, i used the
four field names that you posted, and tested my code with the strings that
you posted. note that the code does NOT create tblImportResult, you have to
do that before you run the code. also note that in tblImportResult, the
first three fields are Text data type, and the Price field is Currency data
type. here's the procedure:

Public Sub isSplitValues()

Dim rst As DAO.Recordset, strSQL As String, i As Integer
Dim aseller As Variant
Dim arating As Variant
Dim afeedback As Variant
Dim aprice As Variant

Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Do
aseller = Split(rst("sellerID"), ",")
arating = Split(rst("feedbackrating"), ",")
afeedback = Split(rst("totalfeedback"), ",")
aprice = Split(rst("price"), ",")
For i = 0 To UBound(aseller)
CurrentDb.Execute "INSERT INTO tblImportResult ( " _
& "sellerID, feedbackrating, totalfeedback, price ) " _
& "SELECT '" & Trim(aseller(i)) & "', '" _
& Trim(arating(i)) & "', '" & Trim(afeedback(i)) _
& "', " & CCur(Trim(aprice(i))), dbFailOnError
Next i
rst.MoveNext
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

MsgBox "done"

End Sub

hth


BLTibbs said:
I have a table in access that has imported 4 fields. Each field has data
seperated by comas. Each chunk of data (between the comas) is information
that is a unique vendor value. If field 1 has data for 6 vendors then fields
2-4 will also have data for 6 vendors and each chunk of data between the
comas correlates to the data in the same position in the other 3 fields
(confused yet) - here is an example...

field 1 = [sellerID]
contents are:
A18OZMH8UQINVM , A2B7KD8W65ON41 , A1AIVX1XT5B5H7 , AQWX9I76NVSJD ,
A353CQKEQ1MM9N , A2LHZW69NOBUX3

field 2 = [feedbackrating]
contents are:
4.7 , 4.7 , 4.7 , 4.9 , 4.7 , 4.8

field 3 = [totalfeedback]
contents are:
39593 , 2227 , 5252 , 100 , 124 , 43

field 3 = [price]
contents are:
$0.89 , $1.00 , $1.45 , $7.99 , $16.55 , $27.99

what this means is that the first chunk of data in field 1 (A18OZMH8UQINVM )
has a feedbackrating of (4.7 - the first one) and the last chunk of data in
field 1 (A2LHZW69NOBUX3) has a feedback rating of (4.8), etc.

What I need to do is create somehow to rebuild this file so that I have a
record for the each chunk of data in each field. like this:
sellerID, feedbackrating, totalfeedback, price
A18OZMH8UQINVM, 4.7, 39593, $0.89
A2B7KD8W65ON41, 4.7, 2227, $1.00

Is there some way to do this with code or a macro? Please help! Thanks in
advance.
Click to expand...
 
B

BLTibbs

Tina - I think what you have here is what I need. However, I am still
learning on VBA and am not sure how to run this procedure... where do I put a
public sub and were and when does it actually do the parsing? Is it event
driven?

tina said:
after some fiddling, i came up with a public procedure that works. it takes
the values from the fields in the "imported" table, and creates separate
records in a new table. i named the table with the imported values
"tblImport", and the new table "tblImportResult"; in both tables, i used the
four field names that you posted, and tested my code with the strings that
you posted. note that the code does NOT create tblImportResult, you have to
do that before you run the code. also note that in tblImportResult, the
first three fields are Text data type, and the Price field is Currency data
type. here's the procedure:

Public Sub isSplitValues()

Dim rst As DAO.Recordset, strSQL As String, i As Integer
Dim aseller As Variant
Dim arating As Variant
Dim afeedback As Variant
Dim aprice As Variant

Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Do
aseller = Split(rst("sellerID"), ",")
arating = Split(rst("feedbackrating"), ",")
afeedback = Split(rst("totalfeedback"), ",")
aprice = Split(rst("price"), ",")
For i = 0 To UBound(aseller)
CurrentDb.Execute "INSERT INTO tblImportResult ( " _
& "sellerID, feedbackrating, totalfeedback, price ) " _
& "SELECT '" & Trim(aseller(i)) & "', '" _
& Trim(arating(i)) & "', '" & Trim(afeedback(i)) _
& "', " & CCur(Trim(aprice(i))), dbFailOnError
Next i
rst.MoveNext
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

MsgBox "done"

End Sub

hth


BLTibbs said:
I have a table in access that has imported 4 fields. Each field has data
seperated by comas. Each chunk of data (between the comas) is information
that is a unique vendor value. If field 1 has data for 6 vendors then fields
2-4 will also have data for 6 vendors and each chunk of data between the
comas correlates to the data in the same position in the other 3 fields
(confused yet) - here is an example...

field 1 = [sellerID]
contents are:
A18OZMH8UQINVM , A2B7KD8W65ON41 , A1AIVX1XT5B5H7 , AQWX9I76NVSJD ,
A353CQKEQ1MM9N , A2LHZW69NOBUX3

field 2 = [feedbackrating]
contents are:
4.7 , 4.7 , 4.7 , 4.9 , 4.7 , 4.8

field 3 = [totalfeedback]
contents are:
39593 , 2227 , 5252 , 100 , 124 , 43

field 3 = [price]
contents are:
$0.89 , $1.00 , $1.45 , $7.99 , $16.55 , $27.99

what this means is that the first chunk of data in field 1 (A18OZMH8UQINVM )
has a feedbackrating of (4.7 - the first one) and the last chunk of data in
field 1 (A2LHZW69NOBUX3) has a feedback rating of (4.8), etc.

What I need to do is create somehow to rebuild this file so that I have a
record for the each chunk of data in each field. like this:
sellerID, feedbackrating, totalfeedback, price
A18OZMH8UQINVM, 4.7, 39593, $0.89
A2B7KD8W65ON41, 4.7, 2227, $1.00

Is there some way to do this with code or a macro? Please help! Thanks in
advance.
Click to expand...
Click to expand...
 
T

tina

where do I put a
public sub and were and when does it actually do the parsing? Is it event
driven?
Click to expand...

a public sub is usually saved into a standard module. it can be called from
an event procedure in a form module by simply putting the name of the sub
inside the event procedure. for example, to run it from the Click event of a
command button named Command0:

Private Sub Command0_Click()

isSplitValues

End Sub

or you can run it directly from the standard module by opening the module,
clicking anywhere in the procedure, and pressing F5.

or you could paste the *body* of the code (minus the "Public Sub..." and
"End Sub" lines) into an event procedure in a form module, *instead of*
putting the whole procedure into a standard module.

hth


BLTibbs said:
Tina - I think what you have here is what I need. However, I am still
learning on VBA and am not sure how to run this procedure... where do I put a
public sub and were and when does it actually do the parsing? Is it event
driven?

tina said:
after some fiddling, i came up with a public procedure that works. it takes
the values from the fields in the "imported" table, and creates separate
records in a new table. i named the table with the imported values
"tblImport", and the new table "tblImportResult"; in both tables, i used the
four field names that you posted, and tested my code with the strings that
you posted. note that the code does NOT create tblImportResult, you have to
do that before you run the code. also note that in tblImportResult, the
first three fields are Text data type, and the Price field is Currency data
type. here's the procedure:

Public Sub isSplitValues()

Dim rst As DAO.Recordset, strSQL As String, i As Integer
Dim aseller As Variant
Dim arating As Variant
Dim afeedback As Variant
Dim aprice As Variant

Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Do
aseller = Split(rst("sellerID"), ",")
arating = Split(rst("feedbackrating"), ",")
afeedback = Split(rst("totalfeedback"), ",")
aprice = Split(rst("price"), ",")
For i = 0 To UBound(aseller)
CurrentDb.Execute "INSERT INTO tblImportResult ( " _
& "sellerID, feedbackrating, totalfeedback, price ) " _
& "SELECT '" & Trim(aseller(i)) & "', '" _
& Trim(arating(i)) & "', '" & Trim(afeedback(i)) _
& "', " & CCur(Trim(aprice(i))), dbFailOnError
Next i
rst.MoveNext
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

MsgBox "done"

End Sub

hth


BLTibbs said:
I have a table in access that has imported 4 fields. Each field has data
seperated by comas. Each chunk of data (between the comas) is information
that is a unique vendor value. If field 1 has data for 6 vendors then fields
2-4 will also have data for 6 vendors and each chunk of data between the
comas correlates to the data in the same position in the other 3 fields
(confused yet) - here is an example...

field 1 = [sellerID]
contents are:
A18OZMH8UQINVM , A2B7KD8W65ON41 , A1AIVX1XT5B5H7 , AQWX9I76NVSJD ,
A353CQKEQ1MM9N , A2LHZW69NOBUX3

field 2 = [feedbackrating]
contents are:
4.7 , 4.7 , 4.7 , 4.9 , 4.7 , 4.8

field 3 = [totalfeedback]
contents are:
39593 , 2227 , 5252 , 100 , 124 , 43

field 3 = [price]
contents are:
$0.89 , $1.00 , $1.45 , $7.99 , $16.55 , $27.99

what this means is that the first chunk of data in field 1 (A18OZMH8UQINVM )
has a feedbackrating of (4.7 - the first one) and the last chunk of
Click to expand...
data
in
field 1 (A2LHZW69NOBUX3) has a feedback rating of (4.8), etc.

What I need to do is create somehow to rebuild this file so that I have a
record for the each chunk of data in each field. like this:
sellerID, feedbackrating, totalfeedback, price
A18OZMH8UQINVM, 4.7, 39593, $0.89
A2B7KD8W65ON41, 4.7, 2227, $1.00

Is there some way to do this with code or a macro? Please help!
Click to expand...
Thanks
in
advance.
Click to expand...
Click to expand...
Click to expand...
 
Top