How to do "text to column" in Access?



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.




Albert D. Kallal

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

? 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).

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question