PDA

View Full Version : SOLVED: Parse Data



Anne Troy
06-21-2004, 12:24 PM
How could I take data and parse it into a new table?

Suppose I have a field called CustName:

Mr B Cooney


And what to break it out into 3 different fields; we'll call them CustSal, CustFI, CustLName, respectively:

CustSal: Mr
CustFI: B
CustLName: Cooney

SJ McAbney
06-21-2004, 01:38 PM
Quickest way would be to use the Split() function to break the field down into separate sections and then use DAO or ADO to put the split components into the relevant fields.

Will the name always come in three parts or will you get data like:

Mr W Wobbly
Mrs Bloggs
Fred Bunberry
Hubert D. Booby

etc?

Anne Troy
06-21-2004, 01:43 PM
Can we assume 3 parts for now? :)
But I'd like to reserve the right... :)
We'll also add it to the KB.

SJ McAbney
06-22-2004, 01:18 AM
OKay, since it is only three parts (and I'm actually more awake than I seem to have been yesterday, based on what I answered with) all you need is one function, one append query, and the source and destination tables.

Firstly, open a standalone module and paste this function into it:

Public Function GetPart(strValue As String, intPart As Integer) As String

Dim strParts() As String

strParts() = Split(strValue, Chr(32))
GetPart = strParts(intPart)

End Function

Open a query and copy and past this SQL:

INSERT INTO tblDestination ( CustSal, CustFI, CustLName )
SELECT GetPart([Data],0) AS Expr1, GetPart([Data],1) AS Expr2, GetPart([Data],2) AS Expr3
FROM tblSource;

Remember to change tblDestination and tblSource to the names of your actual tables. Save the query.

Anne Troy
06-22-2004, 04:09 AM
Hi, Ab! Do you mind giving us another shot at this where it uses a comma as the delimiter? :)

Sorry about that! Just got the request clarified.

SJ McAbney
06-22-2004, 04:36 AM
Just change the line in the function to this:

strParts() = Split(strValue, ",")

Anne Troy
06-22-2004, 04:43 AM
Thanks, Ab. :)
I've alerted the asker.
(I'm cheating again.)