Solved

splitting string field into fields access query OR vba

Posted on 2015-01-01
22
502 Views
Last Modified: 2015-01-22
Hi,
I have a field with this string:
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER


how can i split this string into new field in such a query by this rule:
bring me all the string after ":20:" Until the next ":", etc
result :
field_:20: =111111251540002D
field_:23B:=CREASDD
field_:32A:=09A1237CHF41912,05
ETC.....
0
Comment
Question by:drtopserv
  • 5
  • 5
  • 3
  • +6
22 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40527252
Select MyField From Table1 Where Left(MyField, 1)=":"
0
 

Author Comment

by:drtopserv
ID: 40527261
Sorry,seems not understand my Q.
What i`m trying to tell, that this exact long string in the field needed to be devided to to many strings and put them in other fields (mentioned in the result . look at the result that i need when executing the solution.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40527316
Please provide some more examples of the field content and the table structure that you want to split it into, as it is not completely clear from your example and we don't want to waste time providing a solution that you don't want.

e.g.
Do you want to split the field at every newline (apparently not)
Do you want to split the field at every [newline followed by colon then alphanumeric string then another colon] (yes?)
please clarify: do all examples of this data have the same "field identifiers" (20,23B,32A,33B,50K...)
should the 50k field include everything up to 53A:
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 40527352
I don't know how something this complex could be done in SQL, but it seems a pretty strait-forward solution to simply do some VB code in a loop with the InStr() function and the optional 'start' parameter.  Something like this:
Str = YourBigString
'Find the Start of the 1st Field
P1 = InStr( Str, ":")
Do While P1 > 0
  'Find End of the Next Field
  P2 = InStr(P1+1, Str, ":")
  If P2 = 0 Then
    Exit Do  'String not formated as expected
  End If
  FieldName = "Field_" & Mid$( Str, P1, P2 - P1 + 1 )
  'Find Data
  P1 = P2 + 1
  P2 = InStr( P1, Str, ":" )
  If P2 > 0 Then
    DataStr = Mid$( Str, P1, P2 - P1 ) 'No +1 because the : found starts the next field name
  Else
    DataStr = Mid$( Str, P1 )  'Extracts to end of string
  End If
  'P2 is now the start of the next field name
  P1 = P2 'If no final : found, the while loop will end
  Fields(FieldName).Value = DataStr
Loop

Open in new window

0
 
LVL 10

Assisted Solution

by:Jamie Garroch
Jamie Garroch earned 125 total points
ID: 40527364
You ciuld use the Split function in VBA to load each line of the string into an array and then address that array in the way you want:

Function SplitString (myString As String) As Single
  Dim myArray() as String
  myArray = Split (myString, vbCrLf)
  SplitString = Ubound(myArray)
End Function

Open in new window


The vbCrLf delimiter would need checking as I can't see what character is used in your example.
0
 
LVL 57
ID: 40527671
Slight tweak to what Jamie suggested to give you what you want:

 Function SplitString (myString As String,intToken as Integer) As String
 
  Dim myArray() as Variant

  myArray = Split (myString,":")
  SplitString = myArray(intToken)

End Function

  Your call would be the string/field with the entire date, and the nth token you want returned from it.

Jim.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40527739
you can simply use a query, f1 is the field that contains the string

select f1, left(f1,instrrev(f1,":")) as result1, mid(f1,instrrev(f1,":")+1) as result2
from yourTable
where instr(f1,":") >0

with this info
:20:111111251540002D

the result will be

f1                                           result1    result2
:20:111111251540002D    :20:          111111251540002D



i already posted this yesterday and the thread disappeared????
0
 

Author Comment

by:drtopserv
ID: 40527900
kewl Rey,
but i need to keep going and extract others  :Anystring:
means:
f1                                                          result1    result2
:20:111111251540002D                    :20:          111111251540002D
f1                                                          result1    result2
:23B:CREASDD                                   :23B:        CREASDD
f1                                                         result1    result2                    
:50K:/CH110023412400243526     :50K:        /CH110023412400243526    
MSFSDFER KFSFUTDFR                                    MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2                                               BUDFSFERSTR. 2
8113 BOSDFSFSLSEN                                       8113 BOSDFSFSLSEN

Etc.....
0
 

Author Comment

by:drtopserv
ID: 40527915
to clarify this :
the field i mention in my question has the long string :
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER

that i would like to split into many new fields,and the delimiter should me ->  :numeronly OR numer&Char:
as you can see : :20:  ,  :23B:  , :32A:   ,  :33B:   ,   :50K:   ,  :53A:  ,  :57D:
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 125 total points
ID: 40527921
The following function parses your strings with a regexp object.
Public Function Q_28589320(parmString) As Object
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim lngSM As Long
    Dim oDic As Object
    
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "(:[^:]*:)([^:]*)"
    End If
    
    Set oDic = CreateObject("scripting.dictionary")
    
    If IsNull(parmString) Then
        Set Q_28589320 = oDic
        Exit Function
    End If
    
    If oRE.test(parmString) Then
        Set oMatches = oRE.Execute(parmString)
        For Each oM In oMatches
            With oM
                oDic(.submatches(0)) = .submatches(1)
            End With
        Next
    End If
    Set Q_28589320 = oDic
    Set oDic = Nothing
End Function

Open in new window


I tested the function with the following code:
Sub main()
    Dim strThing As String
    Dim dicThing As Object
    Dim vItem As Variant
    
    strThing = ":20:111111251540002D" & vbCrLf & _
                ":23B:CREASDD" & vbCrLf & _
                ":32A:09A1237CHF41912,05" & vbCrLf & _
                ":33B:CHASD1912,05" & vbCrLf & _
                ":50K:/CH110023412400243526" & vbCrLf & _
                "MSFSDFER KFSFUTDFR" & vbCrLf & _
                "BUDFSFERSTR. 2" & vbCrLf & _
                "8113 BOSDFSFSLSEN" & vbCrLf & _
                ":53A:UBSWCHZH80A" & vbCrLf & _
                ":57D:SFS SDFSDFS" & vbCrLf & _
                "RASDASDA" & vbCrLf & _
                "BRANCH 0" & vbCrLf & _
                "GOGOGOG" & vbCrLf & _
                ":59:/43434" & vbCrLf & _
                "DFSDFL GRSDFOUP" & vbCrLf & _
                "SDFDF + DF LTFDD" & vbCrLf & _
                "DFD HDFDN 343423" & vbCrLf & _
                "GOGOER" & vbCrLf & _
                ":71A:OER"
    Debug.Print strThing
    Debug.Print "==================================="
    
    Set dicThing = Q_28589320(strThing)
    For Each vItem In dicThing
        Debug.Print vItem, dicThing(vItem);
    Next
End Sub

Open in new window


The test produced the following results in the Immediate window
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 0
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER
===================================
:20:          111111251540002D
:23B:         CREASDD
:32A:         09A1237CHF41912,05
:33B:         CHASD1912,05
:50K:         /CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:         UBSWCHZH80A
:57D:         SFS SDFSDFS
RASDASDA
BRANCH 0
GOGOGOG
:59:          /43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:         OER

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57
ID: 40527927
<<that i would like to split into many new fields,and the delimiter should me ->  :numeronly OR numer&Char:>>

  So you can't count on the colon always being there as the start of a new section or was that a typo?   Did you mean to type this:

and the delimiter should me ->  :numeronly: OR :numer&Char:

If you will always have a leading/trailing colon, then use Split().

Token one then will be the code, token two the value for it (you'll have two entries in the array for each element; 1 & 2 first code and value, 3 & 4 second code and value, 5 & 6 third code and value, etc).

Jim.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40527935
i don't understand the result you want to get..

what is the problem with my query ?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40527946
try this query


select f1, IIf(InStr([f1],":")>0,Left([f1],InStrRev([f1],":")),"") as Result1, IIf(InStr([f1],":")>0,Mid([f1],InStrRev([f1],":")+1),[f1]) as Result2
from yourTable;
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40528593
Like this?
Sample attached
Ugly.mdb
0
 

Author Comment

by:drtopserv
ID: 40529068
Nick67 YOU ALmost reach the point, still need small fix :
IN :

ResultID      UglyID      DelimiterValue      UglyText
5      1      :50K:      /CH110023412400243526

in table tblResults , should contain :
UglyText
/CH110023412400243526
 MSFSDFER KFSFUTDFR
 BUDFSFERSTR. 2
 8113 BOSDFSFSLSEN

and not ONLY : /CH110023412400243526
means should contain the string until the next delimiter " :53A:"
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40529081
@drtopserv

Have you tested or have any questions about my solution?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40529136
did you try my query at

http:#a40527946
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40529348
Easily enough fixed.
Ugly text just has to be held and concatenated until the next colon is encountered and THEN written to the table.  You need me to fix that, or do you have it cased?
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 40529772
So like this!
Altered sample attached
Ugly-v1.mdb
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 125 total points
ID: 40530094
@drtopserv

using the information you posted, you only need a query to get what you need.

see  http:#a40527946
0
 

Author Closing Comment

by:drtopserv
ID: 40564032
You all are great helpers
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now