Complicated Split (2 Viewers)

[Keith]

I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;

MemberID	Boats
5882	Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883	Astute Auriga Aeneas Affray Amphion
2407	H34 O10 Porpoise Trenchant Tapir.

I want to create a table as follows;

MemberID	Boat	From	To
5882	Oppossum	1978	1980
5882	Oppossum	1987	1989
5882	Otter	        1980	1984
5882	Opportune	1991	1993
5882	Turbulent	1997	2000
5883	Astute		
5883	Auriga		
5883	Aeneas		
5883	Affray		
5883	Amphion
Etc.

Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.

 

[plog]

My advice would be to do it in one hit, I think it would be easier that way. Here's what I would do:

1. Make a sandbox database. Create a blank Access file, copy in just the structure of the table you will be working with (use the name the same for it though). Then, copy over some test records. Make sure you bring in records to cover all cases (no dates, one date, multiple dates, etc).

2. Start writing parsing code (that's the term of what you are doing, if you need to google for help) that will extract out the Boats field. You will need to use the Access string functions on this page: http://www.techonthenet.com/access/functions/
Doing this in one shot will be easier than 2 seperate runs. You will have to just test for parenthesis and if they are present run code to move the following data to the appropriate fields. If not there, continue parsing as new records.

3. Once you have it working in the sandbox, copy in your table and run your code in the sandbox on it. This way your data is safe and you can test your process.

 

[Mile-O]

Could you work through each record, perhaps using the Split() function to put the data in arrays.

Then some more code to check if it meets a number of criteria and, if so, insert a new record with that data into your new table.

Based on your example, the lower bound of the array (0 in this case) would be the MemberID (if that's the expectation). Then you can loop through the rest doing some evaluation work;

--- Does it have a (00-00) format in it? If so, take the text and make two years and insert them into table.
---Does it exclusively have (00-00) format? No problem, get the text value from the previous array (or last record inserted).
--- Does it not have (00-00) format but has text? It's a boat name. Insert a new record into the table.
--- Is it alphanumeric? If you don't want the likes of 'H34' or 'O10', then ignore them and move on.

 

[Keith]

Thanks Milo. H34 & O10 are boat names, albeit old ones. Sorry I did not make it clear in my first post that MemberId & Boats are seperate fields.

 

[Mile-O]

Sorry I did not make it clear in my first post that MemberId & Boats are seperate fields.

You did. I just never read it properly.

Still, can certainly loop through a Split() array.

 

[vbaInet]

I think Mile-O and plog have covered the crux of it.

I would:
1. Split() it using space " "
2. Loop through each item returned by split and check for the format (00-00) using LIKE.
3. If the format matches extract it, create a new record based on the boat name and the extracted year values.
4. If there's no match, create a new record based on just the boat name (gotten from step 1)

Use a query or a recordset to insert the records.

 

[Keith]

To start my learning curve I have filtered out the easy ones to start with, I have replaced the spaces with comma's

MemberID	Boats
1000	Dreadnought,Oracle,Otus,Otter
1001	Churchill,Warspite,Spartan,Sceptre,Superb
1002	Courageous,Conqueror,Valiant,Superb,Splendid
1003	Artful

I have used code gleaned from this forum to split the 'Boats Field'

Sub splitStringIntoRecords()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Dim sqlStr, insertSQL, arrayVal As String
Dim TestArray() As String
Dim fieldNam, fieldStr As String
Dim i As Integer
sqlStr = "SELECT [MemberID], [Boats] FROM tblBoatsOld"
Set rs = db.OpenRecordset(sqlStr)
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
fieldNam = rs.Fields(0)
fieldStr = rs.Fields(1)
TestArray() = Split(fieldStr, ",")

For i = 0 To UBound(TestArray) - LBound(TestArray) + 1
[COLOR="Red"]If TestArray(i) <> "" Then[/COLOR]

arrayVal = TestArray(i)

insertSQL = "INSERT INTO tblBoats(MemberID, Boat) " _
& "VALUES(""" & fieldNam & """, """ & arrayVal & """)"
DoCmd.RunSQL (insertSQL)

End If
Next i
rs.MoveNext
Loop
End Sub

I get a 'Subscript Out Of Range' Error Highlighted in Red in the Code. It processes the first record correctly befor the error. I must admit the grey matter is slowing up.

 

[Mile-O]

For i = LBound(TestArray()) To UBound(TestArray())

 

[Mile-O]

Also, as an aside:

Dim sqlStr, insertSQL, arrayVal As String

That's making two Variants and one String.

Dim sqStr As String, insertSQL As String, arrayVal As String

 

[vbaInet]

A few more plus the changes Mile-O suggested:

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim sqlStr      As String, insertSQL As String, arrayVal As String
    Dim TestArray   As Variant
    Dim fieldNam    As String, fieldStr  As String
    Dim i           As Integer
    
    sqlStr = "SELECT [MemberID], [Boats] FROM tblBoatsOld"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr, dbOpenSnapshot)
    
    Do While Not rs.EOF
        fieldNam = rs.Fields(0)
        fieldStr = rs.Fields(1)
        TestArray = Split(fieldStr, ",")

...also don't insert into the same table. Insert into a temporary (or staging) table.

 

[Keith]

Thanks Mile-O & vbaInet thats the easy bit done- 3,000 of the 5,000 records sorted

 

[Mile-O]

What's the next bit like?

 

[vbaInet]

I like to see people take on the initiative so kudos to you!

Here's another tip to sort out the next part. After splitting it for the first time, you can:
1. check the Ubound() of TestArray variable if it's zero
2. if it is attempt a second split with space as your delimeter

Or

1. Test for space or comma before deciding to split

Option 2 would be my preference.

 

[Keith]

The Complicated Bit

MemberID	Boats
1024	H43 (42) H44 (42) L26 (42) Tactician(42-44) Varangian(45) Talent (45-46) Tally-Ho(46-47)
1025	Oberon H44 H50 Spirit Vigorous Thrasher
1027	H43 Subtle Sealion
1042	L26 H44 Thunderbolt Truculent Unruly Ultor
1045	Spur Virulent Venturer U1171
1067	Truculent H28 Truant Templar Amphion Alliance Astute Trespasser
1110	Courageous(87-92) Repulse(96) Victorious(00-01).

In brackets is the year i.e. (42) is 1942 and (87-92) is 1987 to 1982 to end up with;

ID	
MemberID	Boat	     From	To
1404	       Taciturn   1942       1949

Sorting out the spelling mistakes in the first batch at the moment.

 

[Mile-O]

Either Regex, which I'm terrible at to check if the string meets particular format...

...or use combinations of InStr() and Mid() to oick out the years from within brackets.

a = InStr(1, MyString, "(")
b = InStr(1, MYString ")")

If b-a = 5 then you have two years to get out. Use Mid() to pick up the first and second number,
If b-a = 3 then you have one year to get out.
If they are 0, no number to pick out.

 

[Keith]

I am trying to reduce the dataset to just those records with dates in. I tried to run a delete query but I get an error 'Could not delete from specified tables'. I have researched this error and tried all suggestiuons found with no luck. The sql for the query is;

DELETE DISTINCTROW tblBoatsWithNumbers.*
FROM tblBoatsWithNumbers INNER JOIN qrytblBoats2 ON tblBoatsWithNumbers.MemberID = qrytblBoats2.Member;

qrytblBoats2 is the MemberID's that I want to delete from tblBoatsWithNumbers.
Any help would be appreciated.

I solved it by using qrytblBoats2 to make a table which I used in the delete query instead of qryBoats2.

 

[BlueIshDan]

Something like this maybe?
This is not tested just a quick jot down of code.

Private Sub MigrateData()

    Dim rs_from As Recordset: Set rs_from = CurrentDb.OpenRecordset("fromTableName")
    Dim rs_to As Recordset: Set rs_to = CurrentDb.OpenRecordset("toTableName")
    
    Dim temp_str As String
    
    While Not rs_from.EOF
    
        For Each split_boats In Split(rs_from!Boats, " ")
            If Len(split_boats & vbNullString) > 0 Then
            
                rs_to.AddNew
                rs_to!MemberID = rs_from!MemberID
                
                If InStr(1, split_boats, "(") > 0 Then
                    
                    temp_str = Split(split_boats, "(")(1)
                    If InStr(1, temp_str, ")") > 0 Then: temp_str = Left(temp_str, Len(temp_str) - 1)
                    
                    If InStr(1, temp_str, "-") > 0 Then
                        rs_to!From = Split(temp_str, "-")(0)
                        rs_to!To = Split(temp_str, "-")(1)
                    Else
                        rs_to!From = temp_str
                    End If
                    
                End If
                
                rs_to.Update
            End If
        Next
    
        rs_from.MoveNext
        
    Wend
    
    rs_from.Close
    rs_to.Close
    
End Sub

 

[Keith]

I have split the records with no dates without any problems. I Have filtered out the data that has dates in and the table is tblSandBoxFrom

MemberID	Boats
1659	Courageous,1986,1988
1659	Conqueror,1977,1979
1659	Walrus,1977,1977
1659	Valiant,1983,1986
1660	Artemis,55,57
1660	Token,57

The ouput table is tblSandBoxTo and the result that I would like to see.

MemberID	Boats	From	To
1659	Courageous	1986	1988
1659	Conqueror	1977	1979
1659	Walrus	        1977	1977
1659	Valiant	        1983	1986
1660	Artemis	        55	57
1660	Token	        57

I have made a complete bodge of the code, Could Someone please point me in the right direction? Not being an IT professional the last time I delt with Array's was in the days of Basic.

Sub splitStringIntoRecords()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlStr As String, insertSQL As String, arrayVal As String
    Dim TestArray() As String
    Dim fieldNam As String, fieldStr As String
    Dim i As Integer

    On Error GoTo splitStringIntoRecords_Error
    DoCmd.SetWarnings False

    sqlStr = "SELECT [MemberID], [Boats] FROM tblSandBoxFrom"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr, dbOpenSnapshot)
    Do While Not rs.EOF
        fieldNam = rs.Fields(0)
        fieldStr = rs.Fields(1)
        TestArray() = Split(fieldStr, ",")

        For i = LBound(TestArray()) To UBound(TestArray())
            If TestArray(i) <> "" Then

                arrayVal = TestArray(i)
                DoCmd.SetWarnings False
                insertSQL = "INSERT INTO tblSandBoxTo(MemberID, Boats, From, To) " _
                          & "VALUES(""" & fieldNam & """, """ & arrayVal(1) & """,""" & arrayVal(2) & """,""" & arrayVal(3) & """)"
                DoCmd.RunSQL (insertSQL)

            End If
        Next i
        rs.MoveNext
    Loop
    DoCmd.SetWarnings True

    On Error GoTo 0
    Exit Sub

splitStringIntoRecords_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure splitStringIntoRecords of Module mdlSplit"
End Sub

 

[Mile-O]

Turn off your On_Error statement and let us know where the error is actually happening.

 

[Mile-O]

Also,

if you do a little check like this...then you can fill out the year completely, assuming that every boat year will be 19.

"VALUES(""" & fieldNam & """, """ & IIf(Len(arrayVal(1))=2,"19","") & _
arrayVal(1)) & "  & """,""" & IIf(Len(arrayVal(2))=2,"19","") & arrayVal(2)) & """,""" & arrayVal(3) & """)"