TCCC_KO
asked on
Parse data of varying lengths in MS Access
I need help in parsing data from one field into multiple fields. I've tried different mid/len/first/last statements and none have worked.
Example of the data in the field:
/irj/?NavigationTarget=ROL
/lherr/Browse/Link.msp
/tio/
/959955_ei/
The data can be any length -- one character or a hundred characters -- No change is needed for the fields that are less than 100 characters
The fields that have characters over 100 I need to parse them into separate fields so that I can join the fields for other analysis and MS Access
will not let you join fields over 255 characters which is the reason I need to break it up.
I'm tracking how many users click a link but I can't compare usage on any one link because of the inability to join the fields that are over 255 but if I could parse the data
I could get the other logic to work.
Example of the data in the field:
/irj/?NavigationTarget=ROL
/lherr/Browse/Link.msp
/tio/
/959955_ei/
The data can be any length -- one character or a hundred characters -- No change is needed for the fields that are less than 100 characters
The fields that have characters over 100 I need to parse them into separate fields so that I can join the fields for other analysis and MS Access
will not let you join fields over 255 characters which is the reason I need to break it up.
I'm tracking how many users click a link but I can't compare usage on any one link because of the inability to join the fields that are over 255 but if I could parse the data
I could get the other logic to work.
I think you need to provide a bit more information about how fields are delimited within the string.
It's not clear to me what your expected results should be with the supplied example input.
For anything complex like this, I would use access VB to break out the values in the string. You could then write the values into a table for use in queries etc.
It's not clear to me what your expected results should be with the supplied example input.
For anything complex like this, I would use access VB to break out the values in the string. You could then write the values into a table for use in queries etc.
ASKER
The fields are delimited with "/" and the character length can be 5 characters to 260 characters.
Field 1: (this is all in one field without any return characters)
/irj/?NavTarget=ROLES://po
EXPECTED OUTPUT: Field 1: irj/?NavTarget=ROLES://por
Field 2: /com.pi.roles/com.ko.ep.ep
Field 2:
/rety56//
EXPECTED OUTPUT: Field 1: /rety56//
Field 2: NULL
I want to be able to join two tables on Field 1 and Field 2 and pull in other data elements as needed but I have to be able to join them. right now I can't join b/c this field is imported into Access with a field type of 'Memo' and it has to be a field type of 'Text' so that I can create queries and join the field as needed. The fields that have more than 255 characters are preventing me from doing this so I determined if I parse the data into two (or more) fields, then I can successfully import as text and run queries as needed.
Field 1: (this is all in one field without any return characters)
/irj/?NavTarget=ROLES://po
EXPECTED OUTPUT: Field 1: irj/?NavTarget=ROLES://por
Field 2: /com.pi.roles/com.ko.ep.ep
Field 2:
/rety56//
EXPECTED OUTPUT: Field 1: /rety56//
Field 2: NULL
I want to be able to join two tables on Field 1 and Field 2 and pull in other data elements as needed but I have to be able to join them. right now I can't join b/c this field is imported into Access with a field type of 'Memo' and it has to be a field type of 'Text' so that I can create queries and join the field as needed. The fields that have more than 255 characters are preventing me from doing this so I determined if I parse the data into two (or more) fields, then I can successfully import as text and run queries as needed.
Well you could feed Field1 into the SPlit function and get yourself an array to work with. In broad outline that would be somthing like...
Dim varParts as Variant
varParts = Split(Field1,"\")
Then you can loop though the array and build your sub-string as you want
Const MAXLEN = 100
Dim n as Long
Dim strComponent As String
strComponent = ""
For n = UBound(varParts) to LBound(varParts)
IF Len(strComponent) + Len(varParts(n) < MAXLEN Then
strComponent = strComponent & varParts(n)
Else
' we hit the limit. Store strComponent in table and then
' start another component string
SaveData strComponent
strComponent = varParts(n)
End If
Next n
Dim varParts as Variant
varParts = Split(Field1,"\")
Then you can loop though the array and build your sub-string as you want
Const MAXLEN = 100
Dim n as Long
Dim strComponent As String
strComponent = ""
For n = UBound(varParts) to LBound(varParts)
IF Len(strComponent) + Len(varParts(n) < MAXLEN Then
strComponent = strComponent & varParts(n)
Else
' we hit the limit. Store strComponent in table and then
' start another component string
SaveData strComponent
strComponent = varParts(n)
End If
Next n
But your expected output for Field1 contains / - if you split your string whenever you find a / you will not get your output.
Are you sure that the format of your input will actually allow a parsing? i.e. is there a set of codable rules that can reliably be applied to your data?
Are you sure that the format of your input will actually allow a parsing? i.e. is there a set of codable rules that can reliably be applied to your data?
Using the split function is rarely good enough unless you KNOW that the incoming data abides by very simple rules. e,g, there are no user supplied strings that could contain your delimitor characters.
ASKER
Actually, maybe I will split the field wherever I find a '/' . It will give me a bunch of rows that I don't want but because of the inconsistency of the character length, maybe that's the only way.
ASKER
Actually, maybe I will split the field wherever I find a '/' . It will give me a bunch of columns that I don't want but because of the inconsistency of the character length, maybe that's the only way.
Where would I put the split code? in VB? I'm using MS Access 2007
Where would I put the split code? in VB? I'm using MS Access 2007
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER