Parsing A Comma Delimited Field

[AccessGeek]

Hi All,
I am working with a feed from Google Analytics and am trying to parse some data. Sample data is attached (in Excel, though I prefer to keep in Access). Important points:
1) I have a field ("dimension") with titles that are comma delimited that I need to parse into separate fields. There is also a pipe delimited field that could be used.
2) The number of titles that need to be parsed can vary from 1 to many. The sample data has country/city but other times it may be continent/country/city/zip or even other dimensions


3) Files can be fairly large…..10-50k records
4) This is for a tool that others will use and must process on the fly

I have tried using the getpart function that I have seen out there and it works, but this relies on knowing the number of fields you are parsing into ahead of time. I was thinking maybe I could write a function that counts the commas, then loops through the field string parsing into the desired number of fields. Is there a better way? Any ideas woulld be greatly appreciated! Thanks!

[pbaldy]

Haven't looked at the sample yet, but have you looked at the Split() function? It will take a delimited input and split it into an array. You can loop through the array and do whatever you need to with it.

[AccessGeek]

I never heard of the Split function! Will test it out and let you know how it goes.
Thanks for your quick help!

[AccessGeek]

Okay...I tried and am struggling a bit. It seems I can parse my field using the Split function, but from there I am not sure how to append each split field to a single record......when I run my code it appends each split field as a new record so their are multiple records. My codes below. Any ideas? Also, if there's a better way to do anything in my code, please let me know.....I know I may not follow all best practices.
Thanks!
~~~~~~~~~~~~~~~~~~~~
Public Function SplitFields()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant
Dim i As Integer
Dim tdfNew As TableDef
Dim dbs As Database
Dim NewFieldCount As Integer
Dim rstNewFieldCount As Recordset
Dim X As Integer
Dim NewField As Field
Set dbs = CurrentDb
Set tdfNew = dbs.CreateTableDef("GAResultsNew")
Set rsOrig = CurrentDb.OpenRecordset("GAResults")
'** This counts the number of fields I need to break the original field into
Set rstNewFieldCount = CurrentDb.OpenRecordset("SELECT First(CharCount([title],'|')+1) AS FldCnt FROM GAResults;")
NewFieldCount = rstNewFieldCount![FldCnt]
'** This creates the new table with the correct number of fields
With tdfNew
.Fields.Append .CreateField("Dimension", dbText)
For X = 1 To NewFieldCount
.Fields.Append .CreateField("Dimension" & X, dbText)
Next X
.Fields.Append .CreateField("MetricName", dbText)
.Fields.Append .CreateField("MetricValue", dbDouble)
End With
dbs.TableDefs.Append tdfNew
Set rsNew = CurrentDb.OpenRecordset("GAResultsNew")
If rsOrig.RecordCount <> 0 Then
rstNewFieldCount.Close
'** This is supposed to split the field and append to the new table along with data but goes haywire
rsOrig.MoveFirst
While Not rsOrig.EOF
vArr = Split(rsOrig("[dimension]"), ",")
For i = 0 To UBound(vArr)
With rsNew
.AddNew
For X = 1 To NewFieldCount
.Fields("Dimension" & X) = vArr(i)
Next X
.Fields("MetricName") = rsOrig("MetricName")
.Fields("MetricValue") = rsOrig("MetricValue")
.Fields("[dimension]") = Trim(vArr(i))
.Update
End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

MsgBox "Done"

End Function

[pbaldy]

Well, I wouldn't have fields like that (Whatever1, Whatever2, etc). It typically indicates a non-normalized db. In any case, you're looping the array outside the .AddNew, which is why you get all the records. My gut tells me you can replace the X loop with the array loop, and tweak this line

.Fields("Dimension" & i + 1) = vArr(i)

[AccessGeek]

Paul,
Thanks for your help....you were right, my loops were all out of wack and I didn't see it until you mentioned it and I stepped through. It's working now. You've helped me yet again!
Thanks!

[pbaldy]

Excellent, glad it worked for you! I took the liberty of marking the thread solved. I can undo that if we still have issues.