Splitting contents of a field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Mornin' all,

I've asked this question in Excel, but I need to do it in Access too. In
case it's of interest, here's the link to the Excel one
http://communities2.microsoft.com/c...ming&mid=6d13d183-f184-499c-b64b-0abb11f004a5

I'd like to take a record like this (where the ' - ' bit
specifies a new field):

R1FQ33 - 20780PD,41930P
R7D41L - 52100S,84500SP,42230P,45210P

and turn it into this:

R1FQ33 - 20780 - PD
R1FQ33 - 41930 - P
R7D41L - 52100 - S
R7D41L - 84500 - SP
R7D41L - 42230 - P
R7D41L - 45210 - P

There can be any number of items in the second column but they're always
separated by a comma. Each item in the cell can be four or five numbers
followed by some letters.

Any help would be much appreciated.

Cheers,
Foss
 
Foss,

Assuming your input is in an two field table called SourceTable, cretae
a new table called TargetTable with three fields (text - long integer -
text, anmes are irrelevant), then paste this code in a general module:

Sub split_field()
Dim db As DAO.Database
Dim src As DAO.Recordset
Dim tgt As DAO.Recordset

Set db = CurrentDb
Set src = db.OpenRecordset("SourceTable")
Set tgt = db.OpenRecordset("TargetTable")
src.MoveFirst
Do Until src.EOF
f0 = src.Fields(0)
f1 = Split(src.Fields(1), ",")
For i = LBound(f1) To UBound(f1)
vStr = Trim(f1(i))
j = num_length(vStr)
vNum = Val(Left(vStr, j))
vText = Right(vStr, Len(vStr) - j)
tgt.AddNew
tgt.Fields(0) = f0
tgt.Fields(1) = vNum
tgt.Fields(2) = vText
tgt.Update
Next
src.MoveNext
Loop
src.Close
Set src = Nothing
tgt.Close
Set tgt = Nothing
Set db = Nothing
End Sub

Function num_length(vInp)
j = 1
Do Until IsNumeric(Mid(vInp, j, 1)) = False
j = j + 1
Loop
num_length = j - 1
End Function

Run the Sub and the new table will be populated as desired.

Note: To run this code, it is required to have an appropriate DAO Object
Library reference. While in the VB editor window, go to menu item Tools
References; check if a Microsoft DAO reference is present among the
Click to expand...
ones checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
If its GUARANEED that a comma will only be used to separate values and
not be contained in a value its self, you can use a function like this.
(Its a bit early so you'll probably have to tweak it a bit, I've written
so many things like this its not funny). If by some chance it works off
the bat, let me know how good I am. :)

David H
aka the String Master

The idea behind the code is to extract the record Id and save it. Then
loop through the remaining portion of the string and build the field
values. Since the field values are separated by a comma, when we get to
a comma, we know that we have a new record. As the for...next loop goes
through the field values, it adds the character being examined to a
variable that represents the field value. When the loop gets to a comma,
the code then builds the record by taking the existing newString, adding
the record Id, adding a '-' and adding the field value. It then adds a
line return to designate the end of the record. From there it keeps
going until the end of the record is found. You'll need to TCO the code
that loops through each line of the raw data and send each line to the
function. You'll also need code that takes the resulting values and
processes them.

function buildRecord(strRecordValue as String)
Dim recordId as string
Dim fieldValue as String
Dim tmpString
Dim newString As String
Dim tmpFieldValue

'Get the record value - statement assumes its the first 6
'characters
recordId = Mid(strRecordValue, 1, 6)
'Build a temp variable to work with - assumes the first
'value that we need starts on the 7th character
tmpString = Mid(strRecordValue, 7, len(strRecordValue))

newString = ""
'loop through each character in the string, looking for commas
for i = 1 to len(tmpString)
'if we don't have a comma, keep going
if(mid(tmpString, 1, i) <> "," then
tmpFieldValue = tmpFieldValue & mid(tmpString, 1, i)
else
'if we have a comma, put the record Id in front the field value and add
a return
newString = newString & recordId & "-" & tmpFieldValue &
Chr(10)'Double check the ASCII value
end if
next i

buildRecord = newString

end function

R1FQ33 - 20780PD,41930P should result in
R1FQ33 - 20780PD[chr(10)]R1FQ33 - 41930P[chr(10)]
 
Thanks Nikos, that's superb!

It's having a problem after doing a lot of the records though. It stops and
complains that there's been an 'Invalid use of Null'.
I've just looked through some more of the rows (100k+ rows!) and there's one
that looks a bit like this:

R7D41L - 52100S,84500SP,42230P,452

The last bit of the string doesn't have a letter as you can see.
Does your code handle this scenario or is this where the problem is occuring?

Thanks again for your help,
Pete
 
Pete,

That's funny, I used your input record and didn't get this problem. When
there's no text part the code assigns a zero-length string to vText,
not a null value; this did give me a problem at the beginning when the
field in the table was set to Allow Zero Length > No (default), but
changing that to Yes fixed it. Still, I don't know why you should get a
Null value. Can you run in debug and see where you get the Null value?

Nikos
 
Nikos,

To be honest, that was just a bit of a guess.

The line it's gone wrong on is:
f1 = Split(src.Fields(1), ",")

Any ideas?

Cheers,
Pete
 
Well, this helps a lot... it implies src.Fields(1) is null, i.e. there
is a record in your source table where the second field (the one to
split) is null. Is this acceptable?
If yes, the code needs to be modified to do what's desired (create a
record with null/zero lenght string second and third field in the target
table, or ignore record altogether.
If not acceptable, clean up your source table.

HTH,
Nikos
 
Nikos,

Yes, having spoken to the users, it seems that's ok to have an empty column
so I'll have a dig and try to make it work!

Thanks again for all your help.

Cheers,
Pete
 
Pete,

Change the sub to:

Sub split_field()
Dim db As DAO.Database
Dim src As DAO.Recordset
Dim tgt As DAO.Recordset

Set db = CurrentDb
Set src = db.OpenRecordset("SourceTable")
Set tgt = db.OpenRecordset("TargetTable")
src.MoveFirst
Do Until src.EOF
f0 = src.Fields(0)
If IsNull(src.Fields(1)) Or src.Fields(1) = "" Then
tgt.AddNew
tgt.Fields(0) = f0
tgt.Fields(1) = Null
tgt.Fields(2) = Null
tgt.Update
Else
f1 = Split(src.Fields(1), ",")
For i = LBound(f1) To UBound(f1)
vStr = Trim(f1(i))
j = num_length(vStr)
vNum = Val(Left(vStr, j))
vText = Right(vStr, Len(vStr) - j)
tgt.AddNew
tgt.Fields(0) = f0
tgt.Fields(1) = vNum
tgt.Fields(2) = vText
tgt.Update
Next
End If
src.MoveNext
Loop
src.Close
Set src = Nothing
tgt.Close
Set tgt = Nothing
Set db = Nothing
End Sub

Just make sure the Required property of fields 2 and 3 in the target
table is set to No, so they will accept the Nulls.

HTH,
Nikos
 
Back
Top