How to split text data from table (1 Viewer)

[SachAccess]

Hi,

I have a table. Value in the table is something like below.
Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|
Separator is pipe character. I want to split this data in different fields.

At present, this entire text in one field. I wan to split it as per pipe separator. How do I do this.
Can anyone please help me in this.

 

[Gasman]

Use the Split() function.

 

[SachAccess]

Hi @Gasman thanks for the help.
I am trying to use below code to perform the task but getting error at below line.

rsWrite.Fields("My_ID") = rsRead.Fields("My_ID")

Getting error as Data type conversion error.
Could you please help if you get time.

'https://bytes.com/topic/access/answers/501632-split-count-text-field
Sub NormalizeTable()
    Dim rsRead As DAO.Recordset
    Dim rsWrite As DAO.Recordset
    Dim varValues As Variant
    Dim intCounter As Integer
   
    Set rsRead = CurrentDb.OpenRecordset("NewTable", dbOpenTable, dbOpenForwardOnly)
    Set rsWrite = CurrentDb.OpenRecordset("Revised_Table", dbOpenTable)
   
    Do Until rsRead.EOF
        varValues = Split(rsRead.Fields("My_ID"), "|")
        For intCounter = 0 To UBound(varValues)
             rsWrite.AddNew
             rsWrite.Fields("My_ID") = rsRead.Fields("My_ID")
             rsWrite.Fields("My_ID") = Trim$(varValues(intCounter))
             rsWrite.Update
         Next intCounter
        rsRead.MoveNext
    Loop
   
    rsRead.Close
    rsWrite.Close
    Set rsRead = Nothing
    Set rsWrite = Nothing
    MsgBox "done"
End Sub

 

[SachAccess]

I tried checking in immediate window, it is giving result as below.

? rsRead.Fields("My_ID")
Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|

? UBound(varValues)
20

 

[Gasman]

You need to inspect the data type for My_ID, plus you are overwriting it anyway on the next line?

 

[Gasman]

I would be expecting you to populate the rswrite fields with each piece of data that was split?
You need to show the structure of both tables I believe.

Think about it. you are splitting one record into it's component parts and then populating fields in the table with those parts.
Walk through the code inspecting the variables.
Just start with a subset of the data, no more that 5 records until you get it correct.

 

[SachAccess]

Hi @Gasman , thanks for the help, data type for My_ID is showing as Text.
Am sorry, did not understand second part of your help answer.

 

[Gasman]

Also start indenting your code properly. That will help in spotting errors just like this.

 

[SachAccess]

Also start indenting your code properly. That will help in spotting errors just like this.

Yes sir, I agree, my mistake. Won't repeat.

 

[Gasman]

Well as you are storing text, I would use a string variable to hold the data that has been split?
I see you have just copied that code verbatim, without understanding any of it?

I do not believe that code would work as is?

 

[Gasman]

I would expect the code to be more like this?

    Do Until rsRead.EOF
        varValues = Split(rsRead.Fields("My_ID"), "|")
        rsWrite.AddNew
        For intCounter = 0 To UBound(varValues)
             rsWrite.Fields(intCounter) = Trim$(varValues(intCounter))
        Next
        rsWrite.Update
        rsRead.MoveNext
    Loop

 

[SachAccess]

Hi @Gasman , I agree with you. Not giving excuse here, I come from a Excel VBA background.
Finding it bit difficult to edit codes available on Google to suit my requirements (which is comparatively easy with Excel).

 

[SachAccess]

I would expect the code to be more like this?

    Do Until rsRead.EOF
        varValues = Split(rsRead.Fields("My_ID"), "|")
        rsWrite.AddNew
        For intCounter = 0 To UBound(varValues)
             rsWrite.Fields(intCounter) = Trim$(varValues(intCounter))
        Next
        rsWrite.Update
        rsRead.MoveNext
    Loop

Hi @Gasman , thanks for the help.
I am getting error with rsWrite.Fields(intCounter) = Trim$(varValues(intCounter)) line as Data type conversion error.

PS - I was trying how to write rsWrite.Fields(intCounter) but was not able to understand how to write.
Similar to For Each row Loop in Excel

 

[Gasman]

Hi @Gasman , I agree with you. Not giving excuse here, I come from a Excel VBA background.
Finding it bit difficult to edit codes available on Google to suit my requirements (which is comparatively easy with Excel).

No, you are probably more familiar with Ranges and the like, than I am.
Logic is the same though, and you still need to grasp the basics of what you are trying to do.

There is another user on another forum doing the same thing, using code without understanding it.
That is just going to frustrate you. You need to spend some time and understand it, just as you did when you started with Excel.

It is like wanting to be an F1 driver, when you cannot even drive yet.
We all have to start somewhere.

 

[Gasman]

Hi @Gasman , thanks for the help.
I am getting error with rsWrite.Fields(intCounter) = Trim$(varValues(intCounter)) line as Data type conversion error.

PS - I was trying how to write rsWrite.Fields(intCounter) but was not able to understand how to write.
Similar to For Each row Loop in Excel

Look and see what that value is.
I would change varValues to strValues As String and use that, as we are dealing with strings. However what is the datatype of the fields in Revised_Table? they need to be text?

 

[SachAccess]

Hi @Gasman thanks a lot for understanding. I agree with all the points you have mentioned.

 

[SachAccess]

Hi @Gasman , datatype of the fields in Revised_Table was wrong, changed it to TEXT.
change varValues to strValues but as a variant only. Not allowing to change it to string.

Now getting different bug at rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))
Item not found in this collection.

Really sorry for all the trouble, please check only if you have time. Already troubled you a lot over this.
Have also attached TXT file am using.

Updated code looks like below.

Sub NormalizeTable()
    Dim rsRead As DAO.Recordset
    Dim rsWrite As DAO.Recordset
    Dim strValues  As Variant
    Dim intCounter As Integer
  
    Set rsRead = CurrentDb.OpenRecordset("NewTable", dbOpenTable, dbOpenForwardOnly)
    Set rsWrite = CurrentDb.OpenRecordset("Revised_Table", dbOpenTable)
  
    Do Until rsRead.EOF
        strValues = Split(rsRead.Fields("My_ID"), "|")
        rsWrite.AddNew
        For intCounter = 0 To UBound(strValues)
             rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))
        Next
        rsWrite.Update
        rsRead.MoveNext
    Loop
  
    rsRead.Close
    rsWrite.Close
    Set rsRead = Nothing
    Set rsWrite = Nothing
    MsgBox "done"
End Sub

 

[SachAccess]

Result in immediate window is as below.

? strValues(intCounter)
Utilitatis

? strValues(intCounter)
causa

 

[Gasman]

Probably need Ubound -1 then? as we are going from 0 to 19, but that is still 20 entries?
Count the number that is meant to be in that record to confirm.
Again, if you inspect the variables, it would be evident

You need to inspect intCounter ?

 

[SachAccess]

Hi @Gasman , I agree with you, it has to be 20. It is correct. With UnBound - 1 also it is giving same bug.

? UBound(strValues)
20

I added a watch window to intCounter. Macro is getting stuck at 1.
Value is showing perfectly in watch window too.
PS - I have kept Revised_Table empty, only header My_ID is there.