Parsing string values in Access

Question

I am importing sql into my Access database and am working on parsing the data into the correct tables and fields. Again I turn to you gurus to assist in a problem

One of the fields that is imported has comma separated values that need to be separated.there are anywhere from one to 10 possible values in the string.

 PHO,Rosgen,NRCS,EMAP,T-DL,YSI-DL

I have figured out that if I make all of the values the same length (say 4 characters) I can get the 1st, last and 1st after the comma to parse but cannot seem to get the middle values extracted correctly.

SELECT Left([FieldForms],InStr([FieldForms],",")-1) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Mid([FieldForms],InStr([FieldForms],",")+1,4) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Mid([FieldForms], 11, 4) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Mid([FieldForms], 16, 4) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Mid([FieldForms], 21, 4) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Mid([FieldForms],InStrRev([FieldForms],",")-4,4) AS DEQ_SampleTypeID
FROM tblSiteVisit
UNION ALL
SELECT Right([FieldForms],InStr([FieldForms],",")-1) AS DEQ_SampleTypeID
FROM tblSiteVisit

If I use the InStrRev or the Right Function I get repeats if there are fewer than the maximum also using the Mid functions results in empty rows.

Is there a way to parse out a string like this and only get results from the string

This would be easier IMO with an Access VBA procedure instead of a query. Would that be a suitable option for you? — HansUp
– HansUp, Commented Nov 27, 2013 at 18:53
Split(FieldForms, ",") will give you a string array. Add a row to your destination table for each item in the array. — HansUp
– HansUp, Commented Nov 27, 2013 at 19:16

Johnny Bones · Accepted Answer · 2013-11-27 19:18:29Z

2

Put the following functions into a Module:

   Function CountCSWords (ByVal S) As Integer
  ' Counts the words in a string that are separated by commas.

  Dim WC As Integer, Pos As Integer
     If VarType(S) <> 8 Or Len(S) = 0 Then
       CountCSWords = 0
       Exit Function
     End If
     WC = 1
     Pos = InStr(S, ",")
     Do While Pos > 0
       WC = WC + 1
       Pos = InStr(Pos + 1, S, ",")
     Loop
     CountCSWords = WC
  End Function

  Function GetCSWord (ByVal S, Indx As Integer)
  ' Returns the nth word in a specific field.

  Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
     WC = CountCSWords(S)
     If Indx < 1 Or Indx > WC Then
       GetCSWord = Null
       Exit Function
     End If
     Count = 1
     SPos = 1
     For Count = 2 To Indx
       SPos = InStr(SPos, S, ",") + 1
     Next Count
     EPos = InStr(SPos, S, ",") - 1
     If EPos <= 0 Then EPos = Len(S)
     GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))
  End Function

Then, put a field in your query like this:

MyFirstField: GetCSWord([FieldForms],1)

Put another one in like this:

MySecondField: GetCSWord([FieldForms],2)

Etc... for as many as you need.

answered Nov 27, 2013 at 19:18

Johnny Bones

8,5027 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gord Thompson Over a year ago

It seems to me that using the Split() function could simplify this code quite a bit.

Johnny Bones Over a year ago

Couldn't figure out how to do that in a query. Split() would create an array. If you can figure it out, post it!

pja Over a year ago

Not the worlds best at VBA...I created the module and the query but it isn't working, I seem to have messed up somewhere. I get an error about an invalid parentheses. Can you give me the query in sql?

Johnny Bones Over a year ago

Try this: SELECT GetCSWord([FieldForms],1) as MyFirstField, GetCSWord([FieldForms],2) as MySecondField FROM tblSiteVisit. That is, assuming the name of the field with the comma separated values is "FieldForms". If not, change the SQL code accordingly. That should give you a query with the first 2 values in your field. If ti works, just repeat accordingly. If it doesn't, then let me know what happened.

HansUp · Accepted Answer · 2015-06-01 03:04:41Z

1

This VBA code reads text values from FieldForms in tblSiteVisit, splits that text into substrings, and then stores each of the substrings in DEQ_SampleTypeID in a new row added to tblDestination.

Dim astrItems() As String
Dim db As DAO.database
Dim i As Long
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strInsert As String

strInsert = "INSERT INTO tblDestination (DEQ_SampleTypeID)" & vbCrLf & _
    "VALUES ([array_item]);"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblSiteVisit", dbOpenTable, dbOpenSnapshot)
Set qdf = db.CreateQueryDef(vbNullString, strInsert)
Do While Not rs.EOF
    astrItems = Split(rs!FieldForms, ",")
    For i = 0 To UBound(astrItems)
        qdf.Parameters("array_item") = astrItems(i)
        qdf.Execute dbFailOnError
    Next
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

edited Jun 1, 2015 at 3:04

answered Nov 27, 2013 at 20:18

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

1 Comment

Johnny Bones Over a year ago

I'm 99% sure (always leave that last 1%...) that this will just insert a new record into a table for each comma separated value. Even though the OP posted a UNION ALL SQL string, his text states he's looking for a way to get each CSV broken out, which tells me he wants them separated into individual fields. Which makes much more sense than creating new records for each value.

Collectives™ on Stack Overflow

Parsing string values in Access

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related