How to do "text to column" in Access?

  • Thread starter Thread starter Joycey
  • Start date Start date
J

Joycey

I know there is a "text to column" function in Excel, how do I do the same
with Access?

I have a database with 700000 rows of data, so export to excel then import
it back is not an option.

The particular field I need to split looks like this
(xxxxxxxxxxx) and (yyyyyyyyyy) and (zzzzzzzzzzz)
(aaaaaaaaaaa) and (bbbbbbb) and (ccccccccccccccc)

The number of characters inside the brackets differ from row to row.

Thanks.
 
How man "and" sets of data will there be? Is it consitanct, or does that
change also?

The follwing function will put out a value from a string:

Public Function strToken(strData As Variant, intToken) As Variant

Dim vArr As Variant
Dim strBuf As String

If IsNull(strData) Then Exit Function

vArr = Split(strData, "(")

If intToken <= UBound(vArr) Then
strBuf = vArr(intToken)
If Len(strBuf) > 0 Then
strBuf = Split(strBuf, ")")(0)
strToken = strBuf
End If
End If


End Function


So, if we have

" (abc) and (def) and (ghi)"

Then in the debug window, we have:

? strtoken("(abc) and (def)",1)

You get abc
and

? strtoken("(abc) and (def)",2)
you get def

The only question now is determining how many "sets" of values you can have.
You would then create the first column, and then run a update query, like:

update tblMain set newCollum1 = strToken([dataCollum],1)

However, it very likely you should normalize each value out to a new table,
and not actually create a new column (unless the data has a 'fixed' number
of columns).
 
Back
Top